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.
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.
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.