SQLskills 101: The Other Bad Thing About Clearing Procedure Cache

Every time you execute a query against SQL Server, it goes through optimization and compilation and a query plan is generated. Most of the time that plan goes into cache (there’s always exceptions, which is a discussion for a separate time), which means that the next time that query executes, SQL Server can use the same plan.  Now, re-using the same plan is typically a good thing; SQL Server doesn’t have to go through full optimization and compilation again.  But sometimes that plan isn’t ideal for the query and you want to remove it from the plan cache.  You can do this manually.  Plans can also age out if they’re not being used, but if I want to purposely evict a plan from cache, I have several options:

Ideally, you should remove only what’s absolutely necessary.  Using DBCC FREEPROCCACHE is a sledgehammer approach and typically creates a spike in CPU as all subsequent queries need to have their plans re-generated.  Glenn gives examples on how to use each statement (and others) in his post Eight Different Ways to Clear the SQL Server Plan Cache, and I want to show you one more thing that happens when you clear a plan (or all plans) from cache.

For this demo script, I recommend running it against a TEST/DEV/QA environment because I am removing plans from cache which can adversely affect performance.

We’ll  run one statement and one stored procedure multiple times against the WideWorldImporters database:

/*
Create a stored procedure to use for testing
*/
USE [WideWorldImporters];
GO

DROP PROCEDURE IF EXISTS [Application].[usp_GetCountryInfo];
GO

CREATE PROCEDURE [Application].[usp_GetCountryInfo]
@Country_Name NVARCHAR(60)
AS
SELECT *
FROM [Application].[Countries] 
JOIN [Application].[StateProvinces] [s]
ON [s].[CountryID] = .[CountryID]
WHERE .[CountryName] = @Country_Name;
GO

/*
Remove everything from cache
*/
DBCC FREEPROCCACHE;
GO

/*
Run the stored procedure 20 times
*/
EXECUTE [Application].[usp_GetCountryInfo] N'United States';
GO 20

/*
Run the query 20 times
*/
SELECT
[s].[StateProvinceName],
[s].[SalesTerritory],
[s].[LatestRecordedPopulation],
[s].[StateProvinceCode]
FROM [Application].[Countries] 
JOIN [Application].[StateProvinces] [s]
ON [s].[CountryID] = .[CountryID]
WHERE .[CountryName] = 'United States';
GO 20

Now let’s see what’s in cache, and what’s also in sys.dm_exec_query_stats (which gives us execution statistics).

SELECT
[qs].[last_execution_time],
[qs].[execution_count],
[qs].[total_logical_reads]/[qs].[execution_count] [AvgLogicalReads],
[qs].[max_logical_reads],
[t].,
[p].[query_plan]
FROM sys.dm_exec_query_stats [qs]
CROSS APPLY sys.dm_exec_sql_text([qs].sql_handle) [t]
CROSS APPLY sys.dm_exec_query_plan([qs].[plan_handle]) [p]
WHERE [t]. LIKE '%Application%';
GO
Text, plan, and query stats from the DMOs

Text, plan, and query stats from the DMOs

 

 

 

 

Cool, I see that each one executed 20 times, and in addition to the text and the plan I pulled information about I/Os.

Now let’s clear procedure cache, and we’ll take the good old sledgehammer approach because we’re lazy and run DBCC FREEPROCACHE.  Then run the query to see what’s in sys.dm_exec_query_stats…

DMO information *after* running DBCC FREEPROCCACHE

DMO information *after* running DBCC FREEPROCCACHE

 

 

 

 

 

 

Do you see what happened?  In addition to removing the plan, we also lost all the query execution stats from dm_exec_query_stats.  We have no history about query performance (unless we were writing it to another table on a regular basis, or have a third-party tool that captures baselines).  If you don’t believe me, just run SELECT * FROM sys.dm_exec_query_stats…all that data is gone (but will start to re-populate as you run queries and plans go into cache).

This is important to know! If you’re troubleshooting a performance issue and you want to try removing the plan from cache, be aware that you’re removing execution statistics too (and maybe save those off first in case you need them for reference/comparison later).

Migrating to In-Memory OLTP…what’s stopping you?

In the past few months I’ve had several clients reach out about migrating to In-Memory OLTP solutions.  When the second or third request rolled in I remember thinking, “Finally!”  As in, I’ve been wondering why businesses haven’t been looking to implement In-Memory sooner.  In fact, I added a section on In-Memory to our IEPTO2 course because with the availability of the feature in SQL Server 2016 SP1 (yes, there are memory limitations, but it’s a good place to start) I figured we would see an uptick in interest.  But here we are, half way through 2017 and over 6 months since the release of SQL Server 2016 SP1, and I still see a lot of hesitation around it.

I wrote a post over on SQLPerformance last week, Testing DML Statements for In-Memory OLTP, and that generated some discussions on Twitter.  So I figured it was time for a post to find out what’s holding companies back.  This isn’t a true poll – it’s a fill-in-the-blank.  As in: post a comment.  If you have considered migrating to In-Memory and then didn’t, I want to understand why.  I recognize there are limitations – the product is still new and it’s evolving.  But perhaps if we understand the largest inhibitors to migration we can help move them up on Microsoft’s list via Connect and other pathways.  Understand I am asking for specifics here, for example: we can’t use In-Memory tables because they don’t support spatial data types.  Whatever the reason, share it via a comment.

I’m giving this a week or so, then I’ll write a follow up either discussing trends, debunking myths, showing some testing, or perhaps just talking about why I’m sad that no one uses In-Memory OLTP.  🙂  In all seriousness, I have no idea what the follow up will look like, it all depends on whether you and your friends share info!  Until then, thanks for reading and commenting!

(And if you’re in the US, please have a safe 4th of July…no one wants to go to the hospital after trying to set off fireworks at home…just ask my friend who’s a hand surgeon, it’s her least favorite time of year!)

Fireworks from the professionals

Fireworks from the professionals