when were the sys-dm_os_wait_stats last cleared? sql-2005!

Question

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?

Answer 1

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

Answer 2

Another area to look into is the Default trace which is ON by default. The default trace logs DBCC Events --> Audit DBCC event. So the event gets fired when a DBCC command is ran.

Remember that default trace will have 5 max files with 20MB size limit. So if your server is busy, you might miss the event.

Alternatively, you can have a WMI alert triggering the load of the trace file into a SQL Server table when a rollover happens.

Answer 3

Here is a simple version that does not cause the error "Arithmetic overflow error converting expression to data type int." if the MSSQLSERVER service has been running long enough to cause the value to be BIGINT.

SELECT wait_time_ms AS Miliseconds,
     wait_time_ms/1000 AS Seconds, 
     wait_time_ms/1000/60 Minutes, 
     wait_time_ms/1000/60/60 AS Hours,
     wait_time_ms/1000/60/60/24 AS Days
FROM [sys].[dm_os_wait_stats] AS dm_os_wait_stats
WHERE [wait_type] = 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP';

This error occurs (I think) if the service has been running for more than about 24 days. INT data type is up to 2147483647, beyond which it is BIGINT. Please correct me if I'm wrong in this caluclation. Marcello's query causes that overflow error.

SELECT 2147483647 / 1000 / 60 / 60 / 24