Let’s say you’re facing parameter sniffing issues for a particular stored procedure. Everything was running smoothly after those first couple runs but now a different value is being passed into the stored procedure causing headaches. What are your available options that you can use to clear out the plan cache? Let’s take a look at three.
- Restart the SQL server – This would be the sledgehammer approach. Restarting the SQL server service or the server that SQL is running on will clear the plan cache; however, that poor performing query isn’t going to run at all when services are restarting and the application is not accessible. This would not be a good place to start for clearing out the plan cache.
- Run DBCC FREEPROCCACHE – No downtime is required and the entire plan cache will be cleared out. This is a better option but still has some drawbacks. When you clear out the entire plan cache, all plans that were previously in cache will have to be rebuilt as queries come in. There will be an increased utilization on CPU and memory while the plans are built as SQL is seeing them for what it is perceiving as the first time.
- Run DBCC FREEPROCCACHE (PLAN_HANDLE) – Only the cached plan for the designated plan handle will be removed and the rest of the plan cache will remain intact. This is the best of these options for removing the problematic plan and not causing overhead in other areas.
For example, let’s find the plan for the usp_GetUserHistory stored procedure because it is having performance issues. We could run this script to see the query text, plan handle, and query plan:
|
1 2 3 4 5 6 7 8 |
SELECT [text], [plan_handle], [query_plan] FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT) CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) WHERE [text] like '%usp_GetUserHistory%'; GO |
We could then take that plan_handle value and plug it into DBCCFREEPROCCACHE like this:
|
1 2 3 |
DBCC FREEPROCCACHE (0x050005007ED2CF48D022DA135602000001000000000000000000000000000000000000000000000000000000); |
We could also use sp_BlitzCache from the Brent Ozar First Responder Kit and run:
|
1 2 3 4 |
EXEC sp_BlitzCache @StoredProcName = 'usp_GetUserHistory'; GO |
Scroll all the way to the right and you’ll find a column called “Remove Plan Handle From Cache” with the value that you can copy, paste, and run to remove the plan.
Just like when thinking about security and granting the minimum permissions necessary, applying that same idea here of only making the minimal change necessary will be the better way to go.
Thanks for reading!
Add comment