Performance Tuning (Query Tuning)

This impeccable SQL Query Tuning course is exclusively designed for SQL Developers, MSBI Developers 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 Managmenet, Index Management, Perfmon Counters, Query Analysis and Execution Plan Analysis paired with Query Tuning Tools including DTA, Profiler using Query Store, Dynamic Objects, DOP, Query Priority Settings.

SQL Server & Query Performance Training Schedules

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 Perf. Baselines
Query Store Query Statistics
Tuning Tools, Counters Spooling, Perf Insights
 

SQL Server Performance Tuning (Query Tuning):

DAY 1: QUERY TUNING 1 - INDEXES, PARTITIONS

  • Audit Long Running Queries - DMV / DMF
  • Activity Monitor Tool and Query Statistics
  • Working with Plan Handle and Execution Time
  • Index Creation Options: SORT_IN_TEMPDB
  • FILL FACTOR and PAD INDEX Options
  • IAM Pages, GAM, SGAM and PFS Bit Maps
  • Resumable Indexes in SQL Server 2019
  • Table Partitions : Advantages, Performance
  • Database Filegroups and Partition Ranges
  • Partition Functions and Partition Schemes
  • Partition Un-partitioned Tables: Indexes
  • Aligned / Indexed Partitions - Importance
  • Partition Management - LIVE Online Tables
  • Partition SPLIT and Partition MERGE

Day 2: QUERY TUNING 2 - STATISTICS, FULLTEXT SEARCH

  • Execution Plans : Table Scan, Index Scan
  • Execution Plans : Index Seek, Query Cost
  • IO Cost and CPU Cost for Query Analysis
  • Operator Cost and Sub Tree Cost Analysis
  • Spooling, Temp DB Role for Query Prefetch
  • Statistics Creation and Usage in Real-time
  • Full Text Search (FTS) Architecture - Tuning
  • Stop Words, Stemmer and Thesaurus For Queries
  • Indexer Program and Query Processor Usage
  • Database Catalogs (FTC) and FDHost.exe
  • Full Text (FT) Indexes for Query Tuning
  • CONTAINS() and FREETEXT() Functions
  • Data Compression - ROW & PAGE Levels

Day 3: QUERY TUNING 3 - INDEX MANAGEMENT

  • Index Management and Fragmentation
  • Index Rebuilds, Index Reorganization
  • Database Maintenance Plans (DMP) and Jobs
  • Query Execution Plans, Stats Updates
  • SAMPLED, DETAILED Scans. FillFactor
  • Index Management Options : Page Count
  • Index Usage and Degree of Parallelism [DOP]
  • SQL Profiler Tuning, Tuning Templates
  • Workload Files, Trace Tables in Profiler
  • DTA Tool with Profiler, Trace Tables, PDS
  • DTA with Query Cache, Extended Events
  • Memory Optimized Tables & FileStream
  • Memory Elevated Snapshots at DB Level
  • UPDATE STATS with RECOMPUTE Options

Day 4: QUERY TUNING 4 - PERFMON, ISOLATION LEVELS

  • Perfmon Counters and Real-time Tracking
  • Processor, Disk and Memory Counters
  • TPS Counters and Log Space Counters
  • TempbDb Data Space Counters, Thread Count
  • NUMA Nodes, Boosting SQL Priority
  • Processor and IO Affinity - TDS Packets
  • Query Memory and Index Memory Settings
  • LOCKS : Mechanism, Types, Concurrency
  • X, S, IS, IX, U, Sch-M and Sch-S Locks
  • Lock Escalations, Deadlock Graphs, LIVE Locks
  • Isolation Levels - Types and Session Settings
  • ReadCommitted, Read Uncommitted Isolations
  • Serializable, Snapshot, Repeatable Read
  • Read Committed Snapshot Isolation Level
  • Choosing Correct Isolation Levels For OLTP
MCSA - 70 761 Exam Guidance

This SQL Server Query performance Tuning Course includes one Real-time Project

This Project includes evaluating complex Stored Procedures and Creating Performance Base Lines for Optimal Query Executeions. This project also includes the use of Maintenance Plans, Statistics and Resource Governance Stategies required for Senior SQL Server Datbase Developers. Very much helpful to clear your Interviews and MCSA Certifications.

 
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