Clear Single Use Query from Plan Cache

by Snehashish Ghosh

What is Single Use Plan

Single use plans in the Plan Cache are the plans in the Plan Cache those have been used only once for a defined period of time due to non usage of variable/bind value and use of hard coded literals in the program query.

Example:

 If we execute another query as below:

 another plan will be generated.

Instead if we use the following:

select max(order_date) from orders where where id>@v_id and pass the value of @v_id during runtime

we would have only one compiled plan for the query in the Plan Cache.

Drawback of Single Use Plans

The single use plans increases the size and number of plan cache entry making searching for existing plans for similar queries slower.

It is recommended to clear the Plan Cache from Single use plans in periodic interval

Identify Size and count of Single Use Plans                                                       

 Find Single Use Plans for tuning

 Clear the Plan Cache from all single use plans

  1. Capture all the single use plans for Adhoc and Prepared statements in a cursor
  2. Use dbcc freeproccache (plan_handle) to free up the plan cache for each of the single use queries

 Clear Plan Cache from single use queries

clear_plna_cache_from_single_use_quries

 

Conclusion

The above TSQL block could be scheduled to execute from SQL Server scheduler to run at defines intervals like once in every fortnight or once in every month. This will augment in proper maintenance of plan cache.

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

*