Episode 103
| July 04, 2017
| 42 minutes
Plan Reuse
When we write our queries to the database, SQL Server has to go and figure out the best way to go and bring back the data you asked for. A query plan is created to help SQL Server remember how to get the data for this query. It takes time to make these queries, so the database wants to limit the number of times it has to create the plans so it will try to reuse the plan as much as possible.
Our topic for today’s episode is query plan reuse and the pros and cons with this concept. We will also touch on the concept of parameter sniffing–a technique SQL Server uses to try and figure out the best values to use for the execution plan with the hopes the plan will help the most queries. Special thanks to James Youkhanis for the suggestion.
Episode Quote
“The concept behind this is it’s there to make things a little bit faster by reusing cache plans.”
“Parameter sniffing is a good thing because without it SQL Server wouldn’t be able to optimize your plan for any kind of parameter. But occasionally it goes wrong.”
“I think it kind of comes down again to kind of knowing your system and understanding the problem”
“Optimized for adhoc workloads is one of those parameters that we most of the time will recommend people turn on”
Listen to Learn
4:53 SQL Server in the News
5:00 Ola Hallengren scripts now on GitHub
6:45 What is plan cache?
7:48 Description of T-SQL and its execution plan
10:15 Scenario in regards to statistics and indexes, and data types
11:30 One-time use query plan cache
12:22 SQL Server and the importance of memory
12:50 A specific problem with one-time use query
12:55 Parameterization
17:30 Parameter sniffing
20:25 Stored procedure and plan cache, parameter sniffing issues
23:55 Options to solve parameter sniffing issues, recompiling
27:28 Controlling plan cache size
28:10 Plan cache and flash array
29:27 Idea of ad-hoc workloads
32:30 Needs parameter reports and examples
38:15 One-time use query reports
38:50 Instance level memory report
39:40 More about hints, recompiling and plan guides
Meet the Hosts
Carlos Chacon
With more than 10 years of working with SQL Server, Carlos helps businesses ensure their SQL Server environments meet their users’ expectations. He can provide insights on performance, migrations, and disaster recovery. He is also active in the SQL Server community and regularly speaks at user group meetings and conferences. He helps support the free database monitoring tool found at databasehealth.com and provides training through SQL Trail events.
Eugene Meidinger
Eugene works as an independent BI consultant and Pluralsight author, specializing in Power BI and the Azure Data Platform. He has been working with data for over 8 years and speaks regularly at user groups and conferences. He also helps run the GroupBy online conference.
Kevin Feasel
Kevin is a Microsoft Data Platform MVP and proprietor of Catallaxy Services, LLC, where he specializes in T-SQL development, machine learning, and pulling rabbits out of hats on demand. He is the lead contributor to Curated SQL, president of the Triangle Area SQL Server Users Group, and author of the books PolyBase Revealed (Apress, 2020) and Finding Ghosts in Your Data: Anomaly Detection Techniques with Examples in Python (Apress, 2022). A resident of Durham, North Carolina, he can be found cycling the trails along the triangle whenever the weather's nice enough.
Want to Submit Some Feedback?
Did we miss something or not quite get it right? Want to be a guest or suggest a guest/topic for the podcast?