Price Free!

Course Features
Language: English
40 hours
Study Level: Intermediate
Certificate of Completion

Performance Tuning Course

( SQL Server 2019 and SQL Server 2017 )

Training Highlights ✓ Complete Practical ✓ Resume Preparation ✓ 24×7 LIVE Server, Lab

✓ Queries: Basic to Advanced ✓ Sub Queries, Nested Queries, Joins ✓ Excel Integration with SQL Server ✓ Material & Practice Labs ✓ MCSA Certification Guidance

✓ Stored Procedures: Basic to Advncd. ✓ Query Tuning with CTEs & Isolations ✓ Real time Projects For Resume ✓ 100% Job Orientation, Support

✓ Azure Cloud Migrations with SQL DB ✓ Real-time Project Work @ End – End

Trainer : Mr. Sai Phanindra Tholeti is a Database Consultant, Microsoft Certified Trainer with more than 13 years expertise. He is rendering impeccable, highly interactive, friendly and highly technical Trainings on Microsoft SQL Server Developer, SQL DBA, MSBI (SSIS, SSAS, SSRS), Power BI and Azure to our Corporate Clients: Infosys, MindTree, ADP, Infotech, PrimeHealth

Training Highlights:

  1. Resume Preparation & Guidance are included in every plan 2. Chapter wise Mock Interviews and Detailed Explanations, Answers 3. 24 x 7 LIVE Online Lab Access with Real-time Databases for SIX Months 4. Real-time Project with Complete Explanation & LIVE Deployments [Go LIVE] 5. Basic to Advanced Training Classes with 100% Practical and 100% Real-time 6. Every concept from the course curriculum will be discussed practically with Examples

SQL Server & T-SQL : Complete Course Plan

Course Description Applicable For Duration

Module 1 Query Tuning, Extended Events,Programming Plan A, B 1 Week Module 1 SQL Database in Azure Cloud (Azure SQL) Plan B 1 Week

Total Dur : 2 W

Module 1: Query Tuning, SQL Database Programming Applicable for Plans A, B

Day 1 : QUERY TUNING 1 – INDEXES, PARTITIONS Audit Long Running Queries using DMVs and DMFs; Activity Monitor Tool and Query Statistics Reports; Logical I/O, Physical I/O and Database I/O, Wait Time; Recent Expensive Queries & Active Expensive Queries; Plan Handle and Execution Time – Query Usage Audit; Factors Impacting the Query Executions, Performance; Resumable Indexes, Usage in SQL Server 2017 & 2019; ONLINE, RESUME, PAUSE, MAX_DURATION Options; Query Store – Settings and Advantages. Options; PARTITIONS Mechanism : Advantages, Performance; Database Filegroups Usage with Partition Ranges; Partition Functions and Partition Schemes – Usage; Partitioning Un-partitioned Tables using Indexes; Aligned / Indexed Partitions – Query Importance; Partition SPLIT and Partition MERGE, NextUsed; Partition Compression Techniques : ROW, PAGE; Data Archival & SWITCH Partitions. Partitioned Views;

Day 2 : QUERY TUNING 2 – FULLTEXT SEARCH, MEMORY TABLES, STATS Full Text Search (FTS) Mechanism – Architecture, Tuning; Stop Words, Stemmer and Thesaurus For FullText Queries; Indexer Program, Query Processor & FT Query Compilation; Database Catalogs (FTC) and FDHost.exe. Daemon Threads; Full Text (FT) Indexes for Query Tuning with Tokenization; Crawler Threads and User Tables. Filter Daemon Host Excel; CONTAINS() Queries and FREETEXT() Queries with SELECT; In-Memory Tables : Creation and Practical Usage for Tuning; Memory Snapshots and Database Level and Table Level; FileStream Files and Memory Snapshot Filegroups for MOT; MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT Settings; Manual Table Stats Updates with FullScan, NoRecompute; Temporal Tables : Real-time Usage and History Tracking; Statistics : Purpose, Creation Scenarios and Usage; Index Statistics and Column Statistics for Tables, ReUse; Comparing Triggers with Temporal Tables for Audits;

