30

Based on getting Query Execution Statistics using this extremely useful piece of SQL obtained from this post Most Executed Stored Procedure - Stack Overflow:

SELECT TOP 100
   qt.TEXT AS 'SP Name',
   SUBSTRING(qt.text, qs.statement_start_offset/2, CASE WHEN (qs.statement_end_offset = -1) THEN LEN(qt.text) ELSE (qs.statement_end_offset - qs.statement_start_offset)/2 END) AS actual_query,
   qs.execution_count AS 'Execution Count',
   qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
   qs.total_worker_time AS 'TotalWorkerTime',
   qs.total_physical_reads AS 'PhysicalReads',
   qs.creation_time 'CreationTime',
   qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = (SELECT dbid
                FROM sys.sysdatabases
               WHERE name = 'BSP')
ORDER BY qs.total_worker_time/qs.execution_count DESC

How would I completely clear out these execution statistics and start from scratch?

This would be particularly useful as development bugs and testing have caused routines to be called an usually large number of times thus invaliding the true usage levels.

Community
  • 1
  • 1
Simon Mark Smith
  • 1,130
  • 3
  • 13
  • 19

1 Answers1

62
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
Andrew
  • 26,629
  • 5
  • 63
  • 86
  • That worked in the test environment - are there any risks to doing this in Production? – Simon Mark Smith Feb 11 '10 at 10:04
  • 1
    Yes, although it's listed as development bugs and testing, so unless they were testing / dev against prod it should be fine. On production it would spike the CPU badly but recover. – Andrew Feb 11 '10 at 10:04
  • 1
    On `SQL Server 2012` I have just used `DBCC FREEPROCCACHE`. Why we should clear the `buffer` also? – gotqn Nov 19 '14 at 06:48
  • 2
    If you don't drop the buffers, then subsequent runs of the query will use the data that was cached into memory - skewing the results since there is every chance the entire procedure is now using logical IO, not physical. http://msdn.microsoft.com/en-us/library/ms187762.aspx – Andrew Nov 19 '14 at 10:02
  • 1
    re "be careful in production" - because this clears the execution plan cache, the server has to create and compile them from scratch. On our (relatively big) app it takes half of a day to get all plans back into the cache. – Alex from Jitbit Jun 18 '19 at 17:05