How to Clear down Query Execution Statistics in SQL Server 2005/2008

sql sql-server performance statistics sql-execution-plan
Simon Mark Smith picture Simon Mark Smith · Feb 11, 2010 · Viewed 38.3k times · Source

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.

Answer

Andrew picture Andrew · Feb 11, 2010 
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Related questions

SQL why is SELECT COUNT(*) , MIN(col), MAX(col) faster then SELECT MIN(col), MAX(col)

We're seeing a huge difference between these queries. The slow query SELECT MIN(col) AS Firstdate, MAX(col) AS Lastdate FROM table WHERE status = 'OK' AND fk = 4193 Table 'table'. Scan count 2, logical reads 2458969, physical reads 0, read-ahead reads 0, lob logical reads 0, …

Read More
When should I use cross apply over inner join?

What is the main purpose of using CROSS APPLY? I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind) I …

Read More
How to check which locks are held on a table

How can we check which database locks are applied on which rows against a query batch? Any tool that highlights table row level locking in real time? DB: SQL Server 2005

Read More