Wait stats are an important aspect of SQL Server performance tuning. They provide valuable insights into the bottlenecks and resource contention issues that may be affecting the overall performance of your SQL Server instance. In a previous blog post, we discussed the basics of wait stats and how to reset them. In this article, we will explore a method to measure and analyze wait stats over a period of time.

At times, it becomes necessary to capture and analyze wait stats over a specific duration. This is particularly useful when performance tuning experts make modifications to the server and want to measure the impact on wait stats before and after the changes. To achieve this, we can create a table to store the wait stats data and populate it at different points in time.

Let’s take a look at an example script that demonstrates this approach:

-- 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

In the above script, we create a table called “MyWaitStatTable” with columns to store wait type, waiting tasks count, wait time in milliseconds, maximum wait time in milliseconds, signal wait time in milliseconds, current date and time, and a flag to identify the time group. We then populate this table at two different points in time using the sys.dm_os_wait_stats dynamic management view.

After populating the table, we can compare the wait time between the two time points to determine the difference in wait stats. This can be useful in identifying any changes in wait times and understanding the impact of server modifications on performance.

By using the flag column, we can easily select and analyze specific sets of wait stats. This method provides a convenient way to compare and measure wait stats over time.

In a future blog post, we will delve into specific wait stats and discuss their significance in SQL Server performance tuning. Stay tuned for more insights in our Wait Types and Queue series.