Day 3 : QUERY TUNING 3 – INDEX MANAGEMENT, LOCKS Index Management Concepts for Performance Tuning; Internal Fragmentation and External Fragmentation; Fragmentation Audits : DMFs and Threshold Values; Index Reorganization and Index Rebuild Options; Database Maintenance Plans (DMP) For Index Reorg; Compress Large Objects, Fragmentation Condition; Index Page Count Condition, Index Usage Condition; Fast, Sampled and Detailed Scan Types for Indexes; Statistics Updates : Full Scan Options, DMP Jobs; LOCKS : Mechanism, Types, Concurrency Control; Lock Types: X, S, IS, IX, U, MD, Sch-M and Sch-S; Lock Audits : SP_WHO2, SP_LOCK, sysprocesses; Deadlock Simulation, Deadlock Prevention Scenarios; Deadlock Audits and Lock Events in Profiler Tool; Isolation Levels – ReadCommitted, Read Uncommitted; Serializable, Snapshot, Repeatable Read Isolations; Read Committed Snapshot Isolation Level in Real-time;

Day 4 : QUERY TUNING 4 – TUNING TOOLS, PERFMON COUNTERS Tuning Tools : Creating Workload Files and Trace Files; SQL Profiler Tool – Tuning Template and TSQL / SP Events; DTA Tool with Profiler Trace Files: Tuning Recommendations; DTA with Query Cache (Procedure Cache) & .SQL File Inputs; Execution Plans – Internals. Actual, LIVE Execution Plans; Plan Types : Index Scan, Index Seek, Tables Scan, Spooling; Query Costs : IO, CPU Cost, SubTree Cost, Operator Cost; NUMA Nodes, Boost SQL Priority, Thread Count, IO Affinity; Perfmon Counters and Real-time Tracking Of Resources; Processor, Disk, Memory, Transactions, Database Counters; Using Perfmon for Big Query Audits. Free & Total Memory; Longest Running Transactions & Transactions Per Second; Database Log Space – Issues, Solutions. Log Rebuilds; TempDB Issues – Causes, Precautions and Solutions; Memory Issues – Causes, Precautions and Solutions; Performance Tuning – Final Checklist and Precautions;

Day 5 : EXECUTION PLAN ANALYSIS and EVENTS Parameter Sniffing : Real-time Scenarios, Issues; Execution Plan Issues with Parameter Sniffing; DBCC SHOW_STATISTICS – Statistics Accuracy; Histograms & Density Vectors for Query Optimizer; RANGE_HI_KEY, EQ_ROWS and RANGE_ROWS; DISTINCT_RANGE_ROWS and Index Overlapping; sp_createstats and sp_autostats System Procedures; Detection and Fix of (Overlapping) Duplicate Indexes; Resource Governor : Real-time Usage & Conditions; Resource Pools : Creation and Usage Options; Workload Groups : Creation, Settings and Limits; Query Priority : LOW, MEDIUM and HIGH; DOP : Degree Of Parallelism and Allocation Grants; Classifier Function : Creation and RECONFIGURE; SQL Traces : Creation and Audits. Limitations; Extended Events and Traces for T-SQL Query Analysis; Extended Events Packages, Targets, Actions, Sessions; TSQL and SP Debug Events with XEL Files for EventInfo; Global Fields and Event Filters with XEL Files @ Traces; XE Profiler – Default Templates for Event Profiling;


Module 2: Azure SQL Database Development Applicable for Plans B

Day 6 : AZURE CLOUD & AZURE SQL DATABASE Introduction to Cloud. Need for Cloud, Advantages; Cloud Architecture Basics – Iaas, PasS and SaaS Advantages of Microsoft Cloud – Azure Platform; Azure Products and Azure Services – MarketPlace; Comparing Azure with Google Cloud for SQL Server; Comparing Azure with AWS Cloud for SQL Server; Azure Sources – Types, Microsoft Market Place; SQL Database Implementations in Azure Platform; Logical Servers, Virtual Machines, Managed Instance; Azure SQL Database Architecture Components; Creating Azure SQL Server (Logical Server); Creating Azure SQL Databases and Pricing Tiers; Price Tiers: Basic, Standard, Premium, PremiumRS; DTUs Allocation for Database Size, Cost Models; Firewall Settings for Azure SQL Server (Logical Server); Adding Firewall Rules – IP for Remote Access; Password Resets and Azure SQL Server Name Format;

