SQL Server: Optimizing Ad Hoc Statement Performance

When using ad hoc T-SQL statements, their performance is dependent on you choosing the correct execution method to ensure they are cached correctly and their plans are reused appropriately, and this course will show you all of that and more. This course is applicable to SQL Server developers and anyone who is responsible for writing ad hoc T-SQL statements that must perform well, from SQL Server 2005 onward
Course info
Rating
(383)
Level
Intermediate
Updated
Dec 6, 2013
Duration
7h 15m
Table of contents
Introduction
Statement Execution Methods
Estimates and Selectivity
Statement Caching
Plan Cache Pollution
Summary
Description
Course info
Rating
(383)
Level
Intermediate
Updated
Dec 6, 2013
Duration
7h 15m
Description

This course is about how different ad hoc statement execution methods affect caching, plan reuse, memory and ultimately performance. Knowing when to use each method is important and understanding how SQL Server works will demystify certain behaviors you may have seen but previously have been unable to explain. SQL Server can support any workload, any design, and any data requests but knowing exactly which one is the most beneficial to use can give you better long-term scalability, availability, and performance. Using the wrong method can cause more memory to be wasted and even result in parameter sniffing problems (where subsequent statements perform poorly because of the plan that’s been cached). This course will show you how each statement execution method works, how it’s cached, whether or not it wastes cache, and finally how to test and rewrite the statement to take better advantage of caching. Along the way we will also cover a variety of other necessary features and tools: estimates, statistics, and heuristics; how to analyze query plans; some indexing strategies to improve performance; and plan guides. This course is an absolute must for everyone that works with SQL Server and it’s also an introduction to concepts that will be built upon in future courses. This course is applicable to all SQL Server versions from SQL Server 2005 onward, and for SQL Server developers as well as anyone responsible for writing data access statements to SQL Server tables. You can have any level of experience to gain from this course but those of you who have experienced what seemed odd behavior/performance with your ad hoc statements will probably benefit the most!

About the author
About the author

Kimberly is a SQL Server MVP, Microsoft Regional Director and President/Founder of SQLskills.com, which she now runs with her husband, Paul Randal. Kimberly’s areas of expertise focus on performance tuning through effective database design and architecture.

More from the author
SQL Server: Indexing for Performance
Beginner
7h 11m
Sep 15, 2017
More courses by Kimberly L. Tripp
Section Introduction Transcripts
Section Introduction Transcripts

Statement Execution Methods
Hi, this is Kimberly Tripp from SQLskills. com, and I'm recording this course for Pluralsight. My course is focused on SQL Server and it's titled Optimizing Ad Hoc Statement Performance. You're watching Module 2: Statement Execution Methods. So in this course, we're going to cover a variety of different aspects of statement execution, and in this first module, we're going to cover statement execution methods, and for this, we're going to look at the different ways in which you can execute SQL Statements. We're going to talk about ad hoc statements and how to understand what's occurring when you execute ad hoc statements. We'll talk about sp_executesql, we'll talk about dynamic string execution, and we'll talk a little bit about SQL injection as it relates to dynamic strings. And then after this module, we'll be into estimates and selectivity, statement caching, plan cache pollution, and a summary.

Statement Caching
Hello. This is Kimberly Tripp from SQLskills. com, and I'm recording this course for Pluralsight. My course is focused on SQL Server, and it's titled Optimizing Ad Hoc Statement Performance. You're watching Module 4: Statement Caching. So, in the course what we started out with were the different statement execution methods. We started to move into how SQL Server estimated the number of rows and came up with the plan that was used to execute the statement and gather the data, and now what I want to start doing is tying these different statement execution methods and their plans, whether or not they're optimal or not, into what gets put into the cache. So, the question is what affects ad hoc statement behavior, are there any server or database settings that could change the behavior, and of course what is the default ad hoc statement behavior? Then we'll start talking about what happens with the different text statements that you pass, so something called textual matching. We'll look at ad hoc statements and what deems those statements as safe versus unsafe. I've used those terms a couple of times, but I really want to go into a little bit more detail here. Then we'll talk about what happens when ad hoc statements are cached and whether or not we can see that information in the plan cache and whether or not it creates any unnecessary or unexpected let's say side effects in the plan cache. And then we'll tie it all together with can we change the ad hoc statement behaviors and maybe reduce some of the impact to the cache when things aren't as optimal as they could be. So, really we'll bring all this together in a lot more detail with Plan Cache Pollution, which is our next module, and then I will summarize the entire course in our statement execution summary. So, let's get started with statement caching.

Plan Cache Pollution
Hello. This is Kimberly Tripp from SQLskills. com, and I'm recording this course for Pluralsight. My course is focused on SQL Server, and it's titled Optimizing Ad Hoc Statement Performance. You're watching Module 5: Plan Cache Pollution. So in this course we've look at a variety of different things. We have looked at the statement execution methods that are possible, we talked about estimates and selectivity to get some insight into how SQL Server determined what the best plan was for your statements, we looked at how those statements were cached, and we started to see a potential problem emerge from the fact that every ad hoc statement with its individual literals was put into the plan cache. That results in what we're going to talk about in this module called plan cache pollution. So, I'm going to define place cache pollution, although we already know a little bit about what that's about, we'll talk about how much of your memory can be allocated to the plan cache and what some of the patterns are in the prior editions of SQL Server as well, we'll look at the stores that SQL Server has in the plan cache, we'll be able to verify what's in the plan cache and get some insight into how much of our plan cache is being used or bloated by/polluted by single-use plans because that's really one of our biggest issues, and then we'll what I'm going to do is I'm going to tie everything together, pretty much everything we've looked at in terms of plan cache pollution, CPU, parameter sniffing, and try to come up with a little bit of a better balance between all of these things so that we can reduce our overall footprint on memory, conserve resources, which will ultimately let us get much better use out of the system that we have, and that will also give us better performance. Once I'm done with this, we will wrap everything up with a statement execution summary.

Summary
Hello. This is Kimberly Tripp from SQLskills. com, and I'm recording this course for Pluralsight. My course is focused on SQL Server, and it's titled Optimizing Ad Hoc Statement Performance. You're watching Module 6: Statement Execution. So, in this course we looked at a variety of different aspects of statement execution and how ad hoc statements are effectively handled by SQL Server. We started out with the different methods, we looked at estimates and selectivity and how statistics factor into query processing, we looked at statement caching, we looked at plan cache pollution and some solutions to solve ad hoc statement plan cache bloat, and now we've got our statement execution summary. So, what I'm really going to do is just pull together all of the key points from these modules and remind you of the things that we discussed so that you can pick and choose where you might need to go back and study some of the things that you want further clarification. So, we're going to talk about statement execution, estimates and caching; statement execution methods, caching, and concerns, and I've got a really cool table that kind of brings all that together so I think you'll like that; and then we'll go through some statement execution solutions, and we'll round out with a couple of slides on where I think you should go next in terms of understanding how to optimize your SQL Server environment.