SQL Server Diagnostic Information Queries Detailed, Day 29

For Day 29 of this series, we start out with Query #65, which is XTP Index Usage. This query retrieves information from the sys.dm_db_xtp_index_stats dynamic management view and the sys.indexes object catalog view about the overall in-memory OLTP index usage in the current database, ordered by object name. Query #65 is shown in Figure 1.

   1: -- Get in-memory OLTP index usage (Query 65) (XTP Index Usage)

   2: SELECT OBJECT_NAME(i.[object_id]) AS [Object Name], i.index_id, i.name, i.type_desc,

   3:        xis.scans_started, xis.scans_retries, xis.rows_touched, xis.rows_returned 

   4: FROM sys.dm_db_xtp_index_stats AS xis WITH (NOLOCK)

   5: INNER JOIN sys.indexes AS i WITH (NOLOCK)

   6: ON i.[object_id] = xis.[object_id] 

   7: AND i.index_id = xis.index_id 



  10: -- This gives you some index usage statistics for in-memory OLTP

  11: -- Returns no data if you are not using in-memory OLTP

Figure 1: Query #65 XTP Index Usage

If you are using in-memory OLTP (aka Hekaton), then this query will show how your in-memory OLTP indexes are being used. Perhaps because this is an Enterprise-only feature and perhaps because it has some limitations in SQL Server 2014, I have not seen this feature being used that much out in the field yet. I think the adoption rate will improve with SQL Server 2016.


Query #66 is Lock Waits. This query retrieves information from the sys.dm_db_index_operational_stats dynamic management function, the sys.objects object catalog view, and the sys.indexes object catalog view about the cumulative lock waits in the current database. Query #66 is shown in Figure 2.

   1: -- Get lock waits for current database (Query 66) (Lock Waits)

   2: SELECT o.name AS [table_name], i.name AS [index_name], ios.index_id, ios.partition_number,

   3:         SUM(ios.row_lock_wait_count) AS [total_row_lock_waits], 

   4:         SUM(ios.row_lock_wait_in_ms) AS [total_row_lock_wait_in_ms],

   5:         SUM(ios.page_lock_wait_count) AS [total_page_lock_waits],

   6:         SUM(ios.page_lock_wait_in_ms) AS [total_page_lock_wait_in_ms],

   7:         SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) AS [total_lock_wait_in_ms]

   8: FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios

   9: INNER JOIN sys.objects AS o WITH (NOLOCK)

  10: ON ios.[object_id] = o.[object_id]

  11: INNER JOIN sys.indexes AS i WITH (NOLOCK)

  12: ON ios.[object_id] = i.[object_id] 

  13: AND ios.index_id = i.index_id

  14: WHERE o.[object_id] > 100

  15: GROUP BY o.name, i.name, ios.index_id, ios.partition_number

  16: HAVING SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) > 0

  17: ORDER BY total_lock_wait_in_ms DESC OPTION (RECOMPILE);


  19: -- This query is helpful for troubleshooting blocking and deadlocking issues

Figure 2: Query #66 Lock Waits

If you are seeing symptoms of locking/blocking/deadlocks (such as high average task counts or actual deadlock errors), then this query can show which tables and indexes are seeing the most lock waits, which can often help you troubleshoot and resolve your blocking issues.

These three Pluralsight Courses go into even more detail about how to run these queries and interpret the results.

SQL Server 2014 DMV Diagnostic Queries – Part 1

SQL Server 2014 DMV Diagnostic Queries – Part 2

SQL Server 2014 DMV Diagnostic Queries – Part 3

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.