SQL Server Monitoring: Why It Matters and What You Should Track

Why should you monitor your SQL Servers?

You can’t manage what you don’t know about. You can’t improve what you’re not measuring. If you have SQL Servers installed somewhere that people have forgotten about, and you probably do if you haven’t used an automated tool to scan for them, then you can’t manage them. You certainly can’t monitor those SQL Servers. This means that you also don’t know how well, or badly, they are performing or what bad practices and habits are lurking, just waiting to cause problems. 

All of this can lead to: 

• Lost time because your team is fighting performance problems that can’t be identified due to the right data being unavailable. 
• Lost revenue from outages that could have been prevented or shortened with the right data. 
• Poor practices and bad habits stay hidden because there isn’t anything to surface the issues. 

What should you monitor on your SQL Servers?

We could debate this topic ad nauseam, so instead, I’ll provide a list of foundational things to consider.

Common server and database settings:

Whatever is used to monitor and manage SQL Servers in your environment would do well to have a section where you can see how instance and database settings are configured. Review this information about your environment and compare what you find to known best practices. 

Host and instance memory usage:

This includes not just a raw number about percentage of RAM used or free, like you can get from the Available Bytes counter, but also things like other Performance Monitor counters such as Free List Stalls/second, Lazy Writes/second, Page Life Expectancy per NUMA Node, and Memory Grants Pending. Taken together, these metrics can help identify memory pressure. 

Host and instance CPU usage:

This one is fairly straightforward but still worth mentioning.

IOPs (Input/Output/second):

You need some measurement of the number of reads and writes occurring on your storage and whether those IOPs are experiencing any latency. Ideally, the metrics will also surface when the latency is happening. If latency is detected, inevitably, the next questions are, “When is it happening?” or “What queries are causing it?” 

Queries and associated waits:

You need to be able to see what queries were running during a certain time and what wait types those queries experienced. You need this same information for what is running at this moment so you can troubleshoot live issues. Without this, it’s really difficult to know what the problem is, or even if the problem is on the SQL Server side versus somewhere else. 

Blocking/Deadlock metrics:

Monitoring should include the number of blocking sessions and deadlocks. This is critical information to have because simply looking at the duration of queries may not tell you the whole story. A query, or set of queries, may be running long because they are experiencing blocking and not because the queries themselves are poorly constructed. Deadlock metrics will help point in the direction of needed application changes, index changes, or both.  

Want to work with The SERO Group?

Want to learn more about how The SERO Group helps organizations take the guesswork out of managing their SQL Servers? Schedule a no-obligation discovery call with us to get started.