Execution plan cache in SQL Server due to performance disturbances from long cache times

The source of this article: http://www.cnblogs.com/wy123/p/7190785.html

(It is not the original works right to retain the source, I my book still far to reach, just to link to the original text, because the following may exist some errors to amend or supplement, without him)

A performance issue was first thrown, and a few days ago, a stored procedure with a very low production environment was encountered, and the developer guessed that the execution of the stored procedure should not be so slow, based on the specific business logic and the amount of data returned.
At the time, I realized that it might be the problem of executing the plan cache, because the current stored procedure is still in compliance with the specification of the parameterized SQL (if dynamic ad hoc query SQL is not a problem)
Interestingly, the relevant parameters provided by the developer are exactly the same as the parameters at compile time, which is the focus of this paper.
It then queries the cache execution plan on the current server, the time it takes to cache the execution plan, and the parameters of the current cached execution plan compile time.
After the query to the execution plan of the corresponding stored procedure cache, it is found that the execution plan is quite different from the execution plan that currently executes SQL directly into the parameters.
Of course, the problem is not entirely consistent with parameter sniffing, so the parameter sniffing issue is not discussed.
The execution plan has been cached, and the current query has hit the cache of the previous execution plan, but the cached execution plan may not be the most (relative) optimization for the current query.
After all, the execution plan has been cached for more than 1 days.
So, is the cached execution plan appropriate for the execution of the current statement? If it is not suitable for the execution of the current statement, and how to deal with, similar problems in the long run, how to avoid?

Execution plan cache information for a specific statement

Refer to the following SQL to query the execution plan cache information for some specified statements.

  SELECTSt.Text,        SUBSTRING(St.Text, (Qs.statement_start_offset/2)+1,            (( CaseQs.statement_end_offset when -1  Then datalength(St.Text)            ELSEQs.statement_end_offsetEND -Qs.statement_start_offset)/2)+1), Qp.query_plan, Qs.plan_handle, Qs.sql_handle,db_name(st.dbid) asdbname, Qs.creation_time, Qs.last_execution_time,getdate() ascurrenttime, Qs.execution_count, Qs.last_worker_time, Qs.last_physical_reads, Qs.last_log Ical_reads, Qs.last_elapsed_time fromsys.dm_exec_query_stats QS CrossAPPLY sys.dm_exec_sql_text (Qs.sql_handle) asStOUTERAPPLY sys.dm_exec_query_plan (Qs.plan_handle) asQPWHERE 1=1     andSt.text  like '% fuzzy matching stored procedure with specific marked SQL text%'Order  byCreation_timedesc 

For a query result similar to the following, from the query to the results can be seen, directly open Query_plan XML, you can see the execution plan of the statement in this stored procedure, currently I am just an example

For the current cache of the execution plan of the compilation parameters, you can copy the Query_plan XML information, with a text editor such as notepad++ format after the display, the query compiler when the parameters,
Reference, in the last part of the XML information, or you can directly follow the keyword search parameterlist, you can find the compile execution plan when the specific parameter values.
In Parameterlist's child node, column is the parameter, and the value of Parametercompiledvalue is the value that compiles the execution plan.
In this way, it is possible to determine whether the cached execution plan has a large difference between the parameters of the compiled and the currently running parameters, and how long the cached execution plan is generated and cached.

For the first mentioned problem, the result is a statement in the currently executing stored procedure whose execution plan has been cached for more than a day because it has not been brought to a condition that triggers a "recompile", and the cache continues to remain in memory.
Since the data in the database is constantly changing, the execution plan compiled at that time, even if the parameters are exactly the same, does not necessarily apply to the current query, the performance problem is generated.
Why is the execution plan compiled with a completely inconsistent current, and the cache has not been cleaned up so far?

There are two reasons for personal speculation, but it is not entirely certain that
One is based on the current data distribution (statistical information) obtained by an implementation plan, it may be that the statistics are not accurate, but there is no external factors to promote the implementation of the plan to re-compile
The second is that the compiled execution plan itself is unreasonable, the compilation of the execution plan is related to many factors, even the memory pressure can not compile a relatively excellent execution plan,
SQL Server execution plan generation, and is not always "most efficient", but relatively efficient, when the memory pressure is low, compiled execution plan, may be large memory pressure when the compiled execution plan is different
In fact, the first reason for speculation there is a certain contradiction,
The latter is more likely, because if the factor causing the recompilation does not change, the same parameters, the current execution will be the same as the cached execution plan, if there are factors that lead to recompilation, then the cache execution plan itself will be cleaned out.

