Proactive Monitoring of SQLServers using WaitStats
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).
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 –
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.
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.
Related Posts
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.