Translation SQL Passion Week 10: Plan cache

Week 9:plan Caching

Adhoc SQL StatementsEach time we submit a specific SQL statement to SQL Server, the query plan compiles for each unique query. What is unique query? Very simple. SQL Server generates a hash value for the full SQL statement (containing the value of hard-coded), which is used as a lookup value in the plan cache. If the hash value is found, then the plan is reused. Otherwise, a new plan will be compiled, so imagine now executing the following 3 queries

SELECT *  fromSales.SalesOrderHeaderWHERECustomerID= 11000GOSELECT *  fromSales.SalesOrderHeaderWHERECustomerID= 30052GOSELECT *  fromSales.SalesOrderHeaderWHERECustomerID= 11223GO

These three queries will be compiled into different query plans because they contain a hard-coded parameter value. As a result, these three query plans are put into the cache, which we call cache pollution.

Because of the hard-coded, these query plans are difficult to reuse and waste valuable cache space.

Plan stabilityWhen we specify a parameter value execution for a query statement or stored procedure, it is easy to apply the plan reuse, but it can also raise a performance issue: When a query plan executes a bookmark lookup lookup (a nonclustered index does not overwrite the query), as Week8 said, If you only retrieve a small amount of data, the Bookmark lookup is said to be a pass. Adding more than a critical point can be more efficient than a full table/index scan. But once the query plan is reused, and these are not considered (whether it reaches the critical point), SQL Server blindly trusts the reuse plan, regardless of the level of performance. Look at the following actual query plan here, SQL Server blindly reused the query plan, predicting the number of rows returned and the actual number of rows returned is very different. The plan in the cache is based on the assumption that the query returns only a small number of records, but actually returns 1499 rows. What we found in the cache was a plan that was optimized based on returning a small number of records. The root cause of this is that we do not have a plan stability, based on the estimated number of return records, we get a bookmark lookup or Table/cluster Scan. This is the most common performance issue. The solution to this problem is to avoid the bookmark Lookup by including the index, regardless of how the parameters change, the same query plan is obtained, the same query performance.

Translation SQL Passion Week 10: Plan cache