Executing DBCC FREEPROCCACHE with parameters

Leave a comment (1) Go to comments

DBCC FREEPROCCACHE, is used to clear all plan cache, which forces that every stored procedure will be recompiled  on it’s first execution to build a execution plan. This was known fact, till SQL Server 2005, DBCC FREEPROCCACHE has a server wide scope, means we can not clear a specific plan, this limitation was removed in SQL Server 2008.

As per Microsoft BOL, In SQL Server 2005, DBCC FREEPROCCACHE, removes all elements from the procedure cache but in SQL Server 2008, DBCC FREEPROCCACHE removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

Microsoft has extended in SQL Server 2008 for DBCC FREEPROCCACHE.  In SQL Server 2008, we can supply a parameter to DBCC FREEPROCCACHE, now we can use the following syntax for DBCC FREEPROCCACHE

 

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]

where :

plan_handle uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache.

sql_handle is the SQL handle of the batch to be cleared.

pool_name is the name of a Resource Governor resource pool.


Code Examples : Let's take a closer look, How we can use DBCC FREEPROCCACHE with parameters and how to find out parameter values

Problem Statement 1 : How to clear a Plan handle for a particular SQL statement ? How to get plan_handle for a particular query and how to clear that ? How us supply plan handle with DBCC FREEPROCCACHE ?

Solution : Following Code, describes, how to get a sql_handle and plan_handle for a particular batch select query and how  we can use that to clear a particular sql query from a batch ?

/* A Normal select statement…, for which we wanted to clear a plan guide. */

USE AdventureWorks2008R2;

GO

SELECT p.LastName, p.FirstName, HumanResources.Employee.JobTitle FROM HumanResources.Employee JOIN Person.Person AS p ON  HumanResources.Employee.BusinessEntityID = p.BusinessEntityID WHERE HumanResources.Employee.OrganizationLevel = 3 ORDER BY p.LastName, p.FirstName OPTION (TABLE HINT(HumanResources.Employee, FORCESEEK))

GO

/* Selecting a plan handle for  a particular SQL query in variable named @plan_id */

declare @plan_id  varbinary(64)

SELECT @plan_id = plan_handle FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE text LIKE N'SELECT p.LastName, p.FirstName, HumanResources.Employee.JobTitle FROM HumanResources.Employee JOIN Person.Person AS p ON HumanResources.Employee.BusinessEntityID = p.BusinessEntityID WHERE HumanResources.Employee.OrganizationLevel = 3 ORDER BY p.LastName, p.FirstName%';

/* Clearing a plan guide for a particular plan_id, which we identified in a last select statement. */

DBCC FREEPROCCACHE (@plan_id  );

go

Code Example 2:

Problem Statement 2 : How to clear a sql_handle? How to get clear a cache using a sql handle?

Answer : A sql_handle contains a hash of all the text in entire group of SQL statements or "Batch". This indicates that executing two identical add-hoc queries will result in one sql_handle.

/* A Normal batch select statement… for which we wanted to clear Cache.

*/

USE AdventureWorks2008R2;

GO

SELECT COUNT(*) FROM   HumanResources.Employee WHERE  BusinessEntityID <> 1

GO

SELECT COUNT(*) FROM   HumanResources.Employee WHERE  BusinessEntityID <> 2

GO

/* Selecting a plan handle for  a particular SQL query in variable named @plan_id

*/

SELECT st.TEXT,qs.sql_handle, qs.plan_handle FROM   sys.dm_exec_query_stats qs  CROSS APPLY sys.Dm_exec_sql_text(sql_handle) st where st.TEXT like 'SELECT COUNT(*) FROM   HumanResources.Employee WHERE  BusinessEntityID <>%';

 

/* Clearing a plan guide for a particular plan_id, which we identified in a last select statement.

*/

DBCC FREEPROCCACHE (0x020000001D4F80393C7B1A505BBB03318C10425F009A8576);

go

Problem Statement 3: How to find a resource pool name? How to get clear a procedure cache for a particular resource pool ?

Answer : When we implement a Resource Governor, we create a workload pool, resource pool and a classifier function. So now in SQL Server 2008, we have an option to clear a all procedure which is allocated to a particular resource pool. Here is an example, for clearing the resource pool named cpu_intensive_processes

/* Select Query to find out all Resource pools availbale */

SELECT * FROM sys.dm_resource_governor_resource_pools;

GO

 

/* Clearing a all procedure cache for a particlumar Resource pool */

DBCC FREEPROCCACHE ('cpu_intensive_processes');

go

Problem Statement 4: How to clear all procedure cache for a sql server instance ?

Answer : Following code describes, how we can clear all procedure cache in SQL Server

/* Clearing all procedure cache, Server wide */

DBCC FREEPROCCACHE;

go

EOF - Executing DBCC FREEPROCCACHE with parameters, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.

Trackbacks and Pingbacks: