Your SQL Server is Slow, What To Look At

As a data professional, you’ll often get a phone call, email, or trouble ticket that says “SQL Server is slow, my query is slow, or things are slow”. This is the dreadful comment that we all receive from time to time and it never provides any detail of ‘what’ is slow.

Where do you start looking? What are some of the common things that you should be familiar with in looking for performance issues when someone says things are slow?

CPU

I always start by looking at the basic health of the server, and one of the first things I look for is CPU utilization. Is the server under CPU strain, is CPU at or near 100% and sustained that high? If so, is it a single query crushing the server or lots of queries? Is it even SQL Server consuming the CPU or is some other process? Often times I find a virus scan running, a PowerShell process, or some other non-SQL application that is competing for CPU.

If it is a SQL workload consuming all the CPU, then its time to dig into the high CPU queries and see if they can be tuned with index tuning or some code refactoring.

Memory – RAM

What does memory look like? Is there enough for the instance. Has max server memory been set to allow adequate memory for the OS. If the OS is being starved, it can lead to some nasty behavior. I tend to like to leave 10% available. I still use page life expectancy as a metric for if things are OK from a memory perspective. Even with solid state disk, which are fast, disk is still slower than RAM. If I see that data is constantly being flushed from the buffer pool and we only have low minutes of PLE, I know we have some memory pressure. I then start looking for tuning opportunities to avoid index and table scans.

Storage

How is my storage performing? What read/write latency is occurring on the instance? Slow disk can manifest as all kinds of issues within SQL Server. Excessive blocking, high LCK waits, and much more. I use a modified version of Paul Randal’s file stats script on his blog post “Capturing IO latencies for a period of time”. The DMV sys.dm_io_virtual_file_stats tracks file statistics and will tell you lots of information, however it is cumulative, so capturing the DMV, waiting for a period of time, then capturing it again will allow you to see a current time slice to know what is going on right now. That is crucial to be able to capture and look at unless you already have a monitoring tool that can show you that baseline.

I always like to rule out infrastructure issues as well as looking for low hanging fruit such as a missing index on a very expensive query. There is only so much tuning you can do if the infrastructure is having issues.