Proactive Monitoring of SQLServers using WaitStats

Spread the love

The open-source monitoring tool SQLMonitor has proved to be very useful & effective for me and my team on a number of instances. Till now, we are monitoring 115+ SQL Servers using the tool in my organization.

Just a few days back, as usual, I began my day by looking at the Monitoring - Live - All Servers dashboard. This dashboard presents any SQL server instance that is having any kind of problem (which could be related to cpu, memory, io, tempdb, disk or log usage).

SQL Server Core Health Metrics using SQLMonitor

Above is the screenshot of the Core Health Metrics I got from the dashboard. Here I could directly notice a server that had no obvious issues like CPU, blocking, memory grants pending, but still Waits Per Core Per Minute metric value was very high. This got me curious, and I clicked on the underlying link to explore more on the Waits Stats dashboard.

Below are the metrics I noticed for the last couple of hours –

Resolve SQL Server Issues using Wait Stats on SQLMonitor tool

We can clearly notice a huge difference in Avg Time Per Wait in the last hour on the server compared to Waits since the server startup. This was an indicator that the queries on the server were facing lot of IO latency. To validate the information further, I browsed to Database File IO Stats dashboard.

Resolve SQL Server Issues using File IO Stats on SQLMonitor tool

Here in the above file io stats dashboard, I could clearly see that both read & write latency for all drives & database files were screwed. This was especially true for Write latency.

Once me & my team were aware of the issue, we found and resolved the root cause of the latency. On the blog Troubleshooting SQL Server Storage IO, I already share the possible solutions to resolve high IO latencies.

Live Demo for SQLMonitor -> https://ajaydwivedi.com/demo/sqlmonitor

Github URL for repository -> https://ajaydwivedi.com/github/sqlmonitor

If you folks are looking for a reliable & effective, yet free-of-cost monitoring tool for SQL Server, then SQLMonitor could be the right solution for you.

I hope this will be helpful to SQL DBAs who are having a hard time figuring out the issues with their SQLServers.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.