SQL Server Performance Tuning Using Waits and Queues

Microsoft Corporation

July 2003

Writer: Tom Davidson

The information contained in this document represents the curr= ent view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, = it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after = the date of publication.

This white paper is for informational purposes only. MICROSOFT= MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS DOCUMENT.<= /p>

Complying with all applicable copyright laws is the responsibi= lity of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system= , or transmitted in any form or by any means (electronic, mechanical, photocopyi= ng, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any licen= se to these patents, trademarks, copyrights, or other intellectual property.

Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place or event is intended or should be inferred.

Microsoft and Windows are registered trademarks of Microsoft Corporation in the U= nited States and/or other countries.

The names of actual companies and products mentioned herein ma= y be the trademarks of their respective owners.

Performance Overview.. = 2

Waits & Queues: A Performance Methodology. 2

Wait Types. <= /span>3

Sysprocesses. 3

Track_waitstats stored procedure. 3

Track_waitstats Sample output 7

Report_waitstats stored procedure. 8

Wait Types and correlation to other Performance info. 9

QUEUES (Perfmon Counters) 21

PERFMON Counters, correlation, possible conclusions & actions. 21

Interesting PERFMON Ratios & comparisons. 33

Application Design issues. 35

Conclusion: Waits & Queues Analysis. 35

Appendix A: References. 37

Appendix B: IO.. = 37

Quick overview of IO subsystems. 37

File & Table level IO.. 37

Performance Overview

SQL Server Performance tuning using waits and queues is an effective mechanism = for identifying and resolving application performance problems. The performance of SQL Server 2000= database applications should be evaluated from several different perspectives. Each tells a different portion of = the performance story. Together t= hey paint a detailed performance picture of the whole.

Waits are represente= d by SQL Server wait statistics. S= QL Server 2000 tracks wait information anytime a user connection is waiting. The application requests resources= and can wait for its completion. This wait information is summarized and categorized across all connections so that a performance profile can be obtained for a given work load. Thus, SQL wait types identify and categorize user (or thread) waits from an application workload or user perspective.

The queues part of performance is represented by PERFMON counters. The counters show performance from a resource point of view. SQL SERVER object counters are exp= osed to PERFMON using the system table master..sysperfinfo. Finally, associations or correlati= ons of wait types to performance counters, as well as interesting performance coun= ter ratios round out the picture.

Each PERFMON object has counters that are used to measure various aspects of performance, such as transfer rates for disks or the amount of processor ti= me consumed for processors. Perf= mon counters (including system, physical disk, etc.) provide a view of performa= nce from a resource standpoint while SQL waits provide a view of performance fr= om a user connection (or application) perspective.

Correlations of wait types to perf counters, and specific ratios of perfmon counters form the basis for an application performance methodology called waits and queue= s.

Waits & Queues: A Performance Methodology

Application performance can be simply explained by looking at waits and queues. Dbcc sqlperf(waitstats) provides a valuable source of wait information from a thread (or application) point of view.&nbs= p; PERFMON on the other hand, provides a breakdown of system resource u= sage in terms of resource queues. =

Requests for system resources such as IO, are made by user connections or threads. If those requests cannot be immedi= ately satisfied, a queue of requests will wait until resources are available.

Wait Types

If a thread goes into a sleep status, a wait state is set. The wait state is contained in master..sysprocesses in the columns waittype, and lastwaittype. Lastwaittype is a character descri= ption of the last wait state for this thread.&nb= sp; It is not reset until another wait state occurs. Waittype is a varbinary wait state= that is the current wait state. A = wait time of 0 means the thread is currently running.

Sysprocesses

Each user has an associated row in the system table master..sysprocesses. The stored procedure sp_who provid= es a list of these user connections or threads as well as other connection information such as command, resource, wait types, wait time and status. When a thread waits, the columns waittype (binary(2)), waittime (int) and lastwaittype (nchar(32)) and waitresou= rce..= The values for waittype and lastwaittype columns are set by memory structures in SQL Server.

Lastwaittype is a character description of the last wait type for this thread. It is not reset until another wait= state occurs. Thus, a non-blank lastwaittype means the thread had= at least one wait state.

The current wait status is recorded in the waittype column. If the waittype is non-zero, the lastwaittype and waittype will be equivalent and indicate the current waitstate for the SPID. If waittype is 0x00, this means the thread is currently running.

Track_waitstats stored procedure

Track_waitstats is a stored procedure that will capture waitstats from DBCC SQLPERF, and provide a ranking of descending order base= d on percentage. This is useful in identifying the greatest opportunites for performance improvements. See the sample output below:

CREATE proc track_waitstats (@num_samples int=3D10,@de= laynum int=3D1,@delaytype nvarchar(10)=3D'minutes')

as

-- T. Davidson

-- @num_samples is the number of times to capture waitstats, default is 10 tim= es

-- de= fault delay interval is 1 minute

-- de= laynum is the delay interval - can be minutes or seconds

-- de= laytype specifies whether the delay interval is minutes or seconds

-- cr= eate waitstats table if it doesn't exist, otherwise truncate
set n= ocount on

if no= t exists (select 1 from sysobjects where name =3D 'waitstats')

        &= nbsp;   create table waitstats ([Wait Type] varchar(80),

        &= nbsp;           &nbs= p;   Requests numeric(20,1),

        &= nbsp;           &nbs= p;   [Wait Time] numeric (20,1),

        &= nbsp;           &nbs= p;   [Signal Wait Time] numeric(20,1),

        &= nbsp;           &nbs= p;   now datetime default getdate())

else =     truncate table waitstat= s

dbcc = sqlperf (waitstats,clear) -- clear out waitstats

decla= re @i int,@delay varchar(8),@dt varchar(3), @now datetime, @totalwait numeric(20,= 1)

        &= nbsp;   ,= @endtime datetime,@begintime datetime

   &nb= sp;        ,@hr int, @min int, @sec int

selec= t @i =3D 1

=

selec= t @dt =3D case lower(@delaytype)

        &= nbsp;   when 'minutes' then 'm'

        &= nbsp;   when 'minute' then 'm'

        &= nbsp;   when 'min' then 'm'

        &= nbsp;   when 'mm' then 'm'

        &= nbsp;   when 'mi' then 'm'

        &= nbsp;   when 'm' then 'm'

        &= nbsp;   when 'seconds' then 's'

        &= nbsp;   when 'second' then 's'

        &= nbsp;   when 'sec' then 's'

        &= nbsp;   when 'ss' then 's'

        &= nbsp;   when 's' then 's'

        &= nbsp;   else @delaytype

end

if @d= t not in ('s','m')

begin=

        &= nbsp;   print 'please supply delay type e.g. seconds or minutes'

        &= nbsp;   return

end

if @d= t =3D 's'

begin=

        &= nbsp;   select @sec =3D @delaynum % 60

        &= nbsp;   select @min =3D cast((@delaynum / 60) as int)

        &= nbsp;   select @hr =3D cast((@min / 60) as int)

        &= nbsp;   select @min =3D @min % 60

end

if @d= t =3D 'm'

begin=

        &= nbsp;   select @sec =3D 0

        &= nbsp;   select @min =3D @delaynum % 60

        &= nbsp;   select @hr =3D cast((@delaynum / 60) as int)

end

select @delay=3D right('0'+ convert(varchar(2),@hr),2) + ':' + <= /p>
        &= nbsp;   + right('0'+convert(varchar(2),@min),2) + ':' +

        &= nbsp;   + right('0'+convert(varchar(2),@sec),2)

=

if @h= r > 23 or @min > 59 or @sec > 59

begin=

        &= nbsp;   select 'hh:mm:ss delay time cannot > 23:59:59'

        &= nbsp;   select 'delay interval and type: ' + convert (varchar(10),@delaynum) + ',' + @delaytype + ' converts to ' + @delay

        &= nbsp;   return

end

=

while= (@i <=3D @num_samples)

begin=

        =      insert into waitstats ([Wait Type], Requests, [Wait Time],[Signal Wa= it Time])      &= nbsp;

        &= nbsp;   exec ('dbcc sqlperf(waitstats)')

        &= nbsp;   select @i =3D @i + 1

        &= nbsp;   waitfor delay @delay

end

selec= t @now=3Dmax(now),@begintime=3Dmin(n= ow),@endtime=3Dmax(now)

from waitstats where [wait type] =3D 'Total'

=

--- s= ubtract waitfor, sleep, and resource_queue from Total

select @totalwait =3D sum([wait time]) + 1 from waitstats

where= [wait type] not in ('Waitfor','Sleep','Resource_Queue', 'Total', '***total***') a= nd now =3D @now

=

-- in= sert adjusted totals, rank by percentage descending

inser= t into waitstats select '***total***',0,@totalwait,@totalwait,@now

select 'start, end, duration'=3D'start: ' + convert(varchar(20),@begintime,20) + '= end: ' + convert(varchar(20),@endtime,20) + ' duration (minutes): ' + convert(varchar(10), datediff(mi,@begintime,@endtime))

select 'waitstats samples'=3D@num_samples, 'delay, type'=3D convert (varchar(10),@delaynum) + ',' + @delaytype,'waitfor delay interval'=3D@delay

selec= t [wait type],[wait time],percentage=3Dcast (100*[wait time]/@totalwait as numeric(= 20,1))

from waitstats

where= [wait type] not in ('waitfor','sleep','resource_queue','total')=

and n= ow =3D @now

order= by percentage desc

Track_waitstats Sample output

The a= bove sample shows the lion’s share of wait time, 48%, being due to network= IO waits. Improving network IO i= s the single largest opportunity for improving application performance.

=

Other= lesser opportunities in the above example include LCK_M_X (exlusive locks) and WRITELOG (transaction log). Exclusive lock waits account for almost 13% of total wait time. An examination of transaction mana= gement may offer clues as to whether improvements can be made here.

=

WRITE= LOG means threads are waiting for physical writes to complete to the transaction log. Given the 11% writelog w= aits, a further analysis of PERFMON disk queues for the transaction log will conf= irm whether the IO capacity of the transaction log drives have trouble keeping = up with write requests as shown by steady and high disk queues.

The f= ollowing table contains wait types, descriptions and correlation to other performance information. It is not exhaustive but will point y= ou in the right direction. The impo= rtant idea is that if track_waitstats shows a significant amount (by percentage) = of a given wait type, you should corroborate this clue with the correlated information.   The PERFMON counter table follows with descriptions, additional correlations, and possi= ble conclusions and actions.

Report_waitstats stored procedure

The stored procedure report_waitstats can be run durin= g the execution of track_waitstats or after track_waitstats completes, to provide= a report of wait stats.

CREAT= E proc report_waitstats

as

-- T. Davidson

-- th= is proc will create waitstats report listing wait types by percentage

-- ca= n be run when track_waitstats is executing

set n= ocount on

=

decla= re @now datetime, @totalwait numeric(20,1)

        &= nbsp;   ,= @endtime datetime,@begintime datetime

   &nb= sp;        ,@hr int, @min int, @sec int

selec= t @now=3Dmax(now),@begintime=3Dmin(now),@endtime=3Dmax(now)=

from waitstats where [wait type] =3D 'Total'

=

--- s= ubtract waitfor, sleep, and resource_queue from Total

select @totalwait =3D sum([wait time]) + 1 from waitstats

where= [wait type] not in ('Waitfor','Sleep','Resource_Queue', 'Total', '***total***') a= nd now =3D @now

=

-- in= sert adjusted totals, rank by percentage descending

delete waitstats where [wait type] =3D '***total***' and now =3D @now

inser= t into waitstats select '***total***',0,@totalwait,@totalwait,@now

=

selec= t [wait type],[wait time],percentage=3Dcast (100*[wait time]/@totalwait as numeric(= 20,1))

from waitstats

where= [wait type] not in ('waitfor','sleep','resource_queue','total')=

and n= ow =3D @now

order= by percentage desc

Wait Types and correlation to other Performanc= e info

Wait Type

Category=

Description

Correlation t= o Other info

ASYNC_DISKPOOL_LOCK
IO (Restore DB)
RARE During Backup and Restore (e.g. including zeroing out pages) threads writ= ten in parallel.
Possible disk bottleneck. See disk p= erf counters for confirmation.

ASYNC_IO_COMPLETION

IO

Waiting for asynchronous IO requests to complete.

Identify disk bottlenecks, using PERF Counters, Profiler,

::fn_virtualfilestats and SHOWPLAN

Any of the following will reduce these waits:

1. Adding additional IO bandwidth,

2. Balancing IO across other drives

3. Reducing IO with proper indexing

4. Check for bad query plans

5. Check for memory pressure <= o:p>

See PERFMON Disk perf counters:

1. Disk sec/read

2. Disk sec/write

3. Disk queues

See PERFMON SQL Buffer Cache perf counters for memory pressure:

1. Page Life Expectancy

2. Checkpoint pages/sec

3. Lazywrites/sec

See PERFMON SQL Access Methods for correct indexing:

1. Full Scans/sec

2. Index seeks/sec

Check IoStallMS – IoStallMS is the number of cumulative milliseconds of IO waits for a particular file. If IoStallMS is inordinately high for one or more files, you have a disk bottleneck.

1.    select * from ::fn_virtualfilestats (dbid,file#)

2.    select * from ::fn_virtualfilestats (dbid,-1) to li= st all files for a database. <= o:p>

SQL Profiler can be used to identify which TSQL statements do scans. Select the scans event class & 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. Th= e scan:completed event will provi= de associated IO so you can also search for high reads, writes, and duration= .

Check SHOWPLAN for bad query plans

CMEMTHREAD

Waiting for thread safe memory objects

CURSOR

Asynch Cursor thread

CXPACKET

Parallel process waits. Possible ske= w of data possible lock of a range for this cpu meaning one parallel process is behind, etc.

Check for parallelism – sp_Configure “max degree of parallelism”.

If max degree of parallelism =3D 0, you may want to do one of the following:=

1. turn off parallelism entirely: 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 procs, set max degree of parallelism to <=3D4.

DBTABLE

New Checkpoint request that is waiting for outstanding checkpoint request to complete

See SQL Buffer Cache perf counters:

1. Page Life Expectancy

2. Checkpoint pages/sec

3. Lazywrites/sec

DTC

Waiting for Distributed Transaction Coordinator

Check transaction isolation level

EC

Non-parallel synchronization between parent and child thread

EXCHANGE

Waiting on a parallel process to complete, shutdown or startup.=

Check for parallelism – sp_Configure “max degree of parallelism”.

If max degree of parallelism =3D 0, you may want to do one of the following:=

1. turn off parallelism entirely: 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 procs, set max degree of parallelism to <=3D4.

EXECSYNC

Query memory and spooling to disk

IO_COMPLETION

IO

Waiting for IO requests to complete.

Identify disk bottlenecks, using PERF Counters, Profiler,

::fn_virtualfilestats and SHOWPLAN

Any of the following will reduce these waits:

1. Adding additional IO bandwidth,

2. Balancing IO across other drives

3. Reducing IO with proper indexing

4. Check for bad query plans

See Disk perf counters:

1. Disk sec/read

2. Disk sec/write

3. Disk queues

See SQL Buffer Cache perf counters:

1. Page Life Expectancy

2. Checkpoint pages/sec

3. Lazywrites/sec

See SQL Access Methods for corr= ect indexing:

1. Full Scans/sec

2. Index seeks/sec

See memory perf counter

1. Page faults/sec

Check IoStallMS

1. select * from ::fn_virtualfilestats(dbid,file#)

SQL Profiler can be used to identify which TSQL statements do scan. Select the scans event class & 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. Th= e scan:completed event will provi= de associated IO so you can also search for high reads, writes, and duration= .

Check SHOWPLAN for bad query plans

LATCH_x

Latch

Latches are short term light weight synchronization objects. Latches are not held for the dur= ation of a transaction.

“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 cac= hes (not the buffer pool pages), especially when using heaps and/or text. &nb= sp;

If high, check PERFMON for

1. memory pressure

2. SQL Latch waits (ms)

Look for LOG and Pagelatch_UP wait types.

Latch_x waits can often be alleviated by solving LOG and PAGELATCH_UP contention. In the absence of LOG and/or PAGELATCH_UP contention, the only other option is to partition the table/index in ques= tion in order to create multiple caches (the caches are per-index). =

LATCH_DT

Latch

Destroy Latch

See LATCH_x

LATCH_EX

Latch

Exclusive Latch

See LATCH_x

LATCH_KP

Latch

Keep Latch

See LATCH_x

LATCH_NL

Latch

Null Latch

See LATCH_x

LATCH_SH

Latch

Shared Latch

See LATCH_x

LATCH_UP

Latch

Update Latch

See LATCH_x

LCK_x

Lock

Possible transaction management issue.

1. For shared locks, check Isolation level for transaction.

2. Keep transaction as short as possible

See SQL Locks perf counters

1.Lock wait time (ms)

Hint: check for memory pressure, which causes more physical IO, thus prolonging= the duration of transactions and locks.

LCK_M_BU

Lock

Bulk update lock

See Lck_x

LCK_M_IS

Lock

Intent Share lock

See Lck_x

LCK_M_IU

Lock

Intent Update lock

See Lck_x

LCK_M_IX

Lock

Intent Exclusive lock

See Lck_x

LCK_M_RIn_NL

Lock

Range Intent Null Lock

See Lck_x

LCK_M_RIn_S

Lock

Range Intent Shared lock

See Lck_x

LCK_M_RIn_U

Lock

Range Intent Update lock

See Lck_x

LCK_M_RIn_X

Lock

Range Intent Exclusive lock

See Lck_x

LCK_M_RS_S

Lock

Range Shared Shared (Key-Range) lock

See Lck_x

LCK_M_RS_U

Lock

Range Shared Update (key-range) lock

See Lck_x

LCK_M_RX_S

Lock

Range Exclusive shared (key-range)

See Lck_x

LCK_M_RX_U

Lock

Range Exclusive update (key-range) lock

See Lck_x

LCK_M_RX_X

Lock

Range Exclusive Exclusive (key-range)

See Lck_x

LCK_M_S

Lock

Shared Lock:

See Lck_x

LCK_M_SCH_M

Lock

Modify schema lock:

See Lck_x

LCK_M_SCH_S

Lock

Shared Schema (Stability) lock

See Lck_x

LCK_M_SIU

Lock

Share Intent Update lock

See Lck_x

LCK_M_SIX

Lock

Share Intent Exclusive lock

See Lck_x

LCK_M_U

Lock

Update lock.

See Lck_x

LCK_M_UIX

Lock

Update intent exclusive lock

See Lck_x

LCK_M_X

Lock

Exclusive lock

See Lck_x

LOGMGR

Transaction Log

Waiting for write requests to the transaction log to complete.

Identify disk bottlenecks, using PERF Counters, Profiler,

::fn_virtualfilestats and SHOWPLAN

Any of the following will reduce these waits:

1. Adding additional IO bandwidth,

2. Balancing IO across other drives

3. Moving / Isolating the transaction log on its own drive=

See Disk perf counters:

1. Disk sec/read

2. Disk sec/write

3. Disk queues

See SQL Buffer Cache perf counters:

1. Page Life Expectancy

2. Checkpoint pages/sec

3. Lazywrites/sec

Check IoStallMS for tranlog

1. select * from ::fn_virtualfilestats(dbid,file#)

MISCELLANEOUS

Catch all wait type

NETWORKIO

Waiting on Network IO Completion. W= aiting to read or write to a client on the network

This can occur if a client is in the middle of sending packets to SQL Server, = or when SQL writes data to a client and is waiting for an ACK.

Check NIC bandwidth. 100mbits is preferable to 10mbs.

OLEDB

OLEDB waits. Common causes are:

·         SQL Server is waiting for client application to send data. Some examples include= :

·         1. BULK INSERT

·         2. CONVERT (6.5 to 2000)

·         3. Full text

·         4. Linked server calls incl. four part name calls, remote procedure calls, openquery, openrowset etc.

·         5. Queries that access virtual tables, since these = are implemented as OLEDB rowset providers.

1. Check placement of client app including any file input read by the client= and SQL Server data and log files. See PERFMON disk secs/read & disk secs/write. If disk secs/read are high, you may add additional IO bandw= idth, balance IO across other drives, or move / isolate the database and transaction lo= g on its own drives

2. Inspect TSQL code for RPC, Distributed (Linked Server) & Full Text Se= arch. While SQL server supports these = type queries, they are sometimes performance bottlenecks.
3. To get the SQL Statement involved in OLEDB waits, Select virtual table master..sysprocesses as follows:

a. SQL2000 Service Pack 3 Only

DECLARE @Handle binary(20)

SELECT @Handle =3D sql_handle FROM sysprocesses

WHERE waittype =3D 0x0042

SELECT * FROM ::fn_get_sql(@Handle)

b. SQL2000 RTM, SP1, SP2 – limited to 255 char= acters

dbcc inputbuffer (spid)

PAGEIOLATCH_x

Latches are short term synchronization objects.&= nbsp; used to synchronize access to buffer pages. PageIOLatch is used for disk to memory transfers.

If this is significant in percentage, it normally suggests disk IO subsystem issues. Check disk counters= .

PAGEIOLATCH_DT

IO Page destroy latch

See PAGEIOLATCH_x

PAGEIOLATCH_EX

IO Page latch exclusive

See PAGEIOLATCH_x

PAGEIOLATCH_KP

IO Page latch keep

See PAGEIOLATCH_x

PAGEIOLATCH_NL

IO Page latch null

See PAGEIOLATCH_x

PAGEIOLATCH_SH

IO Page latch shared

See PAGEIOLATCH_x

PAGEIOLATCH_UP

IO Page latch update

See PAGEIOLATCH_x

PAGELATCH_x

Latches are short term light weight synchronization objects. Latches are not held for the dur= ation of a transaction. Typical latching operations during row transfers to mem= ory, controlling modifications to row offset table, etc. Consequently, the duration of la= tches is normally sensitive to available memory.

If this is significant in percentage, it normally indicates cache contention= .

PAGELATCH_DT

Page latch

See PAGELATCH_x

PAGELATCH_EX

Page latch exclusive

Contention can be caused by issues other than IO or memory performance, for example, heavy concurrent inserts into the same index ra= nge can cause this type of contention. If a lot of inserts need to be placed on the same page they are serialized using the latch. A lot = of inserts into the same range can also cause page splits in the index which will hold onto the latch while allocating a new page (this can take a whi= le). Any read accesses to the same range as the inserts would also confl= ict on the latches. The solution in these cases is to distribute the inserts using a more appropriate

See PAGELATCH_x

PAGELATCH_KP

Page latch keep

See PAGELATCH_x

PAGELATCH_NL

Page latch null

See PAGELATCH_x

PAGELATCH_SH

Page latch shared

Contention can be caused by issues other than IO or memory performance, for example, heavy concurrent inserts into the same index ra= nge can cause this type of contention. If a lot of inserts need to be placed on the same page they are serialized using the latch. A lot = of inserts into the same range can also cause page splits in the index which will hold onto the latch while allocating a new page (this can take a whi= le). Any read accesses to the same range as the inserts would also confl= ict on the latches. The solution in these cases is to distribute the inserts using a more appropriate

See PAGELATCH_x

PAGELATCH_UP

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

See PAGELATCH_x

PAGESUPP

Waits for parallel page supplier. Possible disk bottleneck

Any of the following will reduce these waits:

1. Adding additional IO bandwidth,

2. Balancing IO across other drives

3. Reducing IO with proper indexing

4. Check for bad query plans

See Disk perf counters:

1. Disk sec/read

2. Disk sec/write

3. Disk queues

See SQL Buffer Cache perf counters:

1. Page Life Expectancy

2. Checkpoint pages/sec

3. Lazywrites/sec

Check IoStallMS for database

1. select * from ::fn_virtualfilestats(dbid,file#)

PIPELINE_INDEX_STAT

PIPELINE waittypes added to allow one user to perform multiple operations such as writes to log cache on behalf of himself as well as other users who are waiting for same operation. It does all log writes in single operation.

See Disk perf counters:

1. Disk sec/read

2. Disk sec/write

3. Disk queues

See SQL Buffer Cache perf counters:

1. Page Life Expectancy

2. Checkpoint pages/sec

3. Lazywrites/sec

Check IoStallMS for database

1. select * from ::fn_virtualfilestats(dbid,file#)

PIPELINE_LOG

PIPELINE waittypes added to allow one user to perform multiple operations such as writes to log cache on behalf of himself as well as other users who are waiting for same operation. Does in single operation.

See Disk perf counters:

1. Disk sec/read

2. Disk sec/write

3. Disk queues

See SQL Buffer Cache perf counters:

1. Page Life Expectancy

2. Checkpoint pages/sec

3. Lazywrites/sec

Check IoStallMS for database

1. select * from ::fn_virtualfilestats(dbid,file#)

PIPELINE_VLM

PIPELINE waittypes added to allow one user to perform multiple operations such as writes to log cache on behalf of himself as well as other users who are waiting for same operation. Does in single operation.

See Disk perf counters:

1. Disk sec/read

2. Disk sec/write

3. Disk queues

See SQL Buffer Cache perf counters:

1. Page Life Expectancy

2. Checkpoint pages/sec

3. Lazywrites/sec

Check IoStallMS for database

1. select * from ::fn_virtualfilestats(dbid,file#)

PSS_CHILD

Waiting on Asynch thread

RESOURCE_QUEUE

Internal Use only

RESOURCE_SEMAPHORE

COMMON for DSS like workload & large queries such as hash joins; must wait f= or memory quota (grant) prior to execution.

See SQL Memory Mgr perf counters

1. Memory Grants Pending

2. Memory Grants Outstanding

SHUTDOWN

Shutdown without specifying NOWAIT, waits for other users to logout before shutdown completes

Monitory SQL Statistics: User Connections

To expedite shutdown you can:

1. SHUTDOWN WITH NOWAIT

2. Use SQL Kill command to terminate user connections.

SLEEP

Internal Use only

TEMPOBJ

Dropping a global temp object that is being used by others.

TRAN_MARK_DT

Transaction latch - destroy

TRAN_MARK_EX

Transaction latch - Exclusive

TRAN_MARK_KP

Transaction latch - Keep page

TRAN_MARK_NL

Transaction latch - Null

TRAN_MARK_SH

Transaction latch - Shared

TRAN_MARK_UP

Transaction latch - Update

UMS_THREAD

Batch waiting on a worker thread to free up (or batch waiting to get a worker thread to run it)

If this is a high percentage, you can increase the number of worker threads = from the default of 255. The max= imum is 1024.

WAITFOR

Waitfor

Check for waitfor delay in TSQL code

Inspect TSQL code for “waitfor delay” statement

WRITELOG

Transaction Log

Waiting for write requests to the transaction log to complete.

Identify disk bottlenecks, using PERF Counters, Profiler,

::fn_virtualfilestats and SHOWPLAN

Any of the following will reduce these waits:

1. Adding additional IO bandwidth,

2. Balancing IO across other drives

3. Moving / Isolating the transaction log on its own drive=

See Disk perf counters:

1. Disk sec/read

2. Disk sec/write

3. Disk queues

See SQL Buffer Cache counters:

1. Page Life Expectancy

2. Checkpoint pages/sec

3. Lazywrites/sec

Check IoStallMS for tranlog

1. select * from ::fn_virtualfilestats(dbid,file#)

XACTLOCKINFO

Transaction escalation, rollback

QUEUES (Perfmon Counters)

The Queues aspect of the Waits and Queues approach to performance analysis refe= rs to PERFMON counters. PERFMON counters provide a view of = system performance from a resource standpoint.&nb= sp;

PERFMON Counters, correlation, possible conclu= sions & actions

Resource

Component

Perfmon Object

Counters to Monitor

Description

Possible conclusions / actions

Disk

Physical Disk=

Current Queue Length

Sustained high queues me= an your IO subsystem is not keeping up.

Confirm IO issues with d= isk sec/read & disk sec/write.

=

Waitstats correlation: <= o:p>

1. IO_COMPLETION

2. ASYNC_IO_COMPLETION

3. WRITELOG

4. LOGMGR

=

Avg. Disk Queue Length

=

Average of disk queues o= ver time. If this number is consistently high, disk sec/read and disk sec/write will be high as well = indicating IO bandwidth issues.

Confirm IO issues with d= isk sec/read and disk sec/write.

=

Waitstats correlation: <= o:p>

1. IO_COMPLETION

2. ASYNC_IO_COMPLETION

3. WRITELOG

4. LOGMGR

=

Disk Sec/Read=

=

Under normal circumstanc= es, reads should take 4-8ms – confirm with hardware vendor for exact re= ad time. Sustained queues will= skew this number higher because disk sec/read factors in the effects of disk queues. High numbers mean y= our IO subsystem is not keeping up with requests

=

Check individual drive performance if there are multiple drives. If it is a broad problem affecti= ng all drives, the IO subsystem is not keeping up. More drives could be beneficial.= If there is ONE very hot drive, = then look at disk activity such as location of paging file, database, transaction l= og, and other read/write activity.

If disk sec/read > no= rmal read time (ask vendor for normal read time) you can consider the following options:

1. Resolve IO bottleneck by adding more drives; spreading IO across = new drives if possible e.g. move files such as database, transaction log, other application files that are being written to or read from.

2. Check for memory pressure – see memory component.<= /o:p>

3. Check for proper indexing of SQL tables. Proper indexing can save IO. Check SQL query plans looking for scans and sorts, etc. Showp= lan identifies sorting steps.

4. Run SQL Profiler to identify TSQL statements doing scans. In Profiler, select the scans event class & scan stopped event. Go to the data column tab and ad= d object Id. Run the trace. Save the profiler trace to a trace table, and then search for the scans event. Alternately, you can search for = high duration, reads, and writes.

=

Waitstats correlation: <= o:p>

1. IO_COMPLETION

2. ASYNC_IO_COMPLETION

3. WRITELOG

4. LOGMGR

Disk Sec/Write

Under normal circumstanc= es, reads should take 4-8ms - confirm with hardware vendor. Sustained queues will skew this = disk sec/write higher because this counter factors in the effects of disk queu= es. High numbers mean your IO subsys= tem is not keeping up with requests. In some SAN environments, writes can be as low as 1-2ms.

See disk sec/read.

=

High performance (signif= icant insert, update, and delete activity) requires the transaction log to be o= n a separate drive from the database.

=

Waitstats correlation: <= o:p>

1. IO_COMPLETION

2. ASYNC_IO_COMPLETION

3. WRITELOG

4. LOGMGR

Memory / Cache

Memory=

Page Faults/sec

This counter includes bo= th hard faults (those that require disk access) and soft faults (where the faulted page is found elsewhere in physical memory.) Most processors can handle l= arge numbers of soft faults without significant consequence. However, hard fau= lts, which require disk access, can cause significant delays. See the disk component for more information.

Check for memory pressure (see SQL Server buffer manager), low data page hit ra= tes, & memory grants pending.

Pages/sec

Number of pages read fro= m or written to disk to resolve hard page faults.

=

These are hard faults th= at require physical IO to fetch the page.&n= bsp;

Compare with Page Faults= /sec.

=

Check for memory pressur= e (see SQL Server buffer manager), low data page hit rates, & memory grants pending.

CPU

Processor

%User Time

SQL Server runs in User mode. Privileged mode, is designed for operating system components and allows direct access to hard= ware and all memory.

Make sure % user time > 70%. Check task manager (taskmgr.exe) to see how much CPU sqlserver.exe= is getting. If user time < 70%, check on %Processor Time & % Privileged activity.

% Privileged Time

The operating system switches application threads to privileged mode to access operating system services

Should be < 20%. Check task manager (taskmgr.exe) to see how much CPU sqlserver.exe is getting. If %privileged time > 20%, chec= k on %Processor Time & % User Time.

%Processor Time

% of time CPU is executi= ng over sample interval.

Common uses of CPU resou= rces:

=

1. Compilation and re-compilation use CPU resources. Plan re-use and parameterization minimizes CPU consumption due to compilat= ion. For more details on compilation, recompilation, parameterization and plan re-use, see http://msdn.microsoft.com/librar= y/default.asp?url=3D/library/en-us/dnsql2k/html/sql_queryrecompilation.asp<= /a>

=

--Plan re-use is where u= secounts are > 1

select dbid, objid, cacheobjtype, objtype, usecount= s, sql

from master..syscacheobjects<= /p>
order by dbid,objid, cacheobjtype,objtype,usecounts= ,sql

=

Correlate to PERFMON cou= nters:

1. System: Processor Que= ue length

2. SQL Statistics: Compilations/sec

3. SQL Statistics: re-Compilations/sec

4. SQL Statistics: Reque= sts/sec

=

If both of the following= are true, you are cpu bound:

1. Proc time > 85% on= average

2. Context switches (see= system object) > 20K / sec

light weight pooling can= provide a 15% boost. Lightweight po= oling (also known as fiber mode) divides a thread into 10 fibers. Overhead per fiber is less than = that of individual threads.

%Idle Time

% of time CPU is idle ov= er sample interval

=

Interrupts/sec

Interrupts/sec is the av= erage rate, in incidents per second, at which the processor received and servic= ed hardware interrupts.

Correlate with other per= fmon counters such as IO, Network.

Thread=

Process

Page Faults

This counter includes bo= th hard faults (those that require disk access) and soft faults (where the faulted page is found elsewhere in physical memory.) Most processors can handle l= arge numbers of soft faults without significant consequence. However, hard fau= lts, which require disk access, can cause significant delays. See the disk component for more information.

Check for memory pressur= e (see SQL Server buffer manager), low data page hit rates, & memory grants pending, page life expectancy.

System=

Usage<= /p>

Processor Queue Length

=

Number of threads waitin= g to be scheduled for CPU time. Some common uses of CPU resources that may be avoidable:

1. Unnecessary compilati= on and recompilation. Parameteriza= tion and plan re-use would reduce CPU consumption. See http://msdn.microsoft.com/librar= y/default.asp?url=3D/library/en-us/dnsql2k/html/sql_queryrecompilation.asp<= /a>

2. memory pressure<= /o:p>

3. lack of proper indexi= ng

Context Switches/sec

=

=

SQL Server

Access Method

=

=

=

=

=

=

=

=

=

=

Forwarded Records/sec

Number of records fetched through forwarded record pointers.

=

Tables with NO clustered index. If you start out wit= h a short row, and update the row creating a wider row, the row may no longer= fit on the data page. A pointer= will be put in its place and the row will be forwarded to another page.

Look at code to determin= e where the short row is inserted followed by an update.

=

Can be avoided by:<= /o:p>

1. Using Default values = (so that an update will not result in a longer row that is the root cause of forwa= rded records).

2. Using Char instead of= varchar (fixes length so that an update will not result in a longer row

Full Scan/sec=

Entire table or index is scanned. Scans can cause excessive IO if an index would be beneficial.

SQL Profiler can be used= to identify which TSQL statements do scan.&= nbsp; Select the scans event class & events scan:started<= /b> and scan:completed. Include = the object Id data column. Save the profiler trace to a tra= ce table, and then search for the s= cans event.

The scan:completed event will provide associated IO so you can al= so search for high reads, writes, and duration.

Index Searches/sec<= /o:p>

Number of index searches= . Index searches are used to start range scans, single index record fetches, and = to reposition within an index.

Compare to Full Scan/sec= . You want to see high values for = index searches.

Page Splits/sec

Number of page splits oc= curring as the result of index pages overflowing. Normally associated with leaf pa= ges of clustered indexes and non-clustered indexes.

Page splits are extra IO= overhead that results from random inserts.

=

When there is no room on= a data page, and the row must be inserted on the page (due to index order), SQL = will split the page moving half the rows to a new page, and then insert the new row.

=

Correlate to Disk: page sec/write. If this is very = high, you may reorg the index(es) on the table(s) causing the page splits, to reduce page splits temporarily. Fillfactor will leave a certain amount of space available for inse= rts.

Memory Mgr

Memory Grants Pending

Memory resources are req= uired for each user request. If sufficient memory is not available, the user will wait until there is eno= ugh memory for the query to run.

Compare with Memory gran= ts outstanding. If grants pend= ing increases, you can do the following:

1. add more memory to SQ= L Server

2. add more physical mem= ory to the box.

3. check for memory pres= sure – see & correct indexing if you experience “out of memory” conditions.

=

Correlate to Waittype

1. RESOURCE_SEMAPHORE

Buffer Manager

Buffer cache hit ratio

Percentage of time the p= ages requested are already in cache

Check for memory pressur= e. See Checkpoint pages/sec, Lazywrites/sec and Page life expectancy.=

Checkpoint pages/sec

Pages written to disk du= ring the checkpoint process, freeing up SQL cache

Memory pressure is indic= ated if this counter is high along with <= u>high lazy writes/sec and low page life expectancy (<300 se= conds)

Lazy writes/sec

Pages written to disk by= the lazywriter, freeing up SQL cache

Memory pressure is indic= ated if this counter is high along with <= u>high lazy writes/sec and low page life expectancy (<300 se= conds)

Page life expectancy

Time in seconds the data= pages, on average, stay in SQL cache. Low page life < 300 may indicate (1) SQL cache is cold, (2) mem= ory problems or (3) missing indexes. Correlate to Lazywrites/sec and Checkpoint pages/sec

Memory pressure is indic= ated if this counter is low (<300) along with high lazy writes/sec and checkpoint pages/sec. <= /span>

=

Check for missing indexe= s and bad query plans (scans in profiler)

=

Check for high page faul= ts/sec.

Readahead pages/sec=

If memory shortages, col= d cache, or low hit rates, SQL may use worker threads to readahead (bring in pages ahead of time) to raise hit rates. By itself readahead is not a problem unless users are flushing each other’s pages consistently.

Correlate to counters fo= r SQL buffer mgr: buffer cache hit ratio, page life expectancy, lazywrites, and checkp= oint pages for memory pressure. =

=

Check for proper indexin= g and bad query plans (scans in profiler)

Cache Manager=

Cache Hit Ratio

Percentage of time the p= rocedure plan pages are already in cache e.g. procedure cache hits. I.e. how often a compiled procedure is found in the procedure cache (thus avoiding the need to recompile).

Check for memory pressur= e. See Checkpoint pages/sec, Lazywrites/sec and Page life expectancy.=

=

See SQL Profiler: Stored Procedure: CacheHit, CacheMiss, and CacheInsert to see what stored proced= ure query plans are already in cache (Hit), vs. those not in cache (Miss,Inse= rt)

=

Check for appropriate pl= an re-use in the usecounts column of master..syscacheobjects. It is often desirable for query = plans to be re-used for similar SQL although not always.

=

Select cacheobjtype, objtype, dbid, sql, usecounts =

From master..syscacheobjects<= /p>
=

See SQL Statistics: Compilations/sec for discussion of plan reuse.

=

If there is memory press= ure, plans will be discarded to make room for other data and/or procedure plan= s.

Databases

Log Flush Wait Time=

Waiting for transaction = log writes (ms)

See disk perf counters

Check transaction log fi= le ::fn_virtualfilestats(dbid, file#) for IOStallMS (waits in ms)=

Log Flush Waits/sec=

Tranlog writes per secon= d

See disk perf counters, ::fn_virtualfilestats for IOStallMS.

Log Growths

Windows will automatical= ly grow transaction log to accommodate insert, update, and delete activity.<= /o:p>

In general, growths of t= he transaction log will temporarily freeze writes to the transaction log whi= le Windows grows the transaction log file.&= nbsp; Check to see that the growth increment is large enough. If not, performance will suffer = as log growths will occur more often.

Transactions /sec

SQL Server transactions = per second

=

General Statistics<= /o:p>

Logins/sec

Number of logins per sec= ond

User connections

Logout/sec

Number of logouts per se= cond

=

User connections

Number of user connectio= ns

=

Latches

Average Latch Wait Time(= ms)

Latches are short term light weight synchronization object. Latches are not held for the dur= ation of a transaction. Typical latching operations during row transfers to mem= ory, controlling modifications to row offset table, etc.

If high, check PERFMON DISK and MEMORY objects for

1. IO bottlenecks

2. memory pressure

Normally reduced with more memory and/or IO capacity

Latch Waits/sec

See above

=

Total Latch Wait Time(ms= )

Short term light weight synchronization object. Latches are not held for the dur= ation of a transaction. Typical latching operations during row transfers to mem= ory, controlling modifications to row offset table, etc.

If high, check PERFMON DISK and MEMORY objects for

1. IO bottlenecks

2. memory pressure

Normally reduced with more memory and/or IO capacity

Locks<= /p>

Average Wait Time(ms)

Transactions should be as short as possible to limit the blocking of other users.=

Hint: check for memory pressure, which causes more physical IO, thus prolonging= the duration of transactions and locks.

Lock Wait Time(ms)<= /o:p>

Transactions should be as short as possible to limit the blocking of other users.

Hint: check for memory pressure, which causes more physical IO, thus prolonging= the duration of transactions and locks

Lock Waits/sec

Transactions should be as short as possible to limit the blocking of other users.

Hint: check for memory pressure, which causes more physical IO, thus prolonging= the duration of transactions and locks

SQL
Statistics
Compilations/sec
Includes initial compile= and subsequent re-compiles. Compilation and re-compilation ar= e CPU intensive operations. =

=

Unnecessary compilation = can sometimes be avoided with query plan re-use. Query plan re-use can be seen in= the usecounts column of syscacheobjects as follows<= /o:p>

=

Select cacheobjtype, objtype, dbid, sql, usecounts =

From master..syscacheobjects<= /p>

Parameterization is impo= rtant for plan re-use. In additio= n, some types of re-compilation can be avoided. See the SQL Server 2000 recompil= ation paper for more info: http://msdn.microsoft.com/librar= y/default.asp?url=3D/library/en-us/dnsql2k/html/sql_queryrecompilation.asp<= /a>

=

To get initial compilati= ons ONLY, you must subtract recompilations/sec from compilations/sec.

=

Compare to batch request= s/sec to see extent of compilation.

=

Recompilations/sec<= /o:p>

=

Only contains re-compile= s. SQL Profiler can provide informa= tion on what procs are recompiling, what statement, and the reason for recompilation. In Profiler, select the stored procedure = event class & SP:recompilation event, and include the data column eventsubclass. Review the trace searching for eventsubclass values 1 through 6. The statements preceding caused the recompilation. For more details on recompilatio= n, see http://msdn.microsoft.com/librar= y/default.asp?url=3D/library/en-us/dnsql2k/html/sql_queryrecompilation.asp<= /a>

Batch Requests/sec<= /o:p>

=

Total batch requests sho= uld be compared with compilations/sec

Auto-Param Attempts/sec<= o:p>

=

Auto-param attempts shou= ld be compared to failed auto-params/sec. Proper parameterization is important for plan re-use. In some cases, Sp_executeSQL cou= ld be used with adhoc SQL. For mo= re details on recompilation, see http://msdn.microsoft.com/librar= y/default.asp?url=3D/library/en-us/dnsql2k/html/sql_queryrecompilation.asp<= /a>

Failed Auto-Params/sec

Auto-param attempts shou= ld be compared to failed auto-params/sec. Proper parameterization is important for plan re-use. In some cases, Sp_executeSQL cou= ld be used with adhoc SQL. For mo= re details on recompilation, see http://msdn.microsoft.com/librar= y/default.asp?url=3D/library/en-us/dnsql2k/html/sql_queryrecompilation.asp<= /a>

Interesting PERFMON Ratios & comparisons

Some counters in PERFMON have to be compared to other counters for prop= er perspective. While the follow= ing ratios and comparisons are not exhaustive, they nonetheless will point you = in the right direction.

Batch requests/se= c vs. SQL Compilations/sec. The worst case is when compilations are very high compared with batch requ= ests. Possible memory pressure in w= hich query plans are discarded quickly to make room for other activity. Another possibility is lack of parameterization which is important for plan re-use. Parameterization is where var= iables are used instead of literal values.&n= bsp; In some cases sp_executeSQL can be beneficial. Perfmon counters are SQLServer:SQL Statistcs:Batch Requests/sec and SQLServer:= SQL Statistics:SQL Compilations/sec.

<= /span>

SQL Compilations/= sec vs. SQL Re-compilations/sec.= SQL Compilations/sec include t= he initial compile of the stored procedure while SQL Re-compilations/sec = only includes re-compiles (excludes initial compile).   If initial compiles are= low (SQL Compilations – SQL Recompilations) compared to SQL recompilations, then there is a probable recompilation problem. Perfmon counters are SQLServer:SQL Statistcs: SQL Compilations/sec and SQLServer:SQL Statistics:SQL Re-Compilations/sec.

Kernel CPU vs Use= r CPU. If (Kernel CPU/ User CPU) >= .25, may indicate a network, disk driver, or hardware issue. Network and Disk IO is servic= ed in kernel mode. SQL is serv= iced in user mode. Look at Ta= sk Manager. Perfmon counters are Processor:%Processor Time, Processor:%Use= r time, Processor:%Interrupt time.

=

SQL context switc= hes vs Kernel context switches.= Kernel level context switches = are expensive compared with SQL switching. Dbcc sqlperf(umsstats) cntxt switches are SQL switches and cntxt switches(i= dle) are Kernel switches.

Disk Queue Length= vs Disk sec/Transfer. As disk queue length increases= , so does disk sec/transfer. Perfmon counters are PhysicalDisk:Avg Disk Queue Length and PhysicalDisk:Avg Disk sec/Transfer.

Page life expectancy, checkpoint pages/sec, lazywrites/sec comparison. Memory pressure is indicated w= ith low page life expectancy, and high checkpoint pages and lazywrites/sec. Memory pressure, which adversely affects performance, can be lessened one or = more of the following

a. Adding more memory to the box

b. Increasing SQL memory

c. Avoiding table and index scans with pr= oper indexing

d. Elimination of bad query plans

Network: Current bandwidth, bytes total/sec, packets/sec. <= span style=3D'font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:"Arial= ","sans-serif"; mso-bidi-font-family:"Times New Roman"'>Network bandwidth issues shoul= d be corroborated with bytes total/sec.

Page Faults/sec vs Pages/sec. Page faults include both hard faults (those that require disk access) and soft faults (where the fau= lted page is found elsewhere in physical memory.) Most processors can handle large numbers of soft faults without significant consequence. However, hard faults, which require disk access, can cause significant delays.<= span style=3D'mso-spacerun:yes'> Pages/sec represents the numb= er of hard page faults that require physical IO to bring the pages into memo= ry.

Application Design issues

There are application design considerations resulting = from the Waits and Queues methodology. The following table describes some of the application design implications.

Observation

Application issue

Possible remedies

High IO waits

Database design

Memory pressure

Bad query plans resulting from improper indexing.

Add correct indexes to minimize IO.

Add more memory

High CPU utilization

Memory pressure

Plan re-use

Parameterization

Check for correct plan re-use, parameterization, re-compilation , see http://msdn.microsoft.com/l= ibrary/default.asp?url=3D/library/en-us/dnsql2k/html/sql_queryrecompilation= .asp

High blocking / locking

Transaction management

Redo transaction management

use correct transaction isolation levels

Conclusion: Waits & Queues Analysis

There are two complimentary sources of performance information for SQL Server. Wait types are an invaluable clue = in analyzing overall system performance from an application point of view. Wait types provide a view of system performance from a SQL thread standpoint while PERFMON provides a view of system performance from a resource standpoint.

Wait statistics should be corroborated or associated with resource counters in P= ERFMON. For example, a high SQL Server wait types signal the need for further PERFMON investigation of underlying resou= rces such as processor, IO subsystem, network and so forth. Together, these associations or correlations of wait types to perf counters, and other related counter rati= os provide a broad picture of application performance.

In come cases, the experienced performance expert must look beyond the symptom= to find the root problem. While = not exhaustive, the correlated performance info, possible conclusions and actio= ns, and interesting ratios and comparisons sections will shed light on actual r= oot problems, given the symptoms. The waits and queues methodology presented here, will identify system bottlenec= ks and propose further corroboration and conclusions, where appropriate.

In sum, the performance methodology of waits and queues draws on the available performance information comprised of Waitstats, PERFMON counters, and correlated information, to provide a broad profile of application performan= ce. It is an invaluable tool in ferret= ing out and fixing performance problems.

Appendix A: References

Microsoft SQL Server 2000 Recompilat= ion http://msdn.microsoft.com/lib= rary/default.asp?url=3D/library/en-us/dnsql2k/html/sql_queryrecompilation.a= sp

Microsoft SQL Server 2000 Performance Tuning by Whalen, G= arcia, DeLuca & Thompson

Inside SQL Server 2000 by Kalen Delaney<= /li>
Understanding and resolving SQL 2000= Blocking http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;224453

How to monitor SQL Server 2000 block= ing&nb= sp; http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;27150= 9

Appendix B: IO

Quick overview of IO subsystems

IO ba= ndwidth depends on a number of factors such as drive types, configuration (e.g. RAID type) and workload characteristics. Besides space capacity, each drive has vendor published random and sequential IO rates per drive.   RAID provides fault tolerance and additional IO capacity by combining multiple physical drives into a single logical drive. The type of RAID implemented has an effect on the extent of fault tolerance (mirror is an identical copy, vs RA= ID5 allows the loss of 1 drive), read and write performance, and space capacity= (a mirror takes twice as much space). <= /span>

=

Workl= oad characteristics dictate the mix of sequential vs. random IO. Sequential IO is common with large queries such as reports while random IO is normally associated with OLTP workloads.

File & Table level IO

SQL S= erver provides file level IO using the function ::fn_virtualfilestats. In addition, SQL Server can provid= e IOs per table or index if you are set = up properly.

=

Here = are the details. IO is reported at th= e file level. One or more files belo= ng to a file group. Objects are pla= ced on a File Group. If your file gr= oup has multiple files, IO for the File Group is spread across the files. If you place a single table on a file group, you can easily get IO for the tab= le using:

=

Select * from ::fn_virtualfilestats(dbid,file#)

=

Do= not drop tables, just drop the indexes. Then re-create the index with an ON FILEGROUP clause. If the inde= x is a clustered index, the table and clustered index are treated as a single enti= ty, and will move to the new FILEGROUP. If the index is non-clustered, only the nonclustered index is create= d on the new FILEGROUP. To get all= IO for a table, the table and all indexes should be moved to the filegroup.

Wait Type	Category=	Description	Correlation t= o Other info
ASYNC_DISKPOOL_LOCK	IO (Restore DB)	RARE During Backup and Restore (e.g. including zeroing out pages) threads writ= ten in parallel.	Possible disk bottleneck. See disk p= erf counters for confirmation.
ASYNC_IO_COMPLETION	IO	Waiting for asynchronous IO requests to complete. Identify disk bottlenecks, using PERF Counters, Profiler, ::fn_virtualfilestats and SHOWPLAN Any of the following will reduce these waits: 1. Adding additional IO bandwidth, 2. Balancing IO across other drives 3. Reducing IO with proper indexing 4. Check for bad query plans 5. Check for memory pressure <= o:p>	See PERFMON Disk perf counters: 1. Disk sec/read 2. Disk sec/write 3. Disk queues See PERFMON SQL Buffer Cache perf counters for memory pressure: 1. Page Life Expectancy 2. Checkpoint pages/sec 3. Lazywrites/sec See PERFMON SQL Access Methods for correct indexing: 1. Full Scans/sec 2. Index seeks/sec Check IoStallMS – IoStallMS is the number of cumulative milliseconds of IO waits for a particular file. If IoStallMS is inordinately high for one or more files, you have a disk bottleneck. 1. select * from ::fn_virtualfilestats (dbid,file#) 2. select * from ::fn_virtualfilestats (dbid,-1) to li= st all files for a database. <= o:p> SQL Profiler can be used to identify which TSQL statements do scans. Select the scans event class & 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. Th= e scan:completed event will provi= de associated IO so you can also search for high reads, writes, and duration= . Check SHOWPLAN for bad query plans
CMEMTHREAD		Waiting for thread safe memory objects
CURSOR		Asynch Cursor thread
CXPACKET		Parallel process waits. Possible ske= w of data possible lock of a range for this cpu meaning one parallel process is behind, etc.	Check for parallelism – sp_Configure “max degree of parallelism”. If max degree of parallelism =3D 0, you may want to do one of the following:= 1. turn off parallelism entirely: 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 procs, set max degree of parallelism to <=3D4.
DBTABLE		New Checkpoint request that is waiting for outstanding checkpoint request to complete	See SQL Buffer Cache perf counters: 1. Page Life Expectancy 2. Checkpoint pages/sec 3. Lazywrites/sec
DTC		Waiting for Distributed Transaction Coordinator	Check transaction isolation level
EC		Non-parallel synchronization between parent and child thread
EXCHANGE		Waiting on a parallel process to complete, shutdown or startup.=	Check for parallelism – sp_Configure “max degree of parallelism”. If max degree of parallelism =3D 0, you may want to do one of the following:= 1. turn off parallelism entirely: 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 procs, set max degree of parallelism to <=3D4.
EXECSYNC		Query memory and spooling to disk
IO_COMPLETION	IO	Waiting for IO requests to complete. Identify disk bottlenecks, using PERF Counters, Profiler, ::fn_virtualfilestats and SHOWPLAN Any of the following will reduce these waits: 1. Adding additional IO bandwidth, 2. Balancing IO across other drives 3. Reducing IO with proper indexing 4. Check for bad query plans	See Disk perf counters: 1. Disk sec/read 2. Disk sec/write 3. Disk queues See SQL Buffer Cache perf counters: 1. Page Life Expectancy 2. Checkpoint pages/sec 3. Lazywrites/sec See SQL Access Methods for corr= ect indexing: 1. Full Scans/sec 2. Index seeks/sec See memory perf counter 1. Page faults/sec Check IoStallMS 1. select * from ::fn_virtualfilestats(dbid,file#) SQL Profiler can be used to identify which TSQL statements do scan. Select the scans event class & 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. Th= e scan:completed event will provi= de associated IO so you can also search for high reads, writes, and duration= . Check SHOWPLAN for bad query plans
LATCH_x	Latch	Latches are short term light weight synchronization objects. Latches are not held for the dur= ation of a transaction. “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 cac= hes (not the buffer pool pages), especially when using heaps and/or text. &nb= sp;	If high, check PERFMON for 1. memory pressure 2. SQL Latch waits (ms) Look for LOG and Pagelatch_UP wait types. Latch_x waits can often be alleviated by solving LOG and PAGELATCH_UP contention. In the absence of LOG and/or PAGELATCH_UP contention, the only other option is to partition the table/index in ques= tion in order to create multiple caches (the caches are per-index). =
LATCH_DT	Latch	Destroy Latch	See LATCH_x
LATCH_EX	Latch	Exclusive Latch	See LATCH_x
LATCH_KP	Latch	Keep Latch	See LATCH_x
LATCH_NL	Latch	Null Latch	See LATCH_x
LATCH_SH	Latch	Shared Latch	See LATCH_x
LATCH_UP	Latch	Update Latch	See LATCH_x
LCK_x	Lock	Possible transaction management issue. 1. For shared locks, check Isolation level for transaction. 2. Keep transaction as short as possible	See SQL Locks perf counters 1.Lock wait time (ms) Hint: check for memory pressure, which causes more physical IO, thus prolonging= the duration of transactions and locks.
LCK_M_BU	Lock	Bulk update lock	See Lck_x
LCK_M_IS	Lock	Intent Share lock	See Lck_x
LCK_M_IU	Lock	Intent Update lock	See Lck_x
LCK_M_IX	Lock	Intent Exclusive lock	See Lck_x
LCK_M_RIn_NL	Lock	Range Intent Null Lock	See Lck_x
LCK_M_RIn_S	Lock	Range Intent Shared lock	See Lck_x
LCK_M_RIn_U	Lock	Range Intent Update lock	See Lck_x
LCK_M_RIn_X	Lock	Range Intent Exclusive lock	See Lck_x
LCK_M_RS_S	Lock	Range Shared Shared (Key-Range) lock	See Lck_x
LCK_M_RS_U	Lock	Range Shared Update (key-range) lock	See Lck_x
LCK_M_RX_S	Lock	Range Exclusive shared (key-range)	See Lck_x
LCK_M_RX_U	Lock	Range Exclusive update (key-range) lock	See Lck_x
LCK_M_RX_X	Lock	Range Exclusive Exclusive (key-range)	See Lck_x
LCK_M_S	Lock	Shared Lock:	See Lck_x
LCK_M_SCH_M	Lock	Modify schema lock:	See Lck_x
LCK_M_SCH_S	Lock	Shared Schema (Stability) lock	See Lck_x
LCK_M_SIU	Lock	Share Intent Update lock	See Lck_x
LCK_M_SIX	Lock	Share Intent Exclusive lock	See Lck_x
LCK_M_U	Lock	Update lock.	See Lck_x
LCK_M_UIX	Lock	Update intent exclusive lock	See Lck_x
LCK_M_X	Lock	Exclusive lock	See Lck_x
LOGMGR	Transaction Log	Waiting for write requests to the transaction log to complete. Identify disk bottlenecks, using PERF Counters, Profiler, ::fn_virtualfilestats and SHOWPLAN Any of the following will reduce these waits: 1. Adding additional IO bandwidth, 2. Balancing IO across other drives 3. Moving / Isolating the transaction log on its own drive=	See Disk perf counters: 1. Disk sec/read 2. Disk sec/write 3. Disk queues See SQL Buffer Cache perf counters: 1. Page Life Expectancy 2. Checkpoint pages/sec 3. Lazywrites/sec Check IoStallMS for tranlog 1. select * from ::fn_virtualfilestats(dbid,file#)
MISCELLANEOUS		Catch all wait type
NETWORKIO		Waiting on Network IO Completion. W= aiting to read or write to a client on the network This can occur if a client is in the middle of sending packets to SQL Server, = or when SQL writes data to a client and is waiting for an ACK.	Check NIC bandwidth. 100mbits is preferable to 10mbs.
OLEDB		OLEDB waits. Common causes are: · SQL Server is waiting for client application to send data. Some examples include= : · 1. BULK INSERT · 2. CONVERT (6.5 to 2000) · 3. Full text · 4. Linked server calls incl. four part name calls, remote procedure calls, openquery, openrowset etc. · 5. Queries that access virtual tables, since these = are implemented as OLEDB rowset providers.	1. Check placement of client app including any file input read by the client= and SQL Server data and log files. See PERFMON disk secs/read & disk secs/write. If disk secs/read are high, you may add additional IO bandw= idth, balance IO across other drives, or move / isolate the database and transaction lo= g on its own drives 2. Inspect TSQL code for RPC, Distributed (Linked Server) & Full Text Se= arch. While SQL server supports these = type queries, they are sometimes performance bottlenecks. 3. To get the SQL Statement involved in OLEDB waits, Select virtual table master..sysprocesses as follows: a. SQL2000 Service Pack 3 Only DECLARE @Handle binary(20) SELECT @Handle =3D sql_handle FROM sysprocesses WHERE waittype =3D 0x0042 SELECT * FROM ::fn_get_sql(@Handle) b. SQL2000 RTM, SP1, SP2 – limited to 255 char= acters dbcc inputbuffer (spid)
PAGEIOLATCH_x		Latches are short term synchronization objects.&= nbsp; used to synchronize access to buffer pages. PageIOLatch is used for disk to memory transfers.	If this is significant in percentage, it normally suggests disk IO subsystem issues. Check disk counters= .
PAGEIOLATCH_DT		IO Page destroy latch	See PAGEIOLATCH_x
PAGEIOLATCH_EX		IO Page latch exclusive	See PAGEIOLATCH_x
PAGEIOLATCH_KP		IO Page latch keep	See PAGEIOLATCH_x
PAGEIOLATCH_NL		IO Page latch null	See PAGEIOLATCH_x
PAGEIOLATCH_SH		IO Page latch shared	See PAGEIOLATCH_x
PAGEIOLATCH_UP		IO Page latch update	See PAGEIOLATCH_x
PAGELATCH_x		Latches are short term light weight synchronization objects. Latches are not held for the dur= ation of a transaction. Typical latching operations during row transfers to mem= ory, controlling modifications to row offset table, etc. Consequently, the duration of la= tches is normally sensitive to available memory.	If this is significant in percentage, it normally indicates cache contention= .
PAGELATCH_DT		Page latch	See PAGELATCH_x
PAGELATCH_EX		Page latch exclusive Contention can be caused by issues other than IO or memory performance, for example, heavy concurrent inserts into the same index ra= nge can cause this type of contention. If a lot of inserts need to be placed on the same page they are serialized using the latch. A lot = of inserts into the same range can also cause page splits in the index which will hold onto the latch while allocating a new page (this can take a whi= le). Any read accesses to the same range as the inserts would also confl= ict on the latches. The solution in these cases is to distribute the inserts using a more appropriate	See PAGELATCH_x
PAGELATCH_KP		Page latch keep	See PAGELATCH_x
PAGELATCH_NL		Page latch null	See PAGELATCH_x
PAGELATCH_SH		Page latch shared Contention can be caused by issues other than IO or memory performance, for example, heavy concurrent inserts into the same index ra= nge can cause this type of contention. If a lot of inserts need to be placed on the same page they are serialized using the latch. A lot = of inserts into the same range can also cause page splits in the index which will hold onto the latch while allocating a new page (this can take a whi= le). Any read accesses to the same range as the inserts would also confl= ict on the latches. The solution in these cases is to distribute the inserts using a more appropriate	See PAGELATCH_x
PAGELATCH_UP		Page latch Update is used only for allocation related pages, and contention on it is often a sign that more files are needed. Wi= th multiple files, allocations can be distributed across multiple files thus reducing demand on the per-file data structures stored on these pages. The contention is not IO performance, but rather internal allocation contenti= on to access the pages – adding more spindles to a file or moving the = file to a faster disk will not help, nor will adding more memory.=	See PAGELATCH_x
PAGESUPP		Waits for parallel page supplier. Possible disk bottleneck Any of the following will reduce these waits: 1. Adding additional IO bandwidth, 2. Balancing IO across other drives 3. Reducing IO with proper indexing 4. Check for bad query plans	See Disk perf counters: 1. Disk sec/read 2. Disk sec/write 3. Disk queues See SQL Buffer Cache perf counters: 1. Page Life Expectancy 2. Checkpoint pages/sec 3. Lazywrites/sec Check IoStallMS for database 1. select * from ::fn_virtualfilestats(dbid,file#)
PIPELINE_INDEX_STAT		PIPELINE waittypes added to allow one user to perform multiple operations such as writes to log cache on behalf of himself as well as other users who are waiting for same operation. It does all log writes in single operation.	See Disk perf counters: 1. Disk sec/read 2. Disk sec/write 3. Disk queues See SQL Buffer Cache perf counters: 1. Page Life Expectancy 2. Checkpoint pages/sec 3. Lazywrites/sec Check IoStallMS for database 1. select * from ::fn_virtualfilestats(dbid,file#)
PIPELINE_LOG		PIPELINE waittypes added to allow one user to perform multiple operations such as writes to log cache on behalf of himself as well as other users who are waiting for same operation. Does in single operation.	See Disk perf counters: 1. Disk sec/read 2. Disk sec/write 3. Disk queues See SQL Buffer Cache perf counters: 1. Page Life Expectancy 2. Checkpoint pages/sec 3. Lazywrites/sec Check IoStallMS for database 1. select * from ::fn_virtualfilestats(dbid,file#)
PIPELINE_VLM		PIPELINE waittypes added to allow one user to perform multiple operations such as writes to log cache on behalf of himself as well as other users who are waiting for same operation. Does in single operation.	See Disk perf counters: 1. Disk sec/read 2. Disk sec/write 3. Disk queues See SQL Buffer Cache perf counters: 1. Page Life Expectancy 2. Checkpoint pages/sec 3. Lazywrites/sec Check IoStallMS for database 1. select * from ::fn_virtualfilestats(dbid,file#)
PSS_CHILD		Waiting on Asynch thread
RESOURCE_QUEUE		Internal Use only
RESOURCE_SEMAPHORE		COMMON for DSS like workload & large queries such as hash joins; must wait f= or memory quota (grant) prior to execution.	See SQL Memory Mgr perf counters 1. Memory Grants Pending 2. Memory Grants Outstanding
SHUTDOWN		Shutdown without specifying NOWAIT, waits for other users to logout before shutdown completes	Monitory SQL Statistics: User Connections To expedite shutdown you can: 1. SHUTDOWN WITH NOWAIT 2. Use SQL Kill command to terminate user connections.
SLEEP		Internal Use only
TEMPOBJ		Dropping a global temp object that is being used by others.
TRAN_MARK_DT		Transaction latch - destroy
TRAN_MARK_EX		Transaction latch - Exclusive
TRAN_MARK_KP		Transaction latch - Keep page
TRAN_MARK_NL		Transaction latch - Null
TRAN_MARK_SH		Transaction latch - Shared
TRAN_MARK_UP		Transaction latch - Update
UMS_THREAD		Batch waiting on a worker thread to free up (or batch waiting to get a worker thread to run it)	If this is a high percentage, you can increase the number of worker threads = from the default of 255. The max= imum is 1024.
WAITFOR	Waitfor	Check for waitfor delay in TSQL code	Inspect TSQL code for “waitfor delay” statement
WRITELOG	Transaction Log	Waiting for write requests to the transaction log to complete. Identify disk bottlenecks, using PERF Counters, Profiler, ::fn_virtualfilestats and SHOWPLAN Any of the following will reduce these waits: 1. Adding additional IO bandwidth, 2. Balancing IO across other drives 3. Moving / Isolating the transaction log on its own drive=	See Disk perf counters: 1. Disk sec/read 2. Disk sec/write 3. Disk queues See SQL Buffer Cache counters: 1. Page Life Expectancy 2. Checkpoint pages/sec 3. Lazywrites/sec Check IoStallMS for tranlog 1. select * from ::fn_virtualfilestats(dbid,file#)
XACTLOCKINFO		Transaction escalation, rollback

Resource Component	Perfmon Object	Counters to Monitor	Description	Possible conclusions / actions
Disk	Physical Disk=	Current Queue Length	Sustained high queues me= an your IO subsystem is not keeping up.	Confirm IO issues with d= isk sec/read & disk sec/write. = Waitstats correlation: <= o:p> 1. IO_COMPLETION 2. ASYNC_IO_COMPLETION 3. WRITELOG 4. LOGMGR =
Avg. Disk Queue Length =	Average of disk queues o= ver time. If this number is consistently high, disk sec/read and disk sec/write will be high as well = indicating IO bandwidth issues.	Confirm IO issues with d= isk sec/read and disk sec/write. = Waitstats correlation: <= o:p> 1. IO_COMPLETION 2. ASYNC_IO_COMPLETION 3. WRITELOG 4. LOGMGR =
Disk Sec/Read= =	Under normal circumstanc= es, reads should take 4-8ms – confirm with hardware vendor for exact re= ad time. Sustained queues will= skew this number higher because disk sec/read factors in the effects of disk queues. High numbers mean y= our IO subsystem is not keeping up with requests = Check individual drive performance if there are multiple drives. If it is a broad problem affecti= ng all drives, the IO subsystem is not keeping up. More drives could be beneficial.= If there is ONE very hot drive, = then look at disk activity such as location of paging file, database, transaction l= og, and other read/write activity.	If disk sec/read > no= rmal read time (ask vendor for normal read time) you can consider the following options: 1. Resolve IO bottleneck by adding more drives; spreading IO across = new drives if possible e.g. move files such as database, transaction log, other application files that are being written to or read from. 2. Check for memory pressure – see memory component.<= /o:p> 3. Check for proper indexing of SQL tables. Proper indexing can save IO. Check SQL query plans looking for scans and sorts, etc. Showp= lan identifies sorting steps. 4. Run SQL Profiler to identify TSQL statements doing scans. In Profiler, select the scans event class & scan stopped event. Go to the data column tab and ad= d object Id. Run the trace. Save the profiler trace to a trace table, and then search for the scans event. Alternately, you can search for = high duration, reads, and writes. = Waitstats correlation: <= o:p> 1. IO_COMPLETION 2. ASYNC_IO_COMPLETION 3. WRITELOG 4. LOGMGR
Disk Sec/Write	Under normal circumstanc= es, reads should take 4-8ms - confirm with hardware vendor. Sustained queues will skew this = disk sec/write higher because this counter factors in the effects of disk queu= es. High numbers mean your IO subsys= tem is not keeping up with requests. In some SAN environments, writes can be as low as 1-2ms.	See disk sec/read. = High performance (signif= icant insert, update, and delete activity) requires the transaction log to be o= n a separate drive from the database. = Waitstats correlation: <= o:p> 1. IO_COMPLETION 2. ASYNC_IO_COMPLETION 3. WRITELOG 4. LOGMGR
Memory / Cache	Memory=	Page Faults/sec	This counter includes bo= th hard faults (those that require disk access) and soft faults (where the faulted page is found elsewhere in physical memory.) Most processors can handle l= arge numbers of soft faults without significant consequence. However, hard fau= lts, which require disk access, can cause significant delays. See the disk component for more information.	Check for memory pressure (see SQL Server buffer manager), low data page hit ra= tes, & memory grants pending.
Pages/sec	Number of pages read fro= m or written to disk to resolve hard page faults. = These are hard faults th= at require physical IO to fetch the page.&n= bsp;	Compare with Page Faults= /sec. = Check for memory pressur= e (see SQL Server buffer manager), low data page hit rates, & memory grants pending.
CPU	Processor	%User Time	SQL Server runs in User mode. Privileged mode, is designed for operating system components and allows direct access to hard= ware and all memory.	Make sure % user time > 70%. Check task manager (taskmgr.exe) to see how much CPU sqlserver.exe= is getting. If user time < 70%, check on %Processor Time & % Privileged activity.
% Privileged Time	The operating system switches application threads to privileged mode to access operating system services	Should be < 20%. Check task manager (taskmgr.exe) to see how much CPU sqlserver.exe is getting. If %privileged time > 20%, chec= k on %Processor Time & % User Time.
%Processor Time	% of time CPU is executi= ng over sample interval.	Common uses of CPU resou= rces: = 1. Compilation and re-compilation use CPU resources. Plan re-use and parameterization minimizes CPU consumption due to compilat= ion. For more details on compilation, recompilation, parameterization and plan re-use, see http://msdn.microsoft.com/librar= y/default.asp?url=3D/library/en-us/dnsql2k/html/sql_queryrecompilation.asp<= /a> = --Plan re-use is where u= secounts are > 1 select dbid, objid, cacheobjtype, objtype, usecount= s, sql from master..syscacheobjects<= /p> order by dbid,objid, cacheobjtype,objtype,usecounts= ,sql = Correlate to PERFMON cou= nters: 1. System: Processor Que= ue length 2. SQL Statistics: Compilations/sec 3. SQL Statistics: re-Compilations/sec 4. SQL Statistics: Reque= sts/sec = If both of the following= are true, you are cpu bound: 1. Proc time > 85% on= average 2. Context switches (see= system object) > 20K / sec light weight pooling can= provide a 15% boost. Lightweight po= oling (also known as fiber mode) divides a thread into 10 fibers. Overhead per fiber is less than = that of individual threads.
%Idle Time	% of time CPU is idle ov= er sample interval	=
Interrupts/sec	Interrupts/sec is the av= erage rate, in incidents per second, at which the processor received and servic= ed hardware interrupts.	Correlate with other per= fmon counters such as IO, Network.
Thread=	Process	Page Faults	This counter includes bo= th hard faults (those that require disk access) and soft faults (where the faulted page is found elsewhere in physical memory.) Most processors can handle l= arge numbers of soft faults without significant consequence. However, hard fau= lts, which require disk access, can cause significant delays. See the disk component for more information.	Check for memory pressur= e (see SQL Server buffer manager), low data page hit rates, & memory grants pending, page life expectancy.
System=	Usage<= /p>	Processor Queue Length	=	Number of threads waitin= g to be scheduled for CPU time. Some common uses of CPU resources that may be avoidable: 1. Unnecessary compilati= on and recompilation. Parameteriza= tion and plan re-use would reduce CPU consumption. See http://msdn.microsoft.com/librar= y/default.asp?url=3D/library/en-us/dnsql2k/html/sql_queryrecompilation.asp<= /a> 2. memory pressure<= /o:p> 3. lack of proper indexi= ng
Context Switches/sec	=	=
SQL Server	Access Method = = = = = = = = = =	Forwarded Records/sec	Number of records fetched through forwarded record pointers. = Tables with NO clustered index. If you start out wit= h a short row, and update the row creating a wider row, the row may no longer= fit on the data page. A pointer= will be put in its place and the row will be forwarded to another page.	Look at code to determin= e where the short row is inserted followed by an update. = Can be avoided by:<= /o:p> 1. Using Default values = (so that an update will not result in a longer row that is the root cause of forwa= rded records). 2. Using Char instead of= varchar (fixes length so that an update will not result in a longer row
Full Scan/sec=	Entire table or index is scanned. Scans can cause excessive IO if an index would be beneficial.	SQL Profiler can be used= to identify which TSQL statements do scan.&= nbsp; Select the scans event class & events scan:started<= /b> and scan:completed. Include = the object Id data column. Save the profiler trace to a tra= ce table, and then search for the s= cans event. The scan:completed event will provide associated IO so you can al= so search for high reads, writes, and duration.
Index Searches/sec<= /o:p>	Number of index searches= . Index searches are used to start range scans, single index record fetches, and = to reposition within an index.	Compare to Full Scan/sec= . You want to see high values for = index searches.
Page Splits/sec	Number of page splits oc= curring as the result of index pages overflowing. Normally associated with leaf pa= ges of clustered indexes and non-clustered indexes.	Page splits are extra IO= overhead that results from random inserts. = When there is no room on= a data page, and the row must be inserted on the page (due to index order), SQL = will split the page moving half the rows to a new page, and then insert the new row. = Correlate to Disk: page sec/write. If this is very = high, you may reorg the index(es) on the table(s) causing the page splits, to reduce page splits temporarily. Fillfactor will leave a certain amount of space available for inse= rts.
Memory Mgr	Memory Grants Pending	Memory resources are req= uired for each user request. If sufficient memory is not available, the user will wait until there is eno= ugh memory for the query to run.	Compare with Memory gran= ts outstanding. If grants pend= ing increases, you can do the following: 1. add more memory to SQ= L Server 2. add more physical mem= ory to the box. 3. check for memory pres= sure – see & correct indexing if you experience “out of memory” conditions. = Correlate to Waittype 1. RESOURCE_SEMAPHORE
Buffer Manager	Buffer cache hit ratio	Percentage of time the p= ages requested are already in cache	Check for memory pressur= e. See Checkpoint pages/sec, Lazywrites/sec and Page life expectancy.=
Checkpoint pages/sec	Pages written to disk du= ring the checkpoint process, freeing up SQL cache	Memory pressure is indic= ated if this counter is high along with <= u>high lazy writes/sec and low page life expectancy (<300 se= conds)
Lazy writes/sec	Pages written to disk by= the lazywriter, freeing up SQL cache	Memory pressure is indic= ated if this counter is high along with <= u>high lazy writes/sec and low page life expectancy (<300 se= conds)
Page life expectancy	Time in seconds the data= pages, on average, stay in SQL cache. Low page life < 300 may indicate (1) SQL cache is cold, (2) mem= ory problems or (3) missing indexes. Correlate to Lazywrites/sec and Checkpoint pages/sec	Memory pressure is indic= ated if this counter is low (<300) along with high lazy writes/sec and checkpoint pages/sec. <= /span> = Check for missing indexe= s and bad query plans (scans in profiler) = Check for high page faul= ts/sec.
Readahead pages/sec=	If memory shortages, col= d cache, or low hit rates, SQL may use worker threads to readahead (bring in pages ahead of time) to raise hit rates. By itself readahead is not a problem unless users are flushing each other’s pages consistently.	Correlate to counters fo= r SQL buffer mgr: buffer cache hit ratio, page life expectancy, lazywrites, and checkp= oint pages for memory pressure. = = Check for proper indexin= g and bad query plans (scans in profiler)
Cache Manager=	Cache Hit Ratio	Percentage of time the p= rocedure plan pages are already in cache e.g. procedure cache hits. I.e. how often a compiled procedure is found in the procedure cache (thus avoiding the need to recompile).	Check for memory pressur= e. See Checkpoint pages/sec, Lazywrites/sec and Page life expectancy.= = See SQL Profiler: Stored Procedure: CacheHit, CacheMiss, and CacheInsert to see what stored proced= ure query plans are already in cache (Hit), vs. those not in cache (Miss,Inse= rt) = Check for appropriate pl= an re-use in the usecounts column of master..syscacheobjects. It is often desirable for query = plans to be re-used for similar SQL although not always. = Select cacheobjtype, objtype, dbid, sql, usecounts = From master..syscacheobjects<= /p> = See SQL Statistics: Compilations/sec for discussion of plan reuse. = If there is memory press= ure, plans will be discarded to make room for other data and/or procedure plan= s.
Databases	Log Flush Wait Time=	Waiting for transaction = log writes (ms)	See disk perf counters Check transaction log fi= le ::fn_virtualfilestats(dbid, file#) for IOStallMS (waits in ms)=
Log Flush Waits/sec=	Tranlog writes per secon= d	See disk perf counters, ::fn_virtualfilestats for IOStallMS.
Log Growths	Windows will automatical= ly grow transaction log to accommodate insert, update, and delete activity.<= /o:p>	In general, growths of t= he transaction log will temporarily freeze writes to the transaction log whi= le Windows grows the transaction log file.&= nbsp; Check to see that the growth increment is large enough. If not, performance will suffer = as log growths will occur more often.
Transactions /sec	SQL Server transactions = per second	=
General Statistics<= /o:p>	Logins/sec	Number of logins per sec= ond	User connections
Logout/sec	Number of logouts per se= cond	=
User connections	Number of user connectio= ns	=
Latches	Average Latch Wait Time(= ms)	Latches are short term light weight synchronization object. Latches are not held for the dur= ation of a transaction. Typical latching operations during row transfers to mem= ory, controlling modifications to row offset table, etc.	If high, check PERFMON DISK and MEMORY objects for 1. IO bottlenecks 2. memory pressure Normally reduced with more memory and/or IO capacity
Latch Waits/sec	See above	=
Total Latch Wait Time(ms= )	Short term light weight synchronization object. Latches are not held for the dur= ation of a transaction. Typical latching operations during row transfers to mem= ory, controlling modifications to row offset table, etc.	If high, check PERFMON DISK and MEMORY objects for 1. IO bottlenecks 2. memory pressure Normally reduced with more memory and/or IO capacity
Locks<= /p>	Average Wait Time(ms)	Transactions should be as short as possible to limit the blocking of other users.=	Hint: check for memory pressure, which causes more physical IO, thus prolonging= the duration of transactions and locks.
Lock Wait Time(ms)<= /o:p>	Transactions should be as short as possible to limit the blocking of other users.	Hint: check for memory pressure, which causes more physical IO, thus prolonging= the duration of transactions and locks
Lock Waits/sec	Transactions should be as short as possible to limit the blocking of other users.	Hint: check for memory pressure, which causes more physical IO, thus prolonging= the duration of transactions and locks
SQL Statistics	Compilations/sec	Includes initial compile= and subsequent re-compiles. Compilation and re-compilation ar= e CPU intensive operations. = = Unnecessary compilation = can sometimes be avoided with query plan re-use. Query plan re-use can be seen in= the usecounts column of syscacheobjects as follows<= /o:p> = Select cacheobjtype, objtype, dbid, sql, usecounts = From master..syscacheobjects<= /p> Parameterization is impo= rtant for plan re-use. In additio= n, some types of re-compilation can be avoided. See the SQL Server 2000 recompil= ation paper for more info: http://msdn.microsoft.com/librar= y/default.asp?url=3D/library/en-us/dnsql2k/html/sql_queryrecompilation.asp<= /a> = To get initial compilati= ons ONLY, you must subtract recompilations/sec from compilations/sec. = Compare to batch request= s/sec to see extent of compilation.
=	Recompilations/sec<= /o:p> =	Only contains re-compile= s. SQL Profiler can provide informa= tion on what procs are recompiling, what statement, and the reason for recompilation. In Profiler, select the stored procedure = event class & SP:recompilation event, and include the data column eventsubclass. Review the trace searching for eventsubclass values 1 through 6. The statements preceding caused the recompilation. For more details on recompilatio= n, see http://msdn.microsoft.com/librar= y/default.asp?url=3D/library/en-us/dnsql2k/html/sql_queryrecompilation.asp<= /a>
Batch Requests/sec<= /o:p> =	Total batch requests sho= uld be compared with compilations/sec
Auto-Param Attempts/sec<= o:p> =	Auto-param attempts shou= ld be compared to failed auto-params/sec. Proper parameterization is important for plan re-use. In some cases, Sp_executeSQL cou= ld be used with adhoc SQL. For mo= re details on recompilation, see http://msdn.microsoft.com/librar= y/default.asp?url=3D/library/en-us/dnsql2k/html/sql_queryrecompilation.asp<= /a>
Failed Auto-Params/sec	Auto-param attempts shou= ld be compared to failed auto-params/sec. Proper parameterization is important for plan re-use. In some cases, Sp_executeSQL cou= ld be used with adhoc SQL. For mo= re details on recompilation, see http://msdn.microsoft.com/librar= y/default.asp?url=3D/library/en-us/dnsql2k/html/sql_queryrecompilation.asp<= /a>

Observation	Application issue	Possible remedies
High IO waits	Database design Memory pressure	Bad query plans resulting from improper indexing. Add correct indexes to minimize IO. Add more memory
High CPU utilization	Memory pressure Plan re-use Parameterization	Check for correct plan re-use, parameterization, re-compilation , see http://msdn.microsoft.com/l= ibrary/default.asp?url=3D/library/en-us/dnsql2k/html/sql_queryrecompilation= .asp
High blocking / locking	Transaction management	Redo transaction management use correct transaction isolation levels