What Your SQL Server Monitoring Software Should Tell You

Featured article by Leo Ambrose Goodwin, Independent Technology Author

Most users purchase monitoring tool, and in due course, they are not capable of realizing the leading cause of a SQL server performance. In most of these cases you find that the users bought a tool without knowing if it does what they what it to do. Ignorance in asking what you need is the biggest problem with people hence purchasing a tool that will give them issues before realizing what went wrong.

I think of monitoring tools regarding maturity tiers:

Level 1: Inexpensive Wall of Numbers –This one is not specifically SQL Server at all but just a cluster of WMI counters and Perfmon. It is a tool that has no guidance at all on the normal range for that specific number, displaying on a large dashboard of numbers. It exhibits peaks and valleys due to its ability to auto-scale graph hence making it harder to establish problems. This tier has no problem at all only if you are an advanced sysadmin.

Level 2: Basic DMV Wall of Numbers – A bunch of DMV queries are added for kinds of stuff like wait stats, nonetheless queries from the plan cache are not the ones causing any given wait type.

Level 3: Advanced DMV Wall of Numbers –Here you get execution plan, index utilization and availability group’s diagnostics.

Level 4: Mentoring and Root Cause Analysis – These are tools that give you information on the state of SQL Server by using huge lots of data. The tools also tell the main cause of the state of the SQL Server.

So what level is your monitoring tool?

It’s easy to know the level of your monitoring tool by triggering a few common SQL Server emergencies on the dev box and see how your monitoring tool surfaces behave.

Have databases not been backed up recently? The dashboard only has to be opened under its default configurations in time of emergency.

Are databases corrupt? If there’s rows inys.dm_hadr_auto_page_repair, or, msdb.dbo.suspect_pages we have a serious problem.

Who’s the lead blocker in a blocking chain? The dashboard should be opened immediately when queries are stacked and waiting on locks. Atypically huge lock wats are shown by the low-end monitoring tools, but when drilled down they only show the blocked queries subjected to the LCK waits leading to DBAs .-Wrong troubleshooting path.

Every SQL Server monitoring vendor is always open to help. You can consult them, and they will walk you through your monitoring tool and explain fully what they can see in the tool. They will teach you how to maximally use the tool, after all, if you can use the tool well, you going to get another one from a different vendor. If one doesn’t have enough capital to purchase a better monitoring tool, other SQL Server monitoring vendors accept trade-ins. They can swap existing licenses for another monitoring tool in return of paying them maintenance fee.