This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the previous article, I have shared basic theory about the SQL Server Latches.
In this post, I am sharing T-SQL script to find different information and usage statistics of the SQL Server Latches.
Using sys.dm_db_index_operational_stats:
Using this dynamic management function, you can find information about the Locks and Latches.
You can pass different parameters like: DatabaseID, TableID, IndexID, and PartitionNumber.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
/* Syntax: Sys.dm_db_index_operational_stats ( Database identifier | DEFAULT | 0 | NULL ,Object identifier | DEFAULT | 0 | NULL ,Index identifier | DEFAULT | -1 | 0 | NULL ,Partition number | DEFAULT | 0 | NULL ) */ SELECT OBJECT_NAME(object_id) ,page_latch_wait_count ,page_latch_wait_in_ms ,tree_page_latch_wait_count ,tree_page_latch_wait_in_ms ,Page_io_latch_wait_count ,Page_io_latch_wait_in_ms FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) |
Using sys.dm_os_latch_stats:
Using this dynamic management view, you can find different Latches counter information.
Using below DBCC, You can clear this view data for latest updated stats.
|
1 |
DBCC SQLPERF('sys.dm_os_latch_stats', CLEAR) |
SELECT buffer related information.
|
1 |
SELECT * FROM sys.dm_os_latch_stats WHERE latch_class = 'buffer' |
