SQL SERVER – MondayMeme – 11 Words or Less

My friend Thomas LaRock [Blog | Twitter] started interested tradition of writing a blog post of 11 words of less. Following the same SQL Expert and my fellow friend Amit Banerjee [Blog | Twitter] wrote interesting 11 words statement and tagged me. So here is my MondayMeme in 11 words or less.

Here is my contribution:

“Use Wait Types and Queues to Get Quick Performance Bottleneck.”

I am not going to tag anybody but if you have quick one liner do share over here or blog yourself and link back.

What My 11 Words or Less Mean in Practice

Eleven words is a fun limit, but a one-liner can hide a lot of work. So here is the longer version of my line, for anyone who wants to try it on a real server.

SQL Server keeps track of what every task waits for. When a query cannot move ahead, because it needs a page from disk, a lock held by someone else or a turn on the CPU, the time is recorded under a wait type. The totals live in sys.dm_os_wait_stats. Sort that view by wait_time_ms and the top few rows tell you where the server spends its time.

A few wait types show up again and again:

• PAGEIOLATCH_SH: waiting to read data pages from disk into memory.
• LCK_M_X, LCK_M_S and the other lock waits: usually a sign of blocking.
• WRITELOG: waiting for the transaction log to be written to disk.
• CXPACKET: waits linked to parallel queries.
• SOS_SCHEDULER_YIELD: tasks giving up the CPU and waiting for another turn, often a hint of CPU pressure.

The queues part of my line is the other half. Waits tell you what SQL Server waited on, and performance counters for disk, memory and CPU tell you whether that resource is really under pressure. Put the two together and you stop guessing.

Two tips before you start. Many wait types are harmless background waits, so filter them out before you read the list. And since the numbers add up from the last restart, take a snapshot, wait an hour, take another one and compare them. The difference shows what is happening now. Can you say it better in 11 words or less? I would love to read your version.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.