Fandom

How To Wiki

How to monitor latch waits on an SQL server

1,795pages on
this wiki
Add New Page
Talk0 Share

Ad blocker interference detected!


Wikia is a free-to-use site that makes money from advertising. We have a modified experience for viewers using ad blockers

Wikia is not accessible if you’ve made further modifications. Remove the custom ad blocker rule(s) and the page will load as expected.

Go to Michael Dvorkin's user page

Prerequisites Edit

SQL Server 2005

SQL Server 2008

SQL Server 2008 R2

SQL Server 2012

Steps monitor waits statistics Edit

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 Edit

Go to Michael Dvorkin's user page


From HowTo Wiki, a Wikia wiki.

Also on Fandom

Random Wiki