Performance Tuning focuses on optimizing databases and applications for maximum speed, efficiency, and reliability. It involves analyzing queries, indexes, and configurations to enhance system performance and ensure smooth, high-speed data operations.
Training Highlights
✅ SQL Server Architecture & Query Processing Internals
✅ Index Design, Maintenance & Optimization Techniques
✅ Execution Plans, Statistics & Query Cost Analysis
✅ Identifying & Resolving Performance Bottlenecks
✅ TempDB, Memory & CPU Optimization Strategies
✅ Locking, Blocking & Deadlock Troubleshooting
✅ Database Storage, I/O & File Management Best Practices
✅ Performance Monitoring using DMVs & Profiler
✅ End-to-End Real-Time MSSQL Project
✅ Exam Prep, Mock Interviews & 1:1 Mentorship
Who Should Join?
1. Developers who want strong SQL skills
2. Data Analysts who need advanced SQL Tuning
3. ETL/Data Engineering aspirants
4. Existing SQL Developers wanting query-tuning skills
5. Professionals preparing for Azure/Fabric SQL roles
Prerequisites
Basic knowledge on Microsoft SQL (MSSQ) is mandatory for this course.
If you are new to Microsoft Databases, pls opt for our complete
Course Duration: 1 Week
Performance Tuning Course Content:
Module 1: Performance Tuning
Ch 1: Performance Tuning Fundamentals
- Query Monitoring
- Server Dashboards
- Server Logs
- Identify CPU/IO Queries
- Identify Long Running Queries
- DMVs, DMFs For Query Audits
Ch 2: Query Store
- Query Store Concept
- Configure Query Store
- History Retention
- Query Store Settings
Ch 3: Tuning: Indexes
- Indexes: Sort Locations
- Clustered & Online Indexes
- Non Clustered, Column Store
- Included Indexes in Realtime
Filtered Indexes & Usage - Indexed Views (Materialized)
Ch 4: Tuning: Partitions
- Partitions: Performance Tuning
- Partition Functions & Schemes
- Compressions: ROW, PAGE
- Partitions Limitations in OLTP
- Partitions with DWH
Ch 5: Statistics & Tuning
- Statistics: Realtime Usage
- Index & Column Statistics
- Statistics & Key Purpose
- Statistics Versus Indexes
- Stats Updates
Ch 6: Index Management
- Index Management Options
- Index Rebuilds, Re-Organize
- Database Maintenance Plans
- Page Count and Index Conditions
- Resumable & Online Indexes
Ch 7: Tuning Tools
- Tuning Tools: Workload Files
Profiler Tuning, Events
DTA, Profiler Options - Physical Design Structures
- PDS Recommendations
- Query Execution Cache
- Tuning Tools: Precautions
Ch 8: Execution Plans – 1
- Execution Plan Analysis
- IO Cost and CPU Cost
- SubTree & Operator Cost
- Table & Index Scan, Index Seek
- Index Selectivity & Tuning
Ch 9: Execution Plans – 2
- Extended Events
- wait statistics
- sys.dm_exec_query_stats
- sys.dm_exec_requests
- sys.dm_exec_sessions
- Cache Plans
Ch 10: Server Management & Tuning
- Extended Events
- NUMA Nodes, Processor Affinity
- Thread Count, DOP
- MAXDOP
- Memory Grants
- Tempdb Contention/Spills
- Memory Pressure
Ch 11: Lock Management
- Open Transactions in Realtime
- Open Transaction, Blocking
- LOCKS: Types & Audits
- S, X, IX, U and MD Locks
- Sch-M and Sch-S Locks
- SP_WHO2, SP_LOCK
- sysprocesses & Lock Waits
Ch 12: Isolation Levels
- Lock Hints and Isolation Levels
- Read Committed, Uncommitted
- Serializable, Repeatable Read
- Snapshot Isolation, Versioning
- Read Committed Snapshot
- sysprocesses & sp_who2
- Choosing Correct Isolation Level
Ch 13: Deadlocks, LIVE Locks
- Deadlocks in Real-world
- Profiler Tool & Deadlocks
- Lock Management: Deadlocks
- Deadlock Graphs & Events
- Deadlock Avoidance, Prevention
- Deadlock Prevention
- sysprocesses & sp_who2
Ch 14: Deadlocks, LIVE Locks
- CPU waits
- I/O waits
- locking waits
- memory-related waits
- parallelism waits
- symptom & root cause
Ch 15: AI Enabled SQL, Tuning
- Installing AI Components
- Using AI in SQL
- SQL Queries with Prompts
- SQL Query Analysis
- Plan Regression
- Parameter Sniffing
Ch 16: Real-Time Project for Query Tuning
- Banking Scenario
- Big Data Management
- Lock Management
- Tuning Tools
- Isolation Levels
- Deadlock Management
- Capacity Issues, Solutions
What is SQL Server Performance Tuning Training?
This is a practical training program focused on SQL Server and T-SQL Query Tuning, covering query monitoring, performance troubleshooting, Query Store, indexing, execution plans, AI-enabled SQL tuning, and real-time scenarios.
Who should join this training?
This course is useful for SQL Developers, BI Developers, ETL Developers, Azure SQL Developers, Data Engineers, and Senior Data Analysts who want to improve their SQL performance-tuning skills.
What are the prerequisites for this course?
Basic knowledge of Microsoft SQL Server (MSSQL) is required. Beginners who are completely new to Microsoft databases are advised to first learn the fundamentals of Microsoft SQL.
What performance-tuning topics are covered?
The course covers query monitoring, Query Store, indexes, partitions, statistics, index management, tuning tools, execution plans, server tuning, locks, isolation levels, deadlocks, wait statistics, and AI-enabled SQL tuning. Performance Tuning
Will I learn how to troubleshoot slow SQL queries?
Yes. The training includes identifying CPU/I/O-intensive and long-running queries, analyzing execution plans, using DMVs/DMFs, Query Store, wait statistics, indexes, and other tuning techniques to investigate performance issues. Performance Tuning
Does the course include a real-time project?
Yes. The real-time Query Tuning project uses a Banking scenario and includes big-data management, lock management, tuning tools, isolation levels, deadlock management, and capacity-related issues and solutions.
What support will I receive during the training?
Learners receive LIVE practical training, daily hands-on assignments, practice databases/scripts, SQL interview preparation, resume guidance, and mock interview support. Both LIVE Online and Self-Paced Video training modes are listed in the course.
Training Modes
