Tuesday, April 22, 2014

Figuring out when Wait Statistics were last cleared

SELECT
[wait_type],
[wait_time_ms],
-- ************************************************************************
-- If there's a conversion to int error than the wait_time_ms is too long.
-- Convert to seconds instead.
-- DATEADD(ss,-[wait_time_ms]/1000,getdate()) AS [Date/TimeCleared],
-- ************************************************************************
DATEADD(ms,-[wait_time_ms],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_INCREMENTAL_FLUSH_SLEEP';

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


-- ***********************************************************************
-- To clear statistics
-- DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
-- ***********************************************************************

source: http://www.sqlskills.com/blogs/erin/figuring-out-when-wait-stats-were-last-cleared/

No comments:

Post a Comment