Performance Tuning (Query Tuning)

This impeccable Performance Tuning (Query Tuning) course is exclusively designed for SQL Database Developers, MSBI Developers, Data Analysts and Database Administartors (SQL DBAs). This SQL Query Tuning Course includes Mid Level to Advanced Query Tuning Techniques including Memory Optimized Tables, Temporal Tables, LIVE Query Stats, Partitioned Views, Statistics, Buffer Managment, Index Management, Perfmon Counters, Query Analysis and Execution Plan Analysis paired with Query Tuning Tools including DTA and SQL Profiler. This course also inludes Query Store, Dynamic Objects, DOP, Query Priority Settings and NUMA Node Operations for Optimal Query Executions. One Real-time Case Study is also included in this Query Tuning Training Course.

Trainer : Mr. Sai Phanindra T (12+ Yrs Exp)

Dur : 5 Days (1 Hr per day)

Course Fee : INR 4000/-


COURSE HIGHLIGHTS
Memory Optimizations LIVE Execution Plans
Fragmentation Issues Performance Baselines
Database Query Store LIVE Query Statistics
Tuning Tools, Counters Spooling, Perf Insights
 

SQL Server Performance Tuning (Query Tuning):

VIDEO 1: PERFORMANCE TUNING Demo, Indexes

  • Need for Performance Tuning (Query Tuning)
  • SQL Server Query Tuning Operations - Plan
  • SQL Server Versions and Editions - Course Plan
  • Query Parse, Compilation and Query Executions
  • Query Optimizer [QO] Component Overview
  • TDS Packets - Verification and SSMS Options
  • Query Performance Tuning Factors in SQL Server
  • Query Memory Settings and Server Properties
  • Day Wise Course Plan - Performance Tuning Course
  • Indexes: Architecture and Types Of Indexes
  • Clustered Indexes and Non Clustered Indexes
  • Included Index, ColumnStore Index, Filtered Indexes
  • COVERING Indexes and UNIQUE Column Indexes
  • LIVE Online Indexes in Real-time - Practical Use
  • B Tree Structures and Index Access Management (IAM)
  • Branch Level Pages and Leaf Level Pages in Indexes
  • Tuning Join Queries and Query Level Conditions

VIDEO 4: QUERY TUNING - 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

VIDEO 2: QUERY TUNING - QUERY AUDITS, 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 Audits
  • 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

VIDEO 5: QUERY TUNING 4 - TUNING TOOLS, PERFMON

  • 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
  • Understanding the Query Execution Plans, Cost Analysis
  • 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 Space Issues - Reasons, Cautions and Solutions
  • Memory Issues - Reasons, Buffer Cache and Solutions
  • Performance Tuning - Final Checklist and Precautions

VIDEO 3: QUERY TUNING 3 - FULL TEXT SEARCH, TABLE TYPES

  • 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 Exe
  • 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

Real-time Case Study on Performance Tuning


This Case Study includes 24x7 LIVE Access to our remote SQL Server Instance hosting Big Data Sources in a Virtual Machine hosted in Azure Cloud.
The participant would be provided with specific tables and data access. Need to implement SQL Server Query Tuning [Performance Tuning] on the below concepts:
  • 1. Query Audits
  • 2. Table Partitions
  • 3. Index Partitions
  • 4. Index Management
  • 5. Partitions Management
  • 6. Stistics Management
  • 7. Execution Plan Inernals
  • 8. Perfmon Tools & Query Resources
  • 9. Spooling, Threads and Node Priority
 
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