SQL Server 2005 – When Were sys.dm_os_wait_stats Last Cleared?

sql serversql-server-2005

I intend to monitor the execution of my stored procedures in a specific database, using the following script, which I got from
How can I monitor the call count per stored procedure per timespan?

SELECT
    OBJECT_NAME(qt.objectid)
  , qs.execution_count AS [Execution Count]
  , qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second]
  , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
  , qs.total_worker_time AS [TotalWorkerTime]
  , qs.total_elapsed_time / qs.execution_count AS [AvgElapsedTime]
  , qs.max_logical_reads
  , qs.max_logical_writes
  , qs.total_physical_reads
  , DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
    qt.[dbid] = DB_ID()
ORDER BY
    qs.execution_count DESC
OPTION (RECOMPILE);

The DMV sys.dm_exec_query_stats reports cumulative values since the last restart. If you want to measure over a fixed period, use the command below to reset wait stats.

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

DBCC SQLPREF("sys.dm_os_latch_stats",CLEAR); --this one never worked for me

When was sys.dm_os_wait_stats last cleared? But in Sql Server 2005, how do I find it out?

Best Answer

there is a wait type called SQLTRACE_BUFFER_FLUSH -> Occurs when a task is waiting for a background task to flush trace buffers to disk every four seconds.

this article is very good: Figuring Out When Wait Statistics Were Last Cleared - by Erin Stellato

I came out with the following script that seems to be working fine on my servers, including the sql2005 ones.

/* last time the wait cache was cleared - sql 2005*/
SELECT
[wait_type],
[wait_time_ms],
DATEADD(ss,-[wait_time_ms]/1000,getdate()) AS [Date/TimeCleared],
CASE
WHEN [wait_time_ms] < 1000 THEN CAST([wait_time_ms] AS VARCHAR(15)) + ' ms'
WHEN [wait_time_ms] between 1000 and 60000 THEN CAST(([wait_time_ms]/1000) AS VARCHAR(15)) + ' seconds'
WHEN [wait_time_ms] between 60001 and 3600000 THEN CAST(([wait_time_ms]/60000) AS VARCHAR(15)) + ' minutes'
WHEN [wait_time_ms] between 3600001 and 86400000 THEN CAST(([wait_time_ms]/3600000) AS VARCHAR(15)) + ' hours'
WHEN [wait_time_ms] > 86400000 THEN CAST(([wait_time_ms]/86400000) AS VARCHAR(15)) + ' days'
END [TimeSinceCleared]
FROM [sys].[dm_os_wait_stats]
WHERE [wait_type] = 'SQLTRACE_BUFFER_FLUSH'
  OR  [WAIT_TYPE] = 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP';


/* check SQL Server start time - 2008 and higher */
SELECT
[sqlserver_start_time]
FROM [sys].[dm_os_sys_info];


-- to clear the wait cache
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

this time I will leave it like this, because I in a bit of a rush to monitor stored procedures executions, but for the next time I will use other Several Methods to collect SQL Server Stored Procedure Execution History

and maybe also a bit of this one: Converting SQL Trace to Extended Events in SQL Server 2012

hopefully soon we will migrate all of the sql server 2005

Related Question