Join Joey D'Antoni for an in-depth discussion in this video Execution plans introduction, part of SQL Server Performance for Developers.
- [Instructor] So let's talk about query execution. When you issue a query to SQL Server and you press F5 and go ahead and execute that query, a couple of things happen in order. The first thing that's going to happen is that SQL Server is going to parse the syntax of your query. What does that mean? It means that it's going to check to see if your T-SQL that you're submitting is valid and that all of the objects exist. This is a basic check just to make sure all of the objects that are referenced in your query exist and you have valid syntax.
The next thing that's going to happen is SQL Server is going to check its query plan cache. And what is the query plan cache? Well, it's where SQL Server stores execution plans that have been generated already. It's a very expensive process from a performance perspective to generate an execution plan. So if SQL Server has already generated a plan, it's going to try to re-use that plan. If not, it's going to generate a plan that will be kind-of a road map that will show how the data is retrieved and what various operations it needs to perform to retrieve the data to service your query.
So what are execution plans? Like I mentioned, when you issue a query, the optimizer creates a series of operators and these are things like index scans, index seeks, table seeks, table scans, join operators, various joins like merge, nested loops and hash that bring together your data to service your query. The other important thing is that you need to have statistics on your data. The optimizer is only as good at generating a plan as your statistics are. When your statistics are up to date, it gives the optimizer the best opportunity to generate a plan.
A good example of this is if you have a great deal of data skew in your table, and you have a small sub-set of statistics, the query optimizer may think it only requires a small number of rows when it needs millions. So it may choose a sub-optimal execution plan. Like we mentioned earlier, we have the plan cache. Because this whole process of generating plans is quite expensive in terms of performance. The SQL Server optimizer generates a number of plans when it generates one for your query, it then selects the best plan.
So, computationally, this is very expensive. So it's a major performance benefit to cache those execution plans and then re-use them. These plans are all stored by the database engine in that cache in some dynamic management views, and the query store, if you're using SQL Server 2016 or higher, and those plans are all stored as XML, which makes them queryable. You can take advantage of this to search out opportunities for improving your overall performance. So let's walk through the process for how query plans are generated.
The first time you execute a query, assuming it's not in the cache, being the first time you've run the query, the optimizer generates several plans and you'll be able to see this in your plan. And it chooses the one with the lowest cost. A common question is, what is cost in terms of SQL Server? The cost is a measure of how much cpu, IO, and memory required to service that given query. You'll notice the cost actually has a score, and that's related to the number of seconds that a query would take to complete.
Believe it or not, on a server in Redmond, at Microsoft, about 25 years ago now, that was when they built the scoring engine for the optimizer. The optimizer also uses the statistics we talked about. And statistics are generated on indexes, columns, and tables. And we can even have multi column statistics. And based on those statistics and the assumed uniqueness and cardinality of the data, it is going to generate a series of operators that it feels will give you the best results in terms of performance for your query.
Most of the time, it does a really good job. Sometimes it's off, and you're going to learn how to read execution plans to better understand that. The process of generating these multiple execution plans is very performance-intensive. Compiling plans consumes a lot of CPU. So what SQL Server does in order to reduce this resource consumption is stores compiled execution plans in a special area of memory known as the Plan Cache. This allows plans for the same query text to be reused time and time again, which will reduce the overall work load on a server.
Your second execution of that plan will always be faster. One metric you can check to see if you have a problem with this is recompilations per second. This is a metric of how frequently SQL Server is recompiling queries and generating new plans. If that's happening too frequently, you may want to consider enabling the setting Optimize for Ad Hoc Queries, which is a database level setting that you can choose. Or you may also want to consider adding more memory to your server, 'cause it could be an indication of memory pressure.
- Query execution
- How to read an execution plan
- What not to do with SQL Server
- Why cursors are bad
- Why scalar UDFs are expensive
- Datatypes and design
- What is a columnstore index?
- Transaction isolation