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.
Course info
Rating
(37)
Level
Intermediate
Updated
Aug 18, 2016
Duration
1h 28m
Table of contents
Description
Course info
Rating
(37)
Level
Intermediate
Updated
Aug 18, 2016
Duration
1h 28m
Description

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.

About the author
About the author

Gail Shaw is a Data Platform MVP and holds the MCM certification for SQL Server. Her specialties are in performance tuning and database recovery for SQL Server. She is a frequent poster on the SQL Server Central forums, writes articles for both SQLServerCentral.com and Simple-Talk.com, and often speaks at SQLBits and the PASS Community Summit.

More from the author
Section Introduction Transcripts
Section Introduction Transcripts

Course Overview
Hello everyone, my name's Gail Shaw. Welcome to my course, Identifying & Fixing Performance Issues Caused by Parameter Sniffing. I'm a technical lead in the Data Solutions Division at Intellect Software, and I frequently write about database performance tuning. Database performance problems are a perennial problem, so much so that the database is often blamed by default. Well, that or the network. While consistently poor database performance is certainly a problem, erratic performance can be far more annoying to users and far harder to fix. Bad parameter sniffing is a common cause of such bad performance. In this course we're going to see what parameter sniffing problems are, why they occur, how to identify them in a production environment, and how we can fix them. Some of the major topics we'll cover include the behavior of the optimizer, including why it even looks at parameter values, when plans are cached, and how they were used. We'll also look at how we can use DMVs, Extended Events, and SQL Server 2016's Query Store to detect parameter sniffing problems in production. We'll finally look at some options to fix bad parameter sniffing and stabilize query performance. By the end of this course you'll know how to detect and fix any cases of bad parameter sniffing in your systems. Before starting, you should be familiar with T-SQL, and the basics of reading execution plans. I hope you'll join me on this journey to learn how to conquer erratic query performance with the Identifying & Fixing Performance Issues Caused by Parameter Sniffing course, here at Pluralsight.

Conclusion
Hi, and welcome to the last module in this Pluralsight course on Performance Problems Caused by Bad Parameter Sniffing. In this module, I'll quickly recap the material that we've covered in this course, the behavior of the optimizer, how to detect bad parameter sniffing, and the options that we have for fixing their problems, and then I'll look at some additional resources for those who want to study further. But first, let's revisit our imaginary internet bookseller, and see how our sales analyst is doing.