Tuesday, May 31, 2011

DMVs for Transaction Blocking

Blocking the process id in sql server main concern to all dba's, how to trouble shoot using DMV's

Sys.dm_tran_locks            -- find who is locking what
Sys.dm_os_waiting_tasks  --  find what is waiting
Sys.dm_db_index_operational_stats(DMF) -- find  blocking by object

e.g:
1. select * from Sys.dm_tran_locks
2. select * from Sys.dm_os_waiting_tasks order by wait_duration_ms desc

3. --select * from Sys.dm_db_index_operational_stats

--Tables where the most latch contention is occurring

select object_schema_name(ddios.object_id) + '.' + object_name(ddios.object_id) as objectName,
indexes.name, case when is_unique = 1 then 'UNIQUE ' else '' end + indexes.type_desc as index_type,
page_latch_wait_count , page_io_latch_wait_count
from sys.dm_db_index_operational_stats(db_id(),null,null,null) as ddios
join sys.indexes
on indexes.object_id = ddios.object_id
and indexes.index_id = ddios.index_id
order by page_latch_wait_count + page_io_latch_wait_count desc

No comments: