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.

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
FROM
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.

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)