SQLSkills blogspot

SQL SERVER – Stored Procedure Clean Cache and Clean Buffer

1.DBCC FreeProcCache
2.DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

DBCC FREEPROCCACHE is used for purge all of the parsed query plans out of memory. This is commonly used in development environments, but not as common in a production environment.It clears the procedure cache. Executing it be would cause of an ad-hoc SQL statement to be recompiled rather than reused from the cache. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

Use in a development environment is common, for instance when you are working on performance tuning, or parameterization of queries. You can clear the procedure cache with DBCC FreeProcCache, run the program or web page that may be using the database, then see what is in the procedure cache. This can be useful in finding queries that may need to be parameterized. Another way to use would be to find out what queries are being run by some program. To do this you would start by working with a database that is not being used by others, clearing the procedure cache with DBCC FreeProcCache, then run the program you are trying to figure out, then look at what is in the cache, again this is something that could be done in a development or test environment, but I wouldn’t recommend doing it in production.

Use in a production environment should be rare, this is one of the common things to try when SQL Server is having difficulty. If you are are the point that SQL Server is extremely slow to respond and you have been unable to find the cause, one thing to try is to free the procedure cache with DBCC FreeProcCache and see if that fixes the problem.

DBCC FreeProcCache Syntax:

dbcc freeproccache

[ ( @HANDLE | 'POOL NAME' ) ]

[ WITH NO_INFOMSGS ]

Using DBCC FREEPROCCACHE to clear specific execution plans from the cache
You first need to pinpoint the identifier of the execution plan which you want to clear out of the cache. This is known as a “plan handle” and you can find the plan handles and the cached SQL by issuing a query against sys.dm_exec_cached_plans and sys.dm_exec_sql_text

SELECT cp.plan_handle, st.text FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE st.text LIKE N'%ThePlanYouAreLookingFor%'

So here I will clear a plan from the cache, firstly by running this query to get the plan inserted to the plan cache.

SELECT TOP 1 * FROM Person.Person;

Now I will attempt to find the plan_handle for the execution plan.

SELECT cp.plan_handle, st.text FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE st.text LIKE '%Person.Person%'

dbcc freeproccache clear sql server plan cache

So I can see that I want to clear out the cache for the second row as displayed in the screenshot.
So add the plan_handle and say bye to that query plan. :) (The plan handle has been deliberately shortened in the code below otherwise my CMS has trouble displaying it :) )

DBCC FREEPROCCACHE (0x060006002FE61B1D40FDAFF501000000010000....);

-- Example 1 (Sledgehammer)

-- Remove all elements from the plan cache for the entire instance

DBCC FREEPROCCACHE;

-- Flush the cache and suppress the regular completion message

-- "DBCC execution completed. If DBCC printed error messages, contact your system administrator."

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

-- Example 2 (Ballpeen hammer)

-- Remove all elements from the plan cache for one database

-- Get DBID from one database name first

DECLARE @intDBID INT;

SET @intDBID = (SELECT [dbid]

FROM master.dbo.sysdatabases

WHERE name = 'AdventureWorks');

-- Flush the procedure cache for one database only

DBCC FLUSHPROCINDB (@intDBID);

-- Example 3 (Scalpel)

-- Remove one plan from the cache

-- Get the plan handle for a cached plan

SELECT cp.plan_handle, st.[text]

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE [text] LIKE N'%/* GetOnlineSearchResultsMonday %';

-- Remove the specific plan from the cache using the plan handle

DBCC FREEPROCCACHE (0x05000800F7BA926C40C15055070000000000000000000000);

Example:

The following example is from a development envioronment using the AdventureWorks2012 Database.

First we connect to AdventureWorks2012 and see what is in the cache.

USE AdventureWorks2012;

GO

SELECT size_in_bytes, text

 FROM sys.dm_exec_cached_plans

 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;

Here we see that there is plenty in the cache. Next we cleare the cache with DBCC FreeProcCache and take another look at what is in the cache.

DBCC FREEPROCCACHE;

SELECT size_in_bytes, text

 FROM sys.dm_exec_cached_plans

 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;

After running DBCC FreeProcCache you can see that there is nothing left in the cache.
DBCC_FreeProcCache2

When the very next query is run, it will need to be reparsed rather than using an already parsed query in the cache. This will take a bit longer than if there was already a parsed plan to run. Lets run 3 queries, then take a look at the cache.

