TSQL Tuesday #88 -Communication is the Key
March 14, 2017
I thank Kennie Nybo Pontoppidan (b|t) for hosting TSQL Tuesday #88. The topic for this month is the daily database-related WT…….
Last week I was setting up a collection of wait statistics with five minutes interval using a while loop. I was wrapping up Paul Randal’s (b|t) code from this blog post in a loop and persisting in a table to analyze later.
I made it a habit in my career to have my peers review my code. I sent my code to one of my colleagues and asked him to review it. Meantime I put the code in my test environment before promote to the production server for collection. During my test, I noticed a strange behavior. Interval mentioned in ‘WAITFOR DELAY’ was not consistent. I was getting anything between zero (in minutes) to five. I had no clue at that point what was going on. I sent my code to another person to look at, explaining my observation. Got green signal that the code looks good. Later on, I discovered both of us (person reviewing my code and myself) were testing the same code, in the same environment, at the same time. While the loop was starting at a different time with the same value for ‘WAITFOR DELAY’ and writing result set in the same table.
I was also getting this error because a temporary staging table was being dropped by another running session.
Msg 208, Level 16, State 1, Procedure rtime_CollectSystemInfo_WaitTypeSummary,
Line 57 [Batch Start Line 0]
Invalid object name 'tmp_rtime_SQLskillsStats2'.
Here is a snippet of my code and output.
--This is a modified code from
--http://www.sqlskills.com/blogs/paul/capturing-wait-statistics-period-time/
--This is a snippet of the code and nut the full version
IF EXISTS (SELECT 1
FROM sys.tables
WHERE NAME = 'tmp_rtime_SQLskillsStats1')
DROP TABLE [tmp_rtime_sqlskillsstats1];
IF EXISTS (SELECT 1
FROM sys.tables
WHERE NAME = 'tmp_rtime_SQLskillsStats2')
DROP TABLE [tmp_rtime_sqlskillsstats2];
--init schema
SELECT Getdate() AS [StartTime],
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms]
INTO tmp_rtime_sqlskillsstats1
FROM sys.dm_os_wait_stats
WHERE 1 = 0
SELECT Getdate() AS [EndTime],
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms]
INTO tmp_rtime_sqlskillsstats2
FROM sys.dm_os_wait_stats
WHERE 1 = 0
-- get first snapshot
INSERT INTO tmp_rtime_sqlskillsstats1
(starttime,
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms)
SELECT Getdate(),
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms]
FROM sys.dm_os_wait_stats
SET @Delay = '00:' + @WaitMin + ':00'
WAITFOR delay @Delay
--get second snapshot
INSERT INTO tmp_rtime_sqlskillsstats2
(endtime,
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms)
SELECT Getdate(),
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms]
FROM sys.dm_os_wait_stats
The lesson for myself was to communicate better. When I sent my code for review I should have mentioned the name of the SQL instance where I was testing my code.