Day 7 : DTU ARCHITECTURE, ELASTIC QUERIES DTU : Data Transaction Units : Architecture, Pools; DTU – Memory and IO Resources for Reads & Writes; Bounding Box Model for Optimal Performance; Static Pools (DTU) and Elastic Pools (eDTU); eDTUs and Elastic Pool, per Database Settings; EDTU Cost, eDTU max/min Limits and Performance; Configuring Elastic Pools for Azure SQL Databases; Elastic Pools & Tier Selection –

Recommendations; Elastic Scale for Azure SQL Database – Strategies; Vertical Partitioning and Horizontal Partitioning; Elastic Database Tool Libraries for Elastic Queries; Sharding – Topology for Elastic Query Processing; Split-Merge Servie for SaaS Software Applications; Elastic Database Features – ShardMap, ShardKey; MultiShard Queries and Elastic Transactions; LOOKUP, HASH and RANGE Strategies for Sharding;

Day 8 : AZURE SQL DB MIGRATIONS, AZURE DATA STUDIO Data Migration Assistant (DMA) Tool; On-premise to Azure SQL Database Migration; Logical Server, Virtual Machine, Managed Instance; Schema Generation and Compatibility For Migration; Generating Data Scripts and Assessment; Generate and Validate Schemas. Migrations; Migration Scopes : Schema, Data, Schema & Data; Compatibility Checks and Assessment Checks; Resolving Database Migration Compatibility Issues; Azure Data Studio Tool for Database Connections; Azure Data Studio Features – Insights, Metrics; Comparing Azure Data Studio with SQL OPS Tool; SSMS Tool for Azure SQL Database Connections; SQLOPS Tool for Azure SQL Database Connections; SSDT (Visual Studio) for SQL Database Connections; Query Editor for Azure Database Access – in Portal; Azure Database Connections with Other Tools;

DAY 9 : Azure SQL DATABASE TUNING, AZURE SEARCH Azure SQL Server Level Tuning Options; Azure SQL Database Level Tuning Options; Automated Tuning Options and Peak-Loads; Force Plan, Create Index and Drop Index; Query Performance Insight, Intelligence; Index Recommendations with CPU and IO; IO Metrics, CPU Metrics & Query Statistics; Data File IO, Log File IO, Custom Reports; Identify Long Running Queries, Intensive Queries; Query Level Recommendations and Query Costs; Azure Search Service – Configuration, Pricing Tiers; Azure Search for Data Import and Indexer Options; Suggester and Analyzer Index Modes for Tuning; Retrievable, Facetable, Filterable Indexes; Facetable and Searchable Indexes for Tuning; Change Tracking Options, Watermark Columns;

DAY 10 : AZURE SQL DATABASE VERSUS ON-PREMISE Azure SQL Server Architecture Differences; Network Protocols and DB Engine Differences; File Structure and Filegroup Allocations; Secondary Files and FileStream Differences; Table Architecture for Partitions, FT Queries; Query Processing Differences with TDS Packets; Query Monitoring and Resources – Dashboards; Unsupported Commands with T-SQL Queries;

Real-time Case Study for Azure SQL Database (DEV) This Case Study includes :

✓ Database Assessment ✓ Database Migrations ✓ Azure SQL DB Scaling ✓ Execution Plan Analysis ✓ Query Processing Metrics ✓ DTU Allocations and Insights ✓ Performance Tuning Implementation ✓ Azure Search Service Implementation ✓ Azure SQL Database Event Log Commands



Average Rating

No Votes 0 Votes
0 Ratings

Detailed Rating

5 Stars
4 Stars
3 Stars
2 Stars
1 Stars

There are no reviews yet.

Be the first to review “Oracle performance tuning”

Your email address will not be published. Required fields are marked *