GO

SELECT FirstName, MiddleName, LastName

 FROM person.Person p

 WHERE FirstName like 'John';

GO

SELECT FirstName, MiddleName, LastName

 FROM person.Person p

 WHERE FirstName like 'Mary';

GO

SELECT FirstName, MiddleName, LastName

 FROM person.Person p

 WHERE FirstName like 'Bill';

Notice the GO Statement between each query. This tells SSMS to run each query as a separate batch. Without the GO statement the 3 queries would have been parsed as a single batch.
DBCC_FreeProcCache3

Here we see the results from the three queries. The first two returned results, and the third one had no rows in the result set. Now lets take a look at the cache

SELECT size_in_bytes, text

 FROM sys.dm_exec_cached_plans

 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;

Below we see a total of 20 items in the cache now. the top item highlighted in blue is the query we used to see what was in the cache, the second block highlighted in red contains the 3 queries from above, and the third rest of them are queries being run by SQL or other supporting queries. For instance line 13 is the dm_exec_sql_text which is called from the query above that checks the plan. DBCC_FreeProcCache4

If we wanted to filter this down to just the queries we had written you could do it by adding a WHERE text LIKE … clause to the query as shown here.

SELECT size_in_bytes, text

 FROM sys.dm_exec_cached_plans

 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE text LIKE N'SELECT FirstName%';

Here we see that only the three queries show up, and that each of those three takes up about 40K of memory on SQL Server. DBCC_FreeProcCache5

So why are there three copies of the same SELECT statement, this seems a bit wasteful. Indeed it does, for more information see an earlier post called How much Procedure Cache memory does one SQL Statement take up? There are ways to correct this.

Using DBCC FreeProcCache For A Specific Plan Handle

If you wanted to clear just a single plan handle, and not all the plan handles, you could use the optional parameter called @handle.

To get the plan handle, we start by modifying our earlier query to show us what is in the plan cache. You could leave out the WHERE clause on your own system, but I have it here to show us just the three queries in question from above.

SELECT size_in_bytes, text, plan_handle

 FROM sys.dm_exec_cached_plans

 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE text LIKE N'SELECT FirstName%';

Here we see the same three query plans from earlier, with an addition column called plan_handle. To free a single plan handle, we would just copy the numeric plan handle, and add that into the DBCC FreeProcCache query.

DBCC FREEPROCCACHE(0x060007000100FF3310B8DA7D0600000001000000000000000000000000000000000000000000000000000000);

SELECT size_in_bytes, text, plan_handle

 FROM sys.dm_exec_cached_plans

 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE text LIKE N'SELECT FirstName%';

Where we only see 2 of the three original queries in the plan cache.

How big is my Procedure Cache?

You can run the following query to check the size of your procedure cache.

SELECT count(*) AS NumPlans, objtype as Type,

SUM(size_in_bytes)/(1024.0*1024.0) AS size_in_mb

FROM sys.dm_exec_cached_plans

GROUP BY objtype;

Which produces the following results on my test server.

Database Health Reports and the Plan Cache

You can also view the plan cache using the Database Health Reports application as shown here.

DBCC DROPCLEANBUFFERS:

This statement test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.

DBCC DROPCLEANBUFFERS needs to be preceded by a CHECKPOINT to remove all buffers

DBCC DROPCLEANBUFFERS is a very familiar DBCC command in use during performance tuning exercises. We have always used it to test queries with a cold buffer cache without shutting down and restarting the server. But, did you know that DBCC DROPCLEANBUFFERS alone may not be sufficient? Did you know that it is important to ensure that the transaction log is “cleared” before cleaning the buffers?

I learnt it from the Books On-Line here, did not quite believe it and decided to do my own little test, which I will demonstrate today.

Some important points…

Before we begin, I would like to mention a few important points:

If the first two lines seem alien to you, it is time to take a step back and understand the basics of SQL Server operation – especially transaction log and buffer pools
The queries and methods provided by this article should not be used in production environments. These queries and procedures are provided “as-is” and without warranty
Please do not use the queries and methods provided by this article without consultation and supervision by a DBA

Preparing the test

