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
