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
8: ORDER BY OBJECT_NAME(i.[object_id]) OPTION (RECOMPILE);
9:
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);
18:
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.