How to monitor latch waits on an SQL server

Go to Michael Dvorkin's user page

Prerequisites
SQL Server 2005

SQL Server 2008

SQL Server 2008 R2

SQL Server 2012

Steps monitor waits statistics
1. Run the following SQL query

select d.name as database_name, o.name as table_name, i.name as index_name, leaf_insert_count+nonleaf_insert_count+leaf_update_count+nonleaf_update_count as writes, range_scan_count+singleton_lookup_count as reads, case s.row_lock_wait_count when 0 then 0 else s.row_lock_wait_in_ms/s.row_lock_wait_count end as avg_row_wait, s.row_lock_wait_in_ms, case s.page_latch_wait_count when 0 then 0 else s.page_latch_wait_in_ms/s.page_latch_wait_count end as avg_latch_wait, s.page_latch_wait_in_ms, case s.page_io_latch_wait_count when 0 then 0 else s.page_io_latch_wait_in_ms/s.page_io_latch_wait_count end as avg_io_latch_wait, s.page_io_latch_wait_in_ms from sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL) s,         sys.objects o,          sys.databases d,          sys.indexes i    where s.database_id = d.database_id and s.object_id = o.object_id and i.object_id = o.object_id and s.index_id = i.index_id order by s.page_io_latch_wait_in_ms desc

Known Issues
Go to Michael Dvorkin's user page

From HowTo Wiki, a Wikia wiki.