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

• krayknot

  SSCertifiable

  Points: 7998

  More actions

  November 26, 2008 at 11:02 pm

  #904145

  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

  November 27, 2008 at 12:58 am

  #904170

  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