What's more, if you cache a parameter with a lesser probability, or a parameter similar to the one that produces parameter sniff (of course parameter sniff problem is another matter), the effect is even greater.

The following is a production environment on the server, you can see that in the business stored procedures or SQL statement execution plan cache, more than 20 days can be cached, and also cache more than 3 days, 4 days.

It is observed that SQL Server's memory footprint in the execution plan cache is not easily cleaned up, and it can be assumed that this portion of memory (relative to the data cache) has a higher priority.
In other words, when the data cache is under a lot of pressure (ple can be as low as a minute), some execution plans are still cached well.
Unfortunately, in some cases, the cached execution plan, instead of providing better performance, is slow performance because it does not apply to the current query.

Basic knowledge of execution plan caching

1, what is the execution plan cache

After the SQL Server query engine receives the SQL statement, it is first syntactically parsed, then compiled, and then the execution plan is generated, and SQL Server caches it to meet the requirements of the cache.

2, the role of the execution plan cache

By reducing SQL compilation, SQL Server will be able to execute directly using the cached execution plan when it receives the execution plan cache, and avoids the resource and time consumption caused by recompilation.

3, space occupied by the current execution plan cache

Refer to the following SQL, you can use Sys.dm_os_memory_clerks or Sys.dm_exec_cached_plans to query the execution plan cache already occupied space.

--CACHESTORE_SQLCP, dynamic SQL, precompiled SQL execution PlanSelect *  fromsys.dm_os_memory_clerkswhereType= 'CACHESTORE_SQLCP'--proc: Stored Procedures--prepared: Pre-defined statements--adhoc: Dynamic QuerySelect sum(size_in_kb)/1024.0/1024.0  asSize_in_gb from(    SELECTObjType, Size_in_bytes/1024.0  assize_in_kb fromSys.dm_exec_cached_plansWHEREObjTypeinch('Proc','Prepared','Adhoc')) T

The following is the execution plan cache information on a server, which is basically consistent with the results of the query.

4, the maximum space that the execution plan cache can occupy

The memory space occupied by the execution plan is a type of memory known as Stolen memories, Stolen memory includes the execution plan cache, and the session real-time computing needs of the RAM, such as sorting, aggregation calculation, hash join and so on.
The allocation of stolen memory in SQL Server has a formula, after SQL Server 2005 SP1, presumably the following
If Target mermory (that is, the maximum memory space available for SQL Server)

1) less than 8GB in case: Stolen Memory = Target mermory * 75%
2) If Target mermory is between 8GB~64GB: Stolen Memory = 8 * 75% + (target Mermory-8) *50%
3) If Target mermory is greater than 64GB: Stolen Memory = 8 * 75% + (64-8) *50% + (target Mermory-64) *25%

It can be seen that the Stolen memory is not linearly increasing with the increase of the maximum available RAM, but the degree of increase tends to decrease (the derivative of the increase is reduced)
In addition, in the post-SQL Server version of the stolen memory is also in control of the maximum usable capacity, specific reference to the SQL Server 2012 implementation and management of the actual combat Guide + here.

It is important to note that the maximum space occupied by the execution plan cache is only part of the stolen memory, not all of stolen memory.

Then the problem is, it can be thought that the execution plan cache can occupy the maximum space is not directly controlled,
The larger the target mermory of SQL Server, the greater the Stolen memory, and the larger the Stolen memories, the greater the amount of space available for the execution plan cache.
Although the implementation of the plan has an aging clean-up mechanism, single-person feeling is not flexible enough, or can not control strong (now understand that other relational database, open up a lot of configurable parameters of the role, of course, not to say can be arbitrarily configured, want to change)
The larger the memory footprint of the execution plan cache, the better is it?
Certainly not, the problem mentioned at the beginning is attributed to this, precisely because the stolen memory has not yet been given the ram pressure, the execution plan cache has been stored in memory, but the relatively long-stored execution plan is not suitable for the current query.

5, about the configuration of ad hoc query sql

This point is not very important, simply mention, for ad hoc, also known as dynamic SQL, because its parameters are likely to change every time, like SQL is not necessary to cache,
For the first time, only one execution plan's "stub" is stored instead of the entire execution plan, and only SQL execution of the same parameters is executed more than once before the entire full execution plan is cached.

-- Open optimize for ad hoc workloads ' Show advanced Options ',1reconfigurego'optimize for ad hoc workloads ',1reconfigurego

What is the point of this configuration? Memory is a very important resource for the database, if the stolen memory can be configured to use all the memories, on the basis of satisfying stolen memory as much as possible, more and longer cache data is kingly.

