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
Subscribe to:
Post Comments (Atom)

No comments:
Post a Comment