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 | More > Monitoring
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.
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.
In order to use this module, you need to create the core objects found here.
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.
- 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.
The PowerShell script that creates the above object and inserts data into the inventory.WaitStats table is called:
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:
- Right click on Get-MSSQL-Instance-Indexes.ps1 and select Run with PowerShell
- Open a command window and navigate to the folder where you saved the above files and run
- Schedule this as a SQL Server Agent Job to run on a regular basis.
- 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'
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:
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 the PowerShell script to collect data for SQL Server Wait Statistics.
Check out the other parts of this series:
- Monitoring SQL Server with PowerShell Core Object Setup
- Monitoring SQL Server with PowerShell Instance Data Collection
- Monitoring SQL Server with PowerShell Instance Jobs Collection
- Monitoring SQL Server with PowerShell Instance Jobs Last Execution
- Monitoring SQL Server with PowerShell Instance Backups
- Monitoring SQL Server with PowerShell Instance Database and Database Files
- Monitoring SQL Server with PowerShell for use of SysAdmin role
About the author
View all my tips
Article Last Updated: 2020-08-31