Identifying & Fixing Performance Issues Caused by Parameter Sniffing
Bad parameter sniffing can wreak havoc on your database's performance, and can be difficult to troubleshoot. This course covers common causes of parameter sniffing problems, how to identify them, and options for fixing the root problem.
What you'll learn
Bad parameter sniffing is a common cause of erratic performance in SQL Server databases. In Identifying & Fixing Performance Issues Caused by Parameter Sniffing, you'll learn how to detect and fix parameter sniffing problems in your production environments. First you'll see how the query optimizer generates plans and discover what the root cause of bad parameter sniffing is. Next you'll learn how to detect instances of bad parameter sniffing using DMVs, Extended Events and the Query Store. Finally, you'll learn four options for fixing bad parameter sniffing. When you're finished this course, you'll be well positioned to tackle erratic query performance in production SQL Server environments. Software required: SQL Server 2012 or later.
Table of contents
- Characteristics of Parameter Sniffing Problems 4m
- General Approach for Identifying Parameter Sniffing Problems 5m
- Tools for Identifying Bad Parameter Sniffing 3m
- Demo: Identifying Potential Cases of Bad Parameter Sniffing with DMVs 6m
- Demo: Identifying Potential Cases of Bad Parameter Sniffing with Extended Events 6m
- Demo: Identifying Potential Cases of Bad Parameter Sniffing with the Query Store 2m
- Looking at Execution Plans 0m
- Demo: Capturing Execution Plans 5m
- Testing Potential Cases of Bad Parameter Sniffing 1m
- Demo: Testing to Confirm Bad Parameter Sniffing 2m
- Summary 1m
- Preventing Plan Caching and Reuse 4m
- Demo: Preventing Plan Caching and Reuse 1m
- Fixing Parameter Value Used to Generate Plans 3m
- Demo: Fixing Parameter Value Used to Generate Plans 4m
- Getting Multiple Plans 1m
- Demo: Getting Multiple Plans 3m
- Forcing the Plan 2m
- Demo: Forcing the Plan 1m
- Compare and Contrast Options 2m
About the author
Gail is a Database Specialist focusing on database performance tuning and database recovery, with a particular interest in topics such as indexing strategies, execution plans, and writing T-SQL code that performs well and scales gracefully. Gail holds a Microsoft Certified Master certification for SQL Server 2008 and is a Data Platform MVP. She is a frequent poster on the SQL Server Central forums, writes articles for both SQLServerCentral.com and Simple-Talk.com, and has spoken at SQL Saturday... more