Trying to interpret SQL wait times

Hi all

I'm trying to locate performance problems with my SQL server and I had a look at the wait stats. Problem is, I'm not sure how to interpret them. I notice a high average LCK_M_S wait time and a very high number of LATCH_EX waits.

The server was restarted at 1pm yesterday, and the wait times read at 8am this morning. The server has a large number of jobs that run during the night, many of them querying other databases.

Can someone who is familiar with this please offer some comments?

Thanks

--------------------

Wait Type Requests Wait Time Signal Wait Time
-------------------------------- ------------------------ ------------------------ ------------------------
MISCELLANEOUS 154.0 0.0 0.0
LCK_M_SCH_S 0.0 0.0 0.0
LCK_M_SCH_M 0.0 0.0 0.0
LCK_M_S 1670.0 2039964.0 1428.0
LCK_M_U 465.0 134020.0 1937.0
LCK_M_X 1762.0 873514.0 110639.0
LCK_M_IS 75.0 811986.0 92.0
LCK_M_IU 0.0 0.0 0.0
LCK_M_IX 130.0 1685792.0 265.0
LCK_M_SIU 0.0 0.0 0.0
LCK_M_SIX 0.0 0.0 0.0
LCK_M_UIX 0.0 0.0 0.0
LCK_M_BU 0.0 0.0 0.0
LCK_M_RS_S 0.0 0.0 0.0
LCK_M_RS_U 0.0 0.0 0.0
LCK_M_RIn_NL 0.0 0.0 0.0
LCK_M_RIn_S 0.0 0.0 0.0
LCK_M_RIn_U 0.0 0.0 0.0
LCK_M_RIn_X 0.0 0.0 0.0
LCK_M_RX_S 0.0 0.0 0.0
LCK_M_RX_U 0.0 0.0 0.0
LCK_M_RX_X 0.0 0.0 0.0
SLEEP 549990.0 6.8502976E+7 6.8412496E+7
IO_COMPLETION 202802.0 274370.0 2620.0
ASYNC_IO_COMPLETION 28.0 172.0 0.0
RESOURCE_SEMAPHORE 0.0 0.0 0.0
DTC 565.0 28490.0 501.0
OLEDB 2824604.0 4.4249318E+8 2.7451707E+9
FAILPOINT 0.0 0.0 0.0
RESOURCE_QUEUE 1656328.0 2.722647E+8 6.8763904E+7
ASYNC_DISKPOOL_LOCK 3911.0 0.0 0.0
UMS_THREAD 0.0 0.0 0.0
PIPELINE_INDEX_STAT 0.0 0.0 0.0
PIPELINE_LOG 0.0 0.0 0.0
PIPELINE_VLM 0.0 0.0 0.0
WRITELOG 701255.0 1885143.0 211630.0
PSS_CHILD 0.0 0.0 0.0
EXCHANGE 2554.0 63159.0 1765.0
XCB 0.0 0.0 0.0
DBTABLE 4.0 14109.0 0.0
EC 0.0 0.0 0.0
TEMPOBJ 0.0 0.0 0.0
XACTLOCKINFO 0.0 0.0 0.0
LOGMGR 0.0 0.0 0.0
CMEMTHREAD 63840.0 5134.0 3805.0
CXPACKET 6149708.0 5.8710752E+7 1135793.0
PAGESUPP 71407.0 205496.0 21251.0
SHUTDOWN 0.0 0.0 0.0
WAITFOR 79.0 436509.0 436509.0
CURSOR 0.0 0.0 0.0
EXECSYNC 69.0 534389.0 78.0
LATCH_NL 0.0 0.0 0.0
LATCH_KP 0.0 0.0 0.0
LATCH_SH 2.0 0.0 0.0
LATCH_UP 2312.0 139429.0 64.0
LATCH_EX 1.532737E+7 1.7517056E+7 4113430.0
LATCH_DT 0.0 0.0 0.0
PAGELATCH_NL 0.0 0.0 0.0
PAGELATCH_KP 86.0 64.0 48.0
PAGELATCH_SH 1226309.0 22066.0 16428.0
PAGELATCH_UP 169802.0 77257.0 66192.0
PAGELATCH_EX 1256123.0 43215.0 36906.0
PAGELATCH_DT 0.0 0.0 0.0
PAGEIOLATCH_NL 0.0 0.0 0.0
PAGEIOLATCH_KP 0.0 0.0 0.0
PAGEIOLATCH_SH 763118.0 8967530.0 13694.0
PAGEIOLATCH_UP 5605.0 41693.0 421.0
PAGEIOLATCH_EX 466602.0 6764646.0 11960.0
PAGEIOLATCH_DT 0.0 0.0 0.0
TRAN_MARK_NL 0.0 0.0 0.0
TRAN_MARK_KP 0.0 0.0 0.0
TRAN_MARK_SH 0.0 0.0 0.0
TRAN_MARK_UP 0.0 0.0 0.0
TRAN_MARK_EX 0.0 0.0 0.0
TRAN_MARK_DT 0.0 0.0 0.0
NETWORKIO 816426.0 3969610.0 0.0
Total 3.2265156E+7 8.8850637E+8 2.8885345E+9

