SQL SERVER – Remove All Query Cached Plans Not Used In Certain Period

One of the reasons, I love my job of SQL Server Performance Tuning Expert is that I get to learn every single day something new while working with my client on Comprehensive Database Performance Health Check. Recently I got a very unique situation where I had to Remove All Query Cached Plans Not Used In Certain Period. Let us learn about that today.

SQL SERVER - Remove All Query Cached Plans Not Used In Certain Period Query-Cached-Plans-800x310

Story in Brief

During the consulting engagement, we scientifically and systematically worked on my client’s server and build an action plan which will improve their server’s performance by multiple times. Later on, we implemented all the suggestions one by one and made sure that the server works amazingly fast.

However, after following all the performance tuning steps, there was still a problem that few of the stored procedures were still working very slow. After a quick research, we figured out that all the stored procedures which had brand new plans were running extremely fast but the stored procedures plans which were built before we made the changes were running very slow.

Remove Query Cached Plans

This leads us to the situation when we decided to remove the query plans which were older than certain interval. As we had implemented the changes just four hours earlier, it made sense for us to remove all the plans which were 4 hours or older.

Let us see the query which we used to identify all the cache plans which were older than 4 hours.

SELECT plan_handle, creation_time, last_execution_time,
execution_count, qt.text,
CONCAT('DBCC FREEPROCCACHE (',1,convert(varchar(max), qs.plan_handle, 2),')') ClearCache
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
WHERE DATEDIFF(hour,creation_time, GETDATE()) > 4 -- Change 4 to your hour

When I ran above the query, it returned me all the queries with their cache plans which were older than 4 hours. I have also created the very last column which will create a DBCC FREEPROCCACHE command to flush all the caches which are retrieved in the query.

Now just run the last column containing DBCC FREEPROCCACHE and you will be able to remove all the cache older than certain hours.

SQL SERVER - Remove All Query Cached Plans Not Used In Certain Period clearcache

Please note that while it is not recommended to run DBCC FREEPROCCACHE on the production, there have been many instances in my recent consulting engagement where I have run it and fixed the server’s peculiar problems. I will discuss it in future blog posts.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Execution Plan, SQL Cache, SQL DateTime, SQL Function, SQL Scripts, SQL Server, SQL Server DBCC
Previous Post
SQL SERVER – TempDB and Trace Flag 1117 and 1118 – Not Required
Next Post
SQL SERVER – Reducing TempDB Recompilation with Fixed Plan

Related Posts

3 Comments. Leave new

  • Jeffrey Mergler
    March 20, 2020 2:28 am

    Hi Pinal, wouldn’t it be more useful to base this on last execution, rather than creation time?

    WHERE DATEDIFF(hour,last_execution_time, GETDATE()) > 4

  • Jeffrey Mergler
    March 20, 2020 2:42 am

    Actually, I take that back: I didn’t thoroughly read your use case at the beginning and just dove directly into code. :) Creation time actually makes sense. Sorry about that.

  • In my case, the last column command is not formatted correctly. You have to use the 0x number under the plan handle column to make the command work. Otherwise a very useful bit of information.


Leave a Reply