Show me the wait stats

21 May,2016 by Jack Vamvas

Using wait stats to identify pressure points on your SQL Server is a common method for troubleshooting. Unfortunately , it is very easy to jump to conclusions about problems on the basis of the highest wait stats.

My general approach is to use wait stats , but letting them guide me to extra analysis. There could be all sorts of reasons why a wait stat or a combination of wait stats appear .

The more you use wait stats , you develop methods for troubleshooting. Interpreting the initial findings will help you start to ask the right question.

These are some of the wait stats available categorised. This is where the waits are appearing, but there could be multiple reasons for their occurrence.

Memory

    RESOURCE_SEMAPHORE  -   SQL WAIT TYPE – RESOURCE SEMAPHORE and how to reduce it ...

   PAGELATCH_XX              

Network

  ASYNC_NETWORK_IO

Storage

   IO_COMPLETION       SQL Server – IO_COMPLETION and how to reduce it (SQL Server ...

  SOS_SCHEDULER_YIELD    SQL Server – SOS_SCHEDULER_YIELD and how to reduce it (SQL ...

  PAGEIOLATCH_XX    SQL Server – Pageiolatch_ex and how to reduce it

  BACKUPIO             BACKUPIO and sql database backup (SQL Server DBA)

  BACKUPBUFFER   

  WRITELOG      SQL Server –WRITELOG and how to reduce it (SQL Server DBA)

Locks and Latches

   PAGEIOLATCH_XX

   PAGELATCH_XX

   LCK_M_XX      SQL Server – LCK_M_X and how to reduce it (SQL Server DBA)

  LATCH_XX

CPU

    CXPACKET  SQL Server - Waitstats CXPACKET wait type and how to reduce it ...

    THREADPOOL  How to reduce THREADPOOL wait type (SQL Server DBA)

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Show me the wait stats


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer