Menu
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.
391
Using SQL Server Management Studio Click the plus sign to expand the Statistics folder. Right-click the statistics object that you want to delete and select Delete. In the Delete Object dialog box, ensure that the correct statistic has been selected and click OK.
To clear SQL Server's cache, run DBCC DROPCLEANBUFFERS , which clears all data from the cache. Then run DBCC FREEPROCCACHE , which clears the stored procedure cache.
We use the SQL DROP Table command to drop a table from the database. It completely removes the table structure and associated indexes, statistics, permissions, triggers and constraints.
By cleaning the buffer pool before each test run SQL Server will have to re-read the data it needs from disk. To clean the buffer pool you execute the command: DBCC DROPCLEANBUFFERS. Next you should remove your execution plans from the procedure cache.
DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
