“It’s Slow” Is Not A Metric

When I start a SQL Server Critical Care with a client, the first thing I ask is, “What is your #1 pain point? Where does it hurt?” The answer I hear most often is, “The server is too slow”.

I’m a runner. If I was able to run a 5K race at an 8:30 per mile pace, I would think I was the fastest runner on earth. The world record for a 5K by a woman is at a 4:34 per mile pace. She would think an 8:30 pace was…slow.

Slow is relative.

Slow is not a metric.

snails-shutterstock_109614335

It’s a race!

In order to effectively measure our systems, we need metrics.This is why baselines for our systems are so essential.

Getting Started with Baselines

The first thing to ask is, “What information do I need to track?” There are a few data points to monitor on any SQL Server: CPU usage, memory usage, number of reads and writes, time spent on reads and writes, wait statistics, and more.

If you have baselines, when a user says, “The system is slow”, you can check your current metrics against your baselines. Has CPU spiked significantly? You can then use a tool like sp_WhoIsActive or sp_BlitzFirst® to find queries using a large amount of CPU. Maybe you can’t find anything in SQL Server – use Resource Monitor to see if there’s an external process in Windows chewing up CPU.

Are reads or writes taking longer than usual? Again, use a tool to see what queries are currently running and how much work they are doing. If you have shared storage, such as a SAN, have a method in place to determine if it’s the SQL Server using the storage, or another system connected to the SAN.

Become familiar with wait statistics. Run samples so you know what your average waits are. If you get a call saying, “It’s slow”, look at a 60-second sample of current waits. If the top three are normally CXPACKET, ASYNC_NETWORK_IO, and PAGEIOLATCH_SH, and now LCK_M_IX is second on the list, dig into your server to see what is holding the locks.

Tools for Baselines

You have many options for collecting and reporting on baselines. You can write your own, or you can invest in a third party tool.

You can create your own tool using a variety of tools:

Data can be saved to a repository database you create, and you can write custom reports to track anything you wish in Reporting Services (or another report tool). Of course, this involves time, effort, and testing. You want to make sure you are tracking the right metrics, storing them correctly, and reporting on them accurately. The level of customization you get is wonderful, but you need to ensure you create an accurate solution.

metrics quoteThere are many third party tools available for you to monitor SQL Server. The best way to approach selecting one is to write down a list of what you want to track, and make sure the software you pick has those features. Remember things like custom metrics, reports, and alerts! Most tools will offer a free trial; take advantage of that to make sure you’re making the right decision.

Have metrics, not feelings

“Slow” is relative. “Slow” is not a metric. You never know when someone is having a bad day and everything feels slow to him, or he needs to rush out the door early because his kid is sick, or she is getting ready for vacation and time has slowed down in her world. Measure and track. Numbers don’t lie.

Brent says: You get what you measure. The instant you start putting a graph on your cubicle wall, people start paying attention.

Kendra says: User complaints per day is also a metric! Find out what parts of the application people are complaining about and what their experience is specifically. As you work through issues, “Numbers of complaints solved” is a really useful metric for your next performance review.

Previous Post
Measuring Performance Can Slow SQL Server Down
Next Post
Using “OR” and “IN” with SQL Server’s Filtered Indexes

7 Comments. Leave new

  • do you know a smtp server that allows me to send alerts to my “dba team” for notify using 80 tcp port?

    Reply
  • The other thing about good metrics is that they come with units – good, standard consistent units of measurement with are easily worked with and convertible from one scale to another. This is essential for “reporting on them accurately”. A bad habit is to mix units from different systems. Just ask the Mars Climate Orbiter team. An example of this would be to use a time per *mile* and a distance in *kilometers*.

    Reply
  • Its funny how someone that does metrics, kpis and reporting for customers day in and out, I never even considered having performance metrics for our core DB. Certainly I perceive things as slow all the time. My only complaint about by his article is it makes it grossly apparent that I could be doing more.

    Reply
  • I have been convinced that I need to start collecting baselines for the instances that I manage, I have a decent list of things that I should collect but I’m finding it a bit overwhelming to try to build my own collection system. I could probably use SSIS to both query perfmon counters as well as wait stats, query stats etc, but I’m also wondering what the correct interval would be for each metric. I don’t want to monitor the server to death.

    What are the teams thoughts on something like Foglight on SQL Server?

    Thanks!

    Reply
    • Jes Schultz Borland
      February 17, 2015 3:10 pm

      Jeremie, it can be a big task to re-invent the wheel! If you’d like to spend your time focusing on interpreting the stats rather than collecting them, a third party tool can be helpful – and there are great choices out there. Foglight is dependable, as are SQL Sentry Performance Advisor and Red Gate SQL Monitor. Check them out!

      Reply
  • Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}