If you resolve an execution plan that is potentially overdue

The above analysis shows that the execution plan cache is still in memory when the memory pressure is not yet felt by stolen, or if the execution plan cleanup mechanism has not cleaned up the aging execution plan cache.
However, the execution plan of this cache, because it is the statement compiled when the data generated by the plan, is not necessarily suitable for the current query, or for the current query is not optimized.
This is required, in some cases, to artificially clean up the execution plan cache.
SQL Server provides a system feature for DBCC FREEPROCCACHE to clean up the execution plan cache, the parameters are the specific execution plan handle (plan_handle) mentioned above, and the parameters are all clear, note that you can.

-- Remove The specific plan from the cache. Clear specific execution plan DBCC Freeproccache (0x060006001eca270ec0215d05000000000000000000000000); GO

For DBCC FREEPROCCACHE to clean up the execution of a specific plan cache, this method can only be used for emergency use.
If a similar problem occurs frequently, in the case of sentiment, I personally feel that it is unreasonable and definitely the responsibility of the DBA.

More often than not, the execution plan has been cached for some time, which should have been cleaned up, and those that can be used to continue caching? This is difficult to define subjectively.
There is even no valid reference information, because you do not know that 3 days ago compiled the execution plan, after three days of cache, for the current query is still the most (more) optimization.
This can be explored in practice based on specific scenarios, such as the ability to clean up the execution plan cache for more than 1 days based on the build practice, or to set the practice threshold to a larger point, but not too long, such as three days and five days
Personally, since the execution plan can be cached, it can still be cached after being cleaned up, but the execution plan, which is clearly recompiled later, will be much more accurate and the cost of a recompile is worthwhile.
Ideally, once an execution plan is compiled, it is relatively ideal to be able to cache (reuse the execution plan) day.

Of course, this does not include "recompiling with recompile statement stored procedures or OPTION (RECOMPILE) statement-based refactoring" to avoid execution of the plan cache.
Execution plan Cache This is in the conservation of resources and improve performance considerations, but all things are specific two-sided, beneficial must have the disadvantage.
Under normal circumstances, it is also not recommended to use with RECOMPILE or option (RECOMPILE) to force the compilation of stored procedures or SQL statements, most of the time following good specifications.

Probably think about it, using SQL similar to the following, using the dynamic execution of DBCC FREEPROCCACHE, can achieve the desired purpose.
Of course, execution mode, execution time, time threshold can be adjusted according to the specific situation.

  if object_id('tempdb: #t_remove_expired_plan') is  not NULL    Drop Table#t_remove_expired_planGOCreate Table#t_remove_expired_plan (IDint Identity(1,1), Plan_handlevarbinary( -))GOInsert  into#t_remove_expired_plan (plan_handle)SelectQs.plan_handle fromsys.dm_exec_query_stats QSwhereCreation_time< DateAdd(HH,- -,getdate())GODeclare @exists_data bit = 1Declare @v_plan_handle varbinary( -)Declare @str_sql varchar( +) while @exists_data = 1begin    Select Top 1 @v_plan_handle =Plan_handle from#t_remove_expired_planif(@v_plan_handle  is  not NULL)    begin        Executesp_executesql N'DBCC Freeproccache (@plan_handle)'N'@plan_handle varbinary ($)',@plan_handle = @v_plan_handle    End    Delete Top(1) from#t_remove_expired_planif exists(Select 1  from#t_remove_expired_plan)begin        Set @exists_data = 1    End    Else     begin        Set @exists_data = 0    EndEnd

Summarize:

The execution plan cache was originally intended to reduce the resource and time consumption of compiled SQL, and to improve SQL execution efficiency in the presence of reusable execution plan caches
SQL Server has its own cleanup mechanism for how the aging cache plan is handled, but it still lacks some flexibility.
However, in some special cases, the cache execution plan may interfere with the execution efficiency of the current SQL for performance issues.
There may be no problem with the external environment (server resources, Cpu,io, memory, network, etc.), the SQL itself is not a problem, and there is no lack of indexes, and so on, but the execution efficiency can not hit the expected situation,
This is a good time to consider whether the cache execution plan causes the current SQL to take an unreasonable way of executing.
　

Microsoft's platform, has been in line with simple and easy to use, fast-start features, the user has done a lot of encapsulation (shielding), in the personalized configurable, and other databases, especially open-source products compared to some shortcomings.
However, it is possible to achieve the purpose of "configurable" by indirect means.

Execution plan cache in SQL Server due to performance disturbances from long cache times