Why is the SQL Server So Slow?

It’s the perennial question: Why is my SQL server so slow? What was once a finely tuned system has become sluggish and unresponsive. Something has changed and now the server is struggling. But what is the cause? How can we find out where the hold-up is to even start working on a solution? Thankfully, SQL servers come with some pretty neat stats to help us out in the form of WAITS and QUEUES.

What are WAITS?

As your server runs processes, the operating system schedules these processes through an execution queue. Those that are running get a status of RUNNING. However, if the process lacks a resource then it becomes SUSPENDED until the resource is available, at which point it becomes RUNNABLE and then just waits for CPU time.

It is the SUSPENDED state that is most interesting. These resources are the waiting on a specific resource such as IO, memory, network or CPU. Importantly, SQL servers record this information in WAITS. These can then be accessed from the SQL Dynamic Management Views (DMVs) to give us information on what’s happening with the server.

Finding the WAITS

There are three DMVs that, when queried, will provide the necessary information. The primary aggregator for WAIT stats is Sys.dm_os_wait_stats which stores instance level wait information for all session since the last restart or since the wait stats were last cleared. By querying both Sys.dm_os_waiting_tasks and sys.dm_exec_requests together, you can also obtain detailed information at session id level of the wait types, wait times, queries that are being executed and objects that are participating.

Interpreting the WAITS

Armed with this new found information, how do we extract meaning? First off, remember that most waits will occur naturally due to things like background task. Seeing a wait is not a sign of a problem. Instead, it’s volume of waits that show us the way to the most likely blockages. Let’s quickly run through the most common WAIT types.

SOS_SCHEDULER_YIELD

This WAIT occurs when a process voluntarily yields for another. More of these indicate high CPU usage that can be caused by CPU intensive or long running queries.

PAGELATCH_X

Latches are temporary links to in-memory pages. TempDB and Indexes that are in high demand are common causes of this WAIT.

CXPACKET

Caused by queries being run over multiple CPUs. For a properly optimised OLTP application, the only reason for this to occur would be a missing index or WHERE clause.

PAGEIOLATCH_XX

If a query requests a page or data that isn’t buffered, then it must wait for it to be transferred from disk to buffer. If this keeps cropping up, then it might be due to insufficient memory or a failing disk.

ASYNC_IO_COMPLETION & IO_COMPLETION

Similar to the above, this wait indicates IO or disk subsystem problems, with a process waiting on read/write.

WRITELOG

Seen mostly on heavy transactional databases, this WAIT comes from data in the log cache being written to disk. Another IO related WAIT.

LCK_M_XX

This WAIT corresponds to a task trying to lock a resource, commonly caused by other tasks already having the resource locked due to large transactions and improper isolation/partitioning.

Armed with this sample of WAITS, you can begin to get a picture of system bottlenecks. Of course, there are many more that we haven’t covered, but these are some of the most commonly used to identify problems. The advantage is, that by knowing these common WAITS you are better prepared for problems when they arise. You will be able to identify what can be easily fixed with patches, workarounds and SQL Server performance tuning, and what requires further support.

Let DBA Services Resolve the WAITS

If you want to know more, or if your server started to slow, then you can get in touch with us at DBA Services. We offer a range of SQL Server support services, including performance tuning. We will analyse your SQL Server environment to identify specific issues that lead to performance bottlenecks. Then, we will do necessary fixes and guide you through the troubleshooting process.

Servers that are sluggish and often unresponsive can disrupt or cripple your entire business operations. Let us help keep your SQL Server fast. Give us a call. Our DBA specialists are ready to speak with you.