(77 row(s) affected)

is your server a Xeon with HT enabled?
if so, consider one of: disabling HT, restricting SQL Server to one logical CPU on each physical CPU, or disabling parallelism or limiting max degree of par. to 2
this is related to the cxpacket item

Thanks for the help. I've got a fair bit of experience optimising SQL, but not at this level, and I'm not the DBA.

The server has 8 xeon processors, hyperthreading is not enabled and the max degree of parallelism is set to 4

I think we are running a little tight on memory. The server has 8GB. I'm currently looking into the transaction handling in the system.

Gail

Have a look at:
http://sqldev.net/misc/WaitTypes.htm

I use the script below to check wait type usage:

---------------


-- simple waitstate script
-- GertD@SQLDev.Net
--
-- List of Wait Types: Seehttp://sqldev.net/misc/WaitTypes.htm
--
if ((object_id('waitstats') is not null) and
(objectproperty(object_id('waitstats'), 'IsUserTable') = 1))
drop table waitstats

create table waitstats (
wait_type varchar(80),
requests numeric(18, 1),
wait_time numeric (18 ,1),
signal_wait_time numeric(18, 1),
time_stamp datetime default getdate()
)

declare @start int,
@finish int
select @start = 1,
@finish = 10 -- Increments of 10 seconds, change this for longer tests

dbcc sqlperf(waitstats,clear) -- clear out wait statistics

while (@start < @finish)
begin
begin transaction
insert into waitstats(wait_type, requests, wait_time,signal_wait_time)
exec ('dbcc sqlperf(waitstats)')
commit

select @start = @start + 1
waitfor delay '00:00:10' -- every 10 seconds
end

declare @begin_time datetime,
@end_time datetime,
@total_wait numeric(18, 1)

select @begin_time = min(time_stamp),
@end_time = max(time_stamp)
from waitstats where wait_type = 'Total'

--- subtract waitfor, sleep, and resource_queue from Total
select @total_wait = sum(wait_time) + 1
from waitstats
where wait_type not in ('WAITFOR', 'SLEEP', 'RESOURCE_QUEUE', 'Total',
'***total***')
and time_stamp = @end_time

-- insert adjusted totals, rank by percentage descending
insert into waitstats values('***total***', 0, @total_wait, @total_wait,
@end_time)

-- reporting
select @begin_time as [begin time],
@end_time as [end time],
datediff(mi, @begin_time, @end_time) as [duration in min]

select wait_type,
wait_time,
percentage = cast(100 * wait_time / @total_wait as numeric(18,1))
from waitstats
where wait_type not in ('WAITFOR', 'SLEEP', 'RESOURCE_QUEUE', 'Total')
and time_stamp = @end_time
order by percentage desc