Analyze, diagnose, and improve SQL Server performance
Slowdowns in SQL Server are often attributed to hardware or infrastructure. However, in the majority of cases, the real causes lie within queries, indexes, statistics, or the optimizer's choices. Without a diagnostic method, interventions become long and costly, without necessarily producing the expected results.
This training gives you the tools to analyze SQL Server performance, quickly identify the root causes of problems, and apply the right optimization strategies. Through demonstrations and practical labs, you will develop a solid approach that you can reuse immediately on your SQL Server environments, whether they are on-premises, virtualized, or in the cloud.
Is it for you ?
This course is aimed at database administrators, developers, operations or network managers, system engineers, professionals in development, databases, networks and Web specialists.
Prerequisites
Experience with MS SQL Server 2008 R2 administration (Courses MS509) ; Knowledge of SQL Server, database manipulation and SQL language ; experience as a database developer.
What You'll Walk Away With
- ✓ Diagnose the root causes of performance issues in SQL Server. Especially what to do and when to do it.
- ✓ Optimize T-SQL queries to reduce their resource consumption.
- ✓ Effectively leverage analysis tools and take advantage of the new features introduced up to SQL Server 2025.
- ✓ Apply a proven methodology to sustainably improve the performance of your production SQL Server environments up to version 2025.
Training content
1 SQL Server Optimization Fundamentals
- Understanding the main factors influencing performance.
- Identifying the most common bottlenecks.
- What's new in SQL Server 2025 regarding optimization.
- The specificities of virtualized and cloud environments.
2 The Query Optimizer and Execution Plans
- Understanding how the query estimator works.
- Role and inner workings of the optimizer: query optimization.
- Reading and understanding an execution plan.
- Being able to compare two different plans.
- Understanding cardinality estimations.
3 Using Performance Analysis Tools
- Monitoring and measuring performance with the right tools: system stored procedures, Query Store, Extended Events, and Dynamic Management Views (DMVs).
4 SQL Server Index Architecture
- Definition of an index, its role, and the different possible types (Clustered vs. Nonclustered).
- Optimizing SQL Server using indexes (creation, options, structure, configuration).
- Index behavior during query execution.
- Missing Indexes: when should you listen to them... or ignore them?
5 SQL Server Statistics
- Understanding and demystifying how statistics work.
- Automatic updates and cardinality estimation.
- The impact on execution plans.
- Improving decision-making with adequate statistics.
6 Efficient Resource Utilization
- Understanding how the plan cache works.
- Improvements introduced in recent versions of SQL Server (2022 and 2025).
- Recompilation of queries and procedures.
- Parameter-sensitive queries and parameter sniffing.
- Parameter Sensitive Plan (PSP) and Memory Grant Feedback.
- Reducing server resource consumption.
7 SQL Server Database Maintenance
- Understanding the actual impacts of fragmentation and avoiding misconceptions.
- In which cases is fragmentation not the real problem?
- Adjustments to make with the fill factor.
- Maintenance of indexes and statistics.
- When should you rebuild or reorganize an index?
8 T-SQL Query Optimization
- Optimizing your queries and T-SQL code.
- Writing SARGable queries.
- How to avoid poorly written queries.
- Rewriting problematic queries.
- Optimizing without adding indexes.
- T-SQL development best practices.
9 Locks
- Locking, blocking, and deadlocking.
- Managing locks with the optimal isolation level.
- Lock escalation and deadlocks.
- Reading a Deadlock graph.
10 Performance Diagnostic Methodology
- SQL Server health check checklist.
- Where to start when an application is slow?
- Quickly identifying the culprit query.
- Choosing the right tool based on the context.
- Validating the actual impact of an optimization.
- Avoiding false diagnostics and common traps.
11 Labs Included in Your Training
- Quickly identifying the most costly queries using DMVs, Query Store, and Extended Events.
- Analyzing and comparing execution plans to explain performance differences and comparing them with artificial intelligence (AI) suggestions.
- Designing the most appropriate indexes and measuring their actual impact on performance.
- Optimizing T-SQL queries by rewriting code without modifying the infrastructure.
- Distinguishing fragmentation issues from the real causes of slowdowns.
- Fixing non-SARGable queries and measuring the gains achieved.
- Resolving scenarios inspired by real-world cases encountered at client sites.
Keep in Mind
This training prioritizes a practical and visual approach over learning by rote memorization. Complex concepts are explained using comparisons, analogies, and demonstrations inspired by everyday situations in order to make them easier to understand and apply in a professional context. The goal is not only to know what to do, but above all to understand why SQL Server makes certain decisions and how to think like its optimizer. Each concept is illustrated by demonstrations, practical labs, and concrete examples drawn from more than 20 years of hands-on experience in the field.
📌 Practical information
Our training sessions are offered in Montreal or Quebec City, in person or in a virtual classroom. Dates and locations are specified when you select your session below. If you have any questions, check out our FAQ.
