How to clean up the cache and check what and all SQL statements in the cache.

Question

How to clean up the cache and check what and all SQL statements in the cache.

prashan

SSC Journeyman

Points: 78
More actions
November 26, 2008 at 3:53 am

#94346

To freeup the cache information
DBCC FREEPROCCACHE
SELECT [cp].[refcounts]
, [cp].[usecounts]
, [cp].[objtype]
, [st].[dbid]
, [st].[objectid]
, [st].[text]
, [qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

krayknot SSCertifiable Points: 7998 More actions · Answer 1

prashant_j42000 (11/26/2008)
To freeup the cache information
DBCC FREEPROCCACHE
SELECT [cp].[refcounts]
, [cp].[usecounts]
, [cp].[objtype]
, [st].[dbid]
, [st].[objectid]
, [st].[text]
, [qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

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.

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Those commands will also drop all of the data pages from cache, forcing SQL to go to disk next time it needs data.

Do not run those on a busy production system unless you want to seriously degrade performance for a while.

prashant: Was there a question? What are you trying to do?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass