Sql server 2005 Waits and Queues

If max degree of parallelism = 0, you might want to use one of the following options:

1. 
   turn off parallelism completely: set max degree of parallelism to 1
   
2. 
   limit parallelism by setting max degree of parallelism to some number less than the total number of CPUs. For example if you have 8 processors, set max degree of parallelism to <=4.
   

Identify disk bottlenecks by using Performance Counters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN

Any of the following reduces these waits:

1. 
   Adding additional IO bandwidth,
   
2. 
   Balancing IO across other drives
   
3. 
   Reducing IO with appropriate indexing
   
4. 
   Check for bad query plans
   

1. 
   Disk sec/read
   
2. 
   Disk sec/write
   
3. 
   Disk queues
   

1. 
   Page Life Expectancy
   
2. 
   Checkpoint pages/sec
   
3. 
   Lazy writes/sec
   

1. 
   Full Scans/sec
   
2. 
   Index seeks/sec
   

• 
  Page faults/sec
  

SQL Profiler can be used to identify which Transact-SQL statements do scan. Select the scans event class and events scan:started and scan:completed. Include the object Id data column. Save the profiler trace to a trace table, and then search for the scans event. The scan:completed event provides the associated IO so that you can also search for high reads, writes, and duration.

Check SHOWPLAN for bad query plans

“Plain” latches are generally not related to IO. These latches can be used for a variety of things, but they are not used to synchronize access to buffer pages (PAGELATCH_x is used for that).

Possibly the most common case is contention on internal caches (not the buffer pool pages), especially when using heaps or text.

1. 
   memory pressure
   
2. 
   SQL Latch waits (ms)
   

Latch_x waits can frequently be reduced by solving LOG and PAGELATCH_UP contention. If there is no LOG or PAGELATCH_UP contention, the only other option is to partition the table/index in question in order to create multiple caches (the caches are per-index). 

1. 
   For shared locks, check Isolation level for transaction.
   
2. 
   Keep transaction as short as possible
   

• 
  Lock wait time (ms)
  

1. 
   Disk sec/read
   
2. 
   Disk sec/write
   
3. 
   Disk queues
   

Identify disk bottlenecks, by using Performance Counters, Profiler,

sys.dm_io_virtual_file_stats and SHOWPLAN

Any of the following reduces these waits:

1. 
   Adding additional IO bandwidth,
   
2. 
   Balancing IO across other drives
   
3. 
   Moving / Isolating the transaction log on its own drive
   

1. 
   Disk sec/read
   
2. 
   Disk sec/write
   
3. 
   Disk queues
   

1. 
   Page Life Expectancy
   
2. 
   Checkpoint pages/sec
   
3. 
   Lazy writes/sec
   

• 
  select * from sys.dm_io_virtual_file_stats(dbid,file#)
  

Linked server calls including four part name calls, remote procedure calls, openquery, openrowset and so on.

Queries that access DMVs, because these are implemented as OLE DB rowset providers.

Heavy Profiler tracing

1. 
   Check placement of client applications including any file input read by the client and SQL Server data and log files. See PERFMON disk secs/read and disk secs/write. If disk secs/read are high, you can add IO bandwidth, balance IO across other drives, and move or isolate the database and transaction log to its own drives
   
2. 
   Inspect Transact-SQL code for RPC, Distributed (Linked Server) and Full Text Search. Although SQL Server supports these type queries, they are sometimes performance bottlenecks.
   
3. 
   To retrieve the SQL statement involved in OLE DB waits, refer to section “Retrieving statements in the waiter list”.
   

Contention can be caused by issues other than IO or memory performance, for example, heavy concurrent inserts into the same index range can cause this kind of contention. If many inserts must be added on the same page, they are serialized using the latch. Lots of inserts into the same range can also cause page splits in the index which holds onto the latch while allocating a new page (this can take time). Any read accesses to the same range as the inserts would also conflict on the latches. The solution in these cases is to distribute the inserts using a more appropriate index.

Contention can be caused by issues other than IO or memory performance, for example, heavy concurrent inserts into the same index range can cause this kind of contention. If many inserts must be added on the same page, they are serialized using the latch. Lots of inserts into the same range can also cause page splits in the index which holds onto the latch while allocating a new page (this can take a time). Any read accesses to the same range as the inserts would also conflict on the latches. The solution in these cases is to distribute the inserts using a more appropriate

Page latch Update is used only for allocation related pages, and contention on it is frequently a sign that more files are needed. With multiple files, allocations can be distributed across multiple files therefore reducing demand on the per-file data structures stored on these pages. The contention is not IO performance, but internal allocation contention to access the pages. Adding more spindles to a file or moving the file to a faster disk does not help, nor does adding more memory.

COMMON for DSS like workload and large queries such as hash joins; must wait for memory quota (grant) before it is executed.

1. 
   Memory Grants Pending
   
2. 
   Memory Grants Outstanding
   

Identify disk bottlenecks, by using Performance Counters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN

Any of the following reduces these waits:

1. 
   Adding additional IO bandwidth,
   
2. 
   Balancing IO across other drives
   
3. 
   Moving or Isolating the transaction log on its own drive
   

1. 
   Disk sec/read
   
2. 
   Disk sec/write
   
3. 
   Disk queues
   

1. 
   Page Life Expectancy
   
2. 
   Checkpoint pages/sec
   
3. 
   Lazy writes/sec
   

• 
  select * from sys.dm_io_virtual_file_stats(dbid,file#)