First of all, let’s create a sample database, and then change the recovery model to SIMPLE. This would allow the transaction log to be cleared when we issue a CHECKPOINT, and we would not have to run a transaction log backup. Note that after changing the recovery model, it is recommended to take a full database backup. I assume that the server where we are creating this database also contains the AdventureWorks2008R2 database for creation of sample data.

USE master

GO

CREATE DATABASE CleanBufferTest

ON PRIMARY

(NAME = 'CleanBufferTest_Data',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENALICTP03\MSSQL\DATA\CleanBufferTest_Data.mdf',

SIZE = 10,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10)

LOG ON (NAME = 'CleanBufferTest_Log',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENALICTP03\MSSQL\DATA\CleanBufferTest_Log.ldf',

SIZE = 10,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10)

GO

 

ALTER DATABASE CleanBufferTest SET RECOVERY SIMPLE;

GO

Using only DBCC DROPCLEANBUFFERS

Let’s create a test table and select data from it.

USE CleanBufferTest

GO

--Insert data from the AdventureWorks2008R2 database

SELECT * INTO CleanBufferEmployees FROM AdventureWorks2008R2.HumanResources.Employee

--Select data from the newly created table

SELECT * FROM CleanBufferEmployees

Now, using the below mentioned DMV based query, let’s see what data pages are available in the buffer.

USE CleanBufferTest

GO

--DMV to view the buffer descriptors

select so.name,*

from sys.dm_os_buffer_descriptors obd

INNER JOIN sys.allocation_units au ON obd.allocation_unit_id = au.allocation_unit_id

INNER JOIN sys.partitions part ON au.container_id = part.hobt_id

INNER JOIN sys.objects so ON part.object_id = so.object_id

WHERE obd.database_id = DB_ID()

AND so.is_ms_shipped = 0

Check the results of the query mentioned above.

Run DBCC DROPCLEANBUFFERS and then re-run the DMV based buffer look-up query:

1.DBCC DROPCLEANBUFFERS

The following was the output in my case. As you can see, our buffers are NOT clean!

Finally, clean up the test bench.

1.DROP TABLE CleanBufferTest

The ideal method to clean buffers

Let’s re-run the above test, but with this time, we have the CHECKPOINT in between to flush the transaction log to disk.

USE CleanBufferTest

GO

--Insert data from the AdventureWorks2008R2 database

SELECT * INTO CleanBufferEmployees FROM AdventureWorks2008R2.HumanResources.Employee

--Select data from the newly created table

SELECT * FROM CleanBufferEmployees

GO

 

--Checkpoint and then DROPCLEANBUFFER

CHECKPOINT

DBCC DROPCLEANBUFFERS

 

 

USE CleanBufferTest

GO

--DMV to view the buffer descriptors

select so.name,*

from sys.dm_os_buffer_descriptors obd

INNER JOIN sys.allocation_units au ON obd.allocation_unit_id = au.allocation_unit_id

INNER JOIN sys.partitions part ON au.container_id = part.hobt_id

INNER JOIN sys.objects so ON part.object_id = so.object_id

WHERE obd.database_id = DB_ID()

AND so.is_ms_shipped = 0

GO

As you can see, if DBCC DROPCLEANBUFFERS are used with a CHECKPOINT, Microsoft SQL Server forces all dirty pages for the current database to be written to disk and then cleans the buffers – resulting in a truly cold buffer cache. This recommendation has been made on the Books On Line page - http://technet.microsoft.com/en-us/library/ms187762.aspx

Edit (Added - October 03, 2011, 14:00 US-EDT)

As Chintak Chhapia (blog) points out in his comment below, the CHECKPOINT was required because we had a dirty page due to the SELET…INTO operation which ultimately is an INSERT operation.

In a collaborative environment, one would probably end up tuning stored procedures that perform UPDATEs after a couple of SELECTs or when running the application and generating test data in parallel. In such cases too, you would end up with dirty pages in the buffers. Let’s run a simple UPDATE on the CleanBufferEmployees table and then run DBCC DROPCLEANBUFFERS.

USE CleanBufferTest

GO

UPDATE CleanBufferEmployees SET VacationHours += 1

GO

 

--Attempt to clean buffers

DBCC DROPCLEANBUFFERS

GO

When we run our query to look at the buffers, we encounter a couple of rows in the result set, indicating that the buffers are unclean, and therefore requires a CHECKPOINT before DBCC DROPCLEANBUFFERS: