Tuesday 21 October 2008

IO-Lock and Latch Wait Times

A script I run every week or so to determine index lock and latch wait times

set arithabort off
set ansi_warnings off
SET ARITHIGNORE ON
select distinct
object_name(ius.object_id) + '.' + i.name, row_lock_wait_in_ms/row_lock_wait_count as 'Average Row Lock Wait', page_lock_wait_in_ms/page_lock_wait_count as 'Average Page Lock Wait', page_io_latch_wait_in_ms/page_io_latch_wait_count as 'Average Page IO Latch Wait', allow_page_locks
from sys.dm_db_index_usage_stats ius
join
sys.dm_db_index_operational_stats(db_id(), NULL, NULL, NULL) ios
on
ius.object_id = ios.object_id
and
ius.index_id = ios.index_id
join sys.indexes i
on
ius.object_id = i.object_id
and
ius.index_id = i.index_id
join sys.sysindexes si
on
ius.object_id = si.id
and
ius.index_id = si.indid
and
ios.object_id = si.id
and
ios.index_id = si.indid
join
sys.data_spaces sds
on
partition_number = sds.data_space_id
where
ius.object_id > 97
and i.name not like 'sys%'
order by 'Average Page IO Latch Wait' desc

No comments: