SQL Server Wait Stats Monitoring with PowerShell

By: Alejandro Cobar | Updated: 2020-08-31 | Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | > Monitoring

Problem

When working with performance issues in your SQL Server instances, WaitStats are one of the key areas you should review to get an idea of where SQL Server is spending most time waiting on processes to finish. This is key to understanding if there is some overall issue that could be addressed to improve performance.

Solution

This module will gather information about SQL Server WaitStats from all monitored instances. It will connect with each server in the inventory.MasterServerList table and capture the data for each instance. The connection to each SQL Server instance is based on the settings in the inventory.MasterServerList table.

As proactive DBAs, at some point, we would like to know which are the top X wait stats that each of our instances is waiting on the most. Therefore, this PowerShell script will collect the top 5 wait stats from all of the SQL Server instances that you have registered. This can be adjusted in the PowerShell script as needed.

Prerequisites

In order to use this module, you need to create the core objects found here.

Database Objects

For this particular module, only one table will be created (in case it doesn’t exist), and it is the table to store the information of the wait stats, from each SQL Server instance.

This is the structure of the table, so you can get an idea of what data will be stored.

inventory.WaitStats
- serverId - serverid ties back to inventory.MasterServerList
- wait_type - type of wait
- wait_time_seconds - wait time in seconds
- data_collection_timestamp - when data was last collected

*If you want to add more fields to this table, make sure to adjust the structure within the PowerShell script and adapt the respective logic that will handle that additional field.

Important Note: Within the PowerShell code you will find the query that gathers the wait stats information for each instance. However, this particular query will contain a somewhat large set of wait stats that won’t be taken into account. The reason is that there are wait stats that are tracked by SQL Server, but these wait stats are generally not a performance problem. One good example of this is the wait stat called "SLEEP_TASK". If you don’t filter this out, then it might be fetched by the script and you will end up with a lot of noise in your final result set, which is not very helpful. With that said, also keep in mind that the PowerShell script, by default, will fetch the top 5 wait stats per instance. If you want more or less than that, then you have to adjust the TOP 5 statement in the query.

PowerShell Script

The PowerShell script that creates the above object and inserts data into the inventory.WaitStats table is called:

Get-MSSQL-Instance-WaitStats.ps1.

The script has some validations that will help you check if some key elements are missing for the script to run successfully. For instance, it will confirm that the inventory.MasterServerList table exists and that it has at least 1 active instance registered to be able to have something to work with.

How to Use

Navigate to the folder where you created the files and you can run the PowerShell script as follows:

Option 1

Right click on Get-MSSQL-Instance-Indexes.ps1 and select Run with PowerShell

Option 2

Open a command window and navigate to the folder where you saved the above files and run

powershell "C:\temp\Get-MSSQL-Instance-WaitStats.ps1"

Option 3

Schedule this as a SQL Server Agent Job to run on a regular basis.

Option 4

Schedule this as a Windows Task Scheduler job to run on a regular basis.

Check Creation of Database and Objects

After running the PowerShell script, we can see the objects that are created.

If we query the inventory.WaitStats table, we can see the data that has been collected.

Note: The PowerShell script will store only the information from the last execution, no historical data is retained. If you’d like to keep information from previous executions, you would have to modify the script and adapt it to your particular use case.

Checking for Errors

To check for errors query the monitoring.ErrorLog table using the following query:

SELECT *
FROM monitoring.ErrorLog
WHERE script = 'Get-MSSQL-Instance-WaitStats'

If you’d like to know the SQL Server instance that got the errors, you would have to issue the query like this:

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name ELSE 
   CONCAT(msl.server_name,'\',msl.instance) 
   END AS instance,
   e.script,
   e.message,
   e.error_timestamp
FROM monitoring.ErrorLog e
JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId
WHERE e.script = 'Get-MSSQL-Instance-WaitStats'

Useful Queries

Which is the longest wait stat for each one of my instances?

WITH MaxWaitStats AS(
SELECT
    serverId,
    MAX(wait_time_seconds) AS max_wait_time
FROM [DBA].[inventory].[WaitStats]
GROUP BY serverId)
 
SELECT 
     mws.serverId,
     mws.max_wait_time,
     (SELECT wait_type FROM inventory.WaitStats WHERE serverId = mws.serverId AND mws.max_wait_time = wait_time_seconds) AS wait_type
FROM  MaxWaitStats mws

I would like to see all the wait stats ordered from "worst" to "best".

SELECT *ROM inventory.WaitStats
ORDER by wait_time_seconds DESC

Here’s a useful resource, from Tim Ford, that you can read if you’d like to dive a bit deeper into wait stats. In this article, he mentions the exclusion of some wait stats that don’t add any value to the information you are trying to collect from your instances:

SQL Server sys.dm_os_wait_stats DMV Queries

In addition, you can search the internet for other variations of what Tim provides to further reduce the noise of wait stats that can be ignored.

Download Scripts

Download the scripts for this module

Next Steps

Download the PowerShell script to collect data for SQL Server Wait Statistics.

Check out the other parts of this series:

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2020-08-31