SQL SERVER – Capturing Wait Types and Wait Stats Information at Interval – Wait Type – Day 5 of 28

Earlier, I have tried to cover some important points about wait stats in detail. Here are some points that we had covered earlier.

  • DMV related to wait stats reset when we reset SQL Server services
  • DMV related to wait stats reset when we manually reset the wait types

However, at times, there is a need of making this data persistent so that we can take a look at them later on. Sometimes, performance tuning experts do some modifications to the server and try to measure the wait stats at that point of time and after some duration. I use the following method to measure the wait stats over the time.

-- Create Table
CREATE TABLE [MyWaitStatTable](
[wait_type] [nvarchar](60) NOT NULL,
[waiting_tasks_count] [bigint] NOT NULL,
[wait_time_ms] [bigint] NOT NULL,
[max_wait_time_ms] [bigint] NOT NULL,
[signal_wait_time_ms] [bigint] NOT NULL,
[CurrentDateTime] DATETIME NOT NULL,
[Flag] INT
)
GO
-- Populate Table at Time 1
INSERT INTO MyWaitStatTable
([wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms],
[CurrentDateTime],[Flag])
SELECT [wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms],
GETDATE(), 1
FROM sys.dm_os_wait_stats
GO
----- Desired Delay (for one hour) WAITFOR DELAY '01:00:00'
-- Populate Table at Time 2
INSERT INTO MyWaitStatTable
([wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms],
[CurrentDateTime],[Flag])
SELECT [wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms],
GETDATE(), 2
FROM sys.dm_os_wait_stats
GO
-- Check the difference between Time 1 and Time 2
SELECT T1.wait_type, T1.wait_time_ms Original_WaitTime,
T2.wait_time_ms LaterWaitTime,
(
T2.wait_time_ms - T1.wait_time_ms) DiffenceWaitTime
FROM MyWaitStatTable T1
INNER JOIN MyWaitStatTable T2 ON T1.wait_type = T2.wait_type
WHERE T2.wait_time_ms > T1.wait_time_ms
AND T1.Flag = 1 AND T2.Flag = 2
ORDER BY DiffenceWaitTime DESC
GO
-- Clean up
DROP TABLE MyWaitStatTable
GO

If you notice the script, I have used an additional column called flag. I use it to find out when I have captured the wait stats and then use it in my SELECT query to SELECT wait stats related to that time group. Many times, I select more than 5 or 6 different set of wait stats and I find this method very convenient to find the difference between wait stats.

In a future blog post, we will talk about specific wait stats.

Read all the post in the Wait Types and Queue series.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

3 thoughts on “SQL SERVER – Capturing Wait Types and Wait Stats Information at Interval – Wait Type – Day 5 of 28

  1. Pingback: SQL SERVER – A Quick Look at Performance – A Quick Look at Configuration « SQL Server Journey with SQLAuthority

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #015 « SQL Server Journey with SQL Authority

  3. Pingback: SQL SERVER – 28 Links for Learning SQL Wait Stats from Beginning | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s