SQL Server plan cache

In this issue of performance Tuning training , I would like to discuss in detail the plan cache and its side effects in SQL Server. As you learned last week, each logical query that is submitted to SQL Server is compiled into a physical execution plan. The execution plan is then cached, known as the plan cache , for later reuse. First, let's look at the side effects of the Ad hoc SQL statement (Adhoc SQL statements, the opposite: Prepared SQL statements), which is the performance issue.

ad hoc SQL statements (Adhoc SQL statements)

Every time you submit an ad hoc SQL statement to SQL Server, there will be an execution plan compiled for each unique query. What does "unique query" mean? The answer is simple: SQL Server generates a hash value for the full SQL statement (including potentially hard-coded parameter values) and uses this hash value as the lookup value in the plan cache. If the execution plan for this value is found, the plan will be reused, otherwise the new plan will be compiled and cached in the plan cache. Take a look at the following 3 queries we submit to SQL Server:

1SELECT*FromSales.SalesOrderHeader23WHERE CustomerID=1100045GO67SELECT*FromSales.SalesOrderHeader 9 where CustomerID Span style= "color: #808080;" >= 3005210 11 go12 13 select * from Sales.salesorderheader14 15 where CustomerID = 1122316 17 go   

For these 3 queries, SQL Server compiles 3 different execution plans because you provide hard-coded parameter values. So the calculated hash value is different between the 3 queries and the cached plan cannot be found. As a side effect, for almost the same query, you have 3 execution plans. This issue is known as plan cache pollution (pollution) .

You pollute your plan cache with different execution plans that can't be reused (because of hard-coded parameter values), and you're wasting a lot of useful memory, which can be used by other components in SQL Server. The purpose of caching should be high reuse that lasts several times, and specific SQL statements do not fall into this situation.

Program Stability

If you argument your SQL statement, or use a stored procedure. In that case, SQL Server can easily reuse the execution plan. But even reusing execution plans can be a performance issue. For example, SQL Server compiles an execution plan for a query that needs to perform a bookmark lookup Because the nonclustered index used does not overwrite the query field.

In the 8th week we said that if you get a small amount of data from a table, the bookmark lookup is still useful. When you cross the tipping point , using a full table/index scan will be more efficient. However, if SQL Server reuses the cached execution plan, it will not consider this choice--sql server will only blindly reuse your plan-even if the performance is very bad! Let's take a look at the following actual execution plan:

Here SQL Server blindly reuses the cached plan that contains the bookmark lookup. As you can see, the estimated row count (estimated number of rows ) is completely different from the actual number of rows (actual numbers of rows ). SQL Server is based on the assumption that the query only returns 1 records to compile and cache the plan. But in fact, SQL Server returned 1499 records. Look at the execution plan, we will be clearer, the optimizer assumes that only 1 records are returned to perform this operation.

This will cause you to have no plan stability . Based on the estimated number of rows, you get a cached plan for bookmark lookups, or a full table/index scan if you cross a critical point. This is a performance issue that we often encounter during performance tuning.

SQL Server plan cache