Fundamentals of Query Tuning

Brent Ozar made his paid online tutorial free for the month of March. As awesome as he himself is same are his tutorials, pretty awesome. Learned so much in those ~4 hours which helped me better design my troubleshooting methodology for the query.

There were way too many things to learn but I loved decoder ring for the most common wait types. Below are the common wait types and also the way to find the queries using sp_blitzcache with sortorder to find the queries which might be causing these waits.

CXPACKET: queries going parallel to read a lot of data or do a lot of CPU work. Sort by CPU and by READS.

LCK%: locking, so look for long-running queries. Sort by DURATION, and look for the warning of “Long Running, Low CPU.” That’s probably a query being blocked.

PAGEIOLATCH: Reading data pages that aren’t cached in RAM. Sort by READS.

RESOURCE_SEMAPHORE: Queries can’t get enough workspace memory to start running. Sort by MEMORY GRANT, although that isn’t available in older versions of SQL.

SOS_SCHEDULER_YIELD: CPU pressure, so sort by CPU.

WRITELOG: Writing to the transaction log for delete/update/insert (DUI) work. Sort by WRITES.

HADR_SYNC_COMMIT: Check for Avg ms per wait and its solution is either write less data or move the secondary closer. Not related to query tuning; batching changes.

BACKUPIO: Might be taking backups way too often.

PREEMPTIVE_COM_GETDATA/PREMPTIVE_OS_waitforsingleobject/ Memory_allocation_ext: Dead bored CPU.

ASYNC_Network_IO: It is just waiting for the application to consume the data.

CLR_AUTO_EVENT: Can be ignored if it 1x or 2x of the server time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s