SQL Query Performance Testing and Tuning

This one-day hands-on seminar explores the impact of your SQL Server query designs on query performance and how you can study, document and tune the performance of your queries. We will explore and compare query patterns to study impacts on performance. The end goal is to write better queries!

Module 1 - Explore query performance optimization

This module is a review of relational databases and SQL queries and a study of Query Plans for comparing multiple SQL query solutions.

• Introduction
• Relational database design
• Transactional vs. reporting database design
• About the Query Store
• About SQL Server Statistics
• Are you smarter than SQL Server?
• About Query Plans
• Testing alternate solutions by comparing Query Plans
• Dynamic Management Views and Functions
• Summary

Module 2 – Explore performance impacts of indexes and data types:

• Introduction
• Performance impact of data types
• Performance impacts of indexes
• Who can/should create indexes
• Should you use temporary tables?
• Summary

Module 3 – Query Hints and Locking

• Introduction
• Exploring the SQL Server blocking and locking patterns
• Testing for and working around blocking locks
• Working with query hints
• Summary

• Knowledge of the SQL language, particularly the Microsoft T-SQL dialect, at a basic to intermediate level.
• Ability to use various tools for running queries against a Microsoft SQL database, either on-premises or cloud-based. These could include SQL Server Management Studio, Visual Studio Code with the SQL extensions or Azure Data Explorer.
• Basic understanding of SQL Server indexes.
• Basic relational database concepts.