Performance Tuning (Query Tuning)

This impeccable Query Tuning course is exclusively designed for SQL Developers, MSBI Developers and Database Consultants. Course includes Mid Level to Advanced Query Tuning Techniques including Memory Optimized Tables, Temporal Objects, LIVE Query Stats, Partitions, Stats, DTA, Index Plans, Execution Plans, DDM, SQL Buffer Space, In-Memory, Lookup Options, Query Store, Dynamic Objects, DOP, Query Priority Settings, more...!

SQL Server & Query Performance Training Schedules

 
Timings (IST) Free Demo Start Date Register
6:30 AM to 8 AM
Oct 2nd Week
Register
10:00 AM to 11:30 AM Sep 22nd Sep 22nd Register
6:30 PM to 8 PM Oct 4th Week Register

None of the above schedules work for you? opt for On-demand, Self Paced Video Training Course

Trainer : Mr Sai Phanindra T (11+ Yrs Exp)

Dur : 6 Days (1 Hr per day)

Course Fee : INR 4000/- (USD 60)


COURSE HIGHLIGHTS
Daily Tasks Real-time Examples
LIVE Execution Plans Perf. Baselines
70-761 Certification 70-762 Certification
 

SQL Server Performance Tuning (Query Tuning):

CHAPTER 1 : QUERY AUDITS, QUERY TUNING - JOIN OPTIONS, CTES

Identifying Long Running Queries & Activity Monitor Using Important Dynamic Management Objects (DMV, DMF) Using Query Statistics and Cache Plans / Execution Plans Using CROSSAPPLY and Other Operators with Dynamic Objects Avoiding Self Joins - Real-world Scenarios Avoiding Sub Queries and Conditions - Real-world Scenarios Comparing Sub Queries & Joins - Performance Baselines Stored Procedures for Parameterized CTE (Sub) Queries Using CTE for Table Data Operations - DML & Retrieval CTE for DML and DDL Operations in Stored Procedures Using Recursive CTEs and Self Joins with Stored Procedures Precautions for Recursive CTEs - Performance Impact Query Tuning Operations with CTEs. Query Store Options CTE Advantages and Limitations - Precompilations ANCHOR Members and RECURSIVE Members. Termination HASH JOIN - Examples and Precautions. Usage MERGE JOIN - Examples and Precautions. Usage LOOP JOIN - Examples and Precautions. Usage OUTER APPLY, Hybrid and Multi - Level Joins Indexes on Join Options - MERGE and LOOP Joins. Usage Real-world Scenarios @ Joins, Join Options

CHAPTER 2 : PARTITIONS and STATISTICS

Big Data - Performance Considerations Table Partitions and Query Tuning Options Partition Functions and Partition Schemes Partition Ranges, Values and Sort Orders Partitioning Un-partitioned Tables using Indexes Aligned / Indexed Partitioning and Performance Data Compression Types - ROW Level, PAGE Level Partition Numbers and Filtered Compression Concepts Managing Partitions and Query Tuning Options Partitioned Views and Concolation Options Table Archival Process and Partition Split/Merge Query Tuning and Resource Optimization Options STATISTICS - Purpose and Types. Query Tuning Options Column Statistics - Creation and Usage. Advantages Index Statistics - Auto Creation with Indexes, Usage Manual Update of Column Statistics - GUI & Scripting Role of Statistics in Query Tuning Process - Options STATISTICS with Indexes and Query Conditions. Updates LIVE Query Statistics (SQL Server 2016), Table Statistics

CHAPTER 3 : FULL TEXT SEARCH (FTS) & FT Indexes

LIKE Operator - Limitations. Using Wild-cards Full Text Search (FTS) Configuration Options Full Text Search Service Activation - DB Level Filter Daemon Launcher Service - Purpose, Settings Database Catalogs (FTC) and Storage Locations Full Text (FT) Indexes for Query Tuning Full Text Columns and Primary Key Index Full Text Index For Searching Queries. Issues Full Population and Incremental Population CONTAINS() and FREETEXT() Functions Token Search, Inflectional Forms, Operators Data Populations and FILESTREAM with FTS Performance Tuning with Full Text Indexes CONTAINSTABLE and FREETEXTTABLE with FTS Real-world Performance Considerations with FTS

CHAPTER 4 : INDEX INTERNALS AND DTA TOOL

Index Internals and Execution Plans Understanding Execution Plans, Statistics, Cost Index Fragmentation - Issues, Performance SAMPLED and DETAILED Query Scans. FillFactor Index Rebuilds (Online/Offline), Tuning Options Index Reorganization Process and Advantages Page, Row Compressions with Indexes - Cautions Filtered Indexes, Online Indexes, Indexes Views GAM, SGAM Pages, Metadata Header Info Filtered Indexes and Index Size Limitations Table Statistics & Query Tuning Options Handling Heaps, Clustered, and Nonclustered Indexes Fill Factor, Pad Index and Query Tuning DTA: Usage, Sequential / Parallel Query Tuning DTA Tool with Profiler, Trace Tables, Cache Understanding Workload Files & Tables in Profiler SQL Profiler Tuning and Tuning Templates Database Tuning Advisor (DTA) - Usage DTA Tool for Procedure Cache, Recent Queries DTA Tool for Multi-Database Connections Understanding PDS Options with Indexes

CHAPTER 5 : MEMORY MONITORING, LIVE EXECUTION PLANS

Memory Optimized Tables, Optimized Filegroups Memory Snapshot Settings and Real-world Usage Temporal Tables and SYSTEM_VERSIONING Temporal Tables For DML Audits, Performance Impact In-Memory Tables Creation and Index Options Working with Extended Events & Performance Impact LIVE Query Statistics - Monitoring Options, Metrics LIVE Query Statistics - Tracing, and Baselining Collecting and Analyzing Data Using Extended Events Implementing Performance Baseline Methodologies Optimize the file configuration of your databases Use DMVs and gather DB Performance Metrics Memory Tables Versus Temp Tables/Table Variables LIVE Execution Statistics, Hash Plans, Performance Natively Compiled Stored Procedures, Performance Creating System Versioned Temporal Tables Querying and Modifications to Temporal Tables Bulk Inserts, OPENROWSET with Temporal Tables Tuning Bulk Inserts - Recovery Models & Logging

CHAPTER 6 : PERFMON COUNTERS, MEMORY OPTIONS @ TUNING

PERFMON Counters and MSDTC Service Memory Pages & IO Resources : Query Performance MEMORY LEAKS & PAGE WAITS: Query Performance LATCH WAITS and Query Performance Impact CPU, Thread Management and Windows Fibres Working with Machine Code @ SQL Server 2016 Resource Governor - Resource Pools - Tuning Resource Workload Groups - Creation, Settings LOW, HIGH, MEDIUM Priority Queries - Resources Classifier Functions, Cost Based Optimization Query Priority, CPU / Memory / IO Limits Windows Fibres, Priority Boost, DOP Options Processor Settings and Counters. Thresholds Cached Plans and Memory Store for Stored Procedures Performance Tuning - Checklist Activities

 
All Classes are Instructor-Led & LIVE. Completely Practical and Real-time with Study Material, Session Notes, Tasks and 24x7 LIVE Server.
 
 
 

Job-Oriented Real-time Training @ SQL School Training Institute - Trainer : Mr. Sai Phanindra T

 
 
 
New batch for SQL Server / T-SQL Course starts every 10 days. New batch for SQL DBA Course starts every 20 days. New batch for MSBI Course starts every 15 days.
 
SQL Server, SQL DBA, MSBI - Trainings, Placements & Job Support. 24x7 Online Lab. Register Today