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 

   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.

SQL Server 2014 DMV Diagnostic Queries – Part 1

SQL Server 2014 DMV Diagnostic Queries – Part 2

SQL Server 2014 DMV Diagnostic Queries – Part 3

SQL Server Diagnostic Information Queries Detailed, Day 28

For Day 28 of this series, we start out with Query #63, which is Overall Index Usage – Reads. This query retrieves information from the sys.indexes object catalog view, and the sys.dm_db_index_usage_stats dynamic management view about the overall index usage in the current database, ordered by reads. Query #63 is shown in Figure 1.

   1: --- Index Read/Write stats (all tables in current DB) ordered by Reads  (Query 63) (Overall Index Usage - Reads)

   2: SELECT OBJECT_NAME(i.[object_id]) AS [ObjectName], i.name AS [IndexName], i.index_id, 

   3:        s.user_seeks, s.user_scans, s.user_lookups,

   4:        s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads], 

   5:        s.user_updates AS [Writes],  

   6:        i.type_desc AS [Index Type], i.fill_factor AS [Fill Factor], i.has_filter, i.filter_definition, 

   7:        s.last_user_scan, s.last_user_lookup, s.last_user_seek

   8: FROM sys.indexes AS i WITH (NOLOCK)

   9: LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

  10: ON i.[object_id] = s.[object_id]

  11: AND i.index_id = s.index_id

  12: AND s.database_id = DB_ID()

  13: WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1

  14: ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC OPTION (RECOMPILE); -- Order by reads

  15:  

  16:  

  17: -- Show which indexes in the current database are most active for Reads

Figure 1: Query #63 Overall Index Usage – Reads

This query shows you which indexes in the current database have the most cumulative reads (including seeks, scans and lookups) since the instance was last restarted or the index was created. This helps you understand your workload, and shows you which indexes are the most valuable for your workload.  Another use for this query is to help identify possible data compression candidates. If you find an index on a large table with a high number of reads and a low number of writes, then it might be a good candidate for data compression if the data is highly compressible.

 

Query #64 is Overall Index Usage – Writes. This query retrieves information from the sys.indexes object catalog view, and the sys.dm_db_index_usage_stats dynamic management view about the overall index usage in the current database, ordered by writes. Query #64 is shown in Figure 2.

   1: --- Index Read/Write stats (all tables in current DB) ordered by Writes  (Query 64) (Overall Index Usage - Writes)

   2: SELECT OBJECT_NAME(i.[object_id]) AS [ObjectName], i.name AS [IndexName], i.index_id,

   3:        s.user_updates AS [Writes], s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads], 

   4:        i.type_desc AS [Index Type], i.fill_factor AS [Fill Factor], i.has_filter, i.filter_definition,

   5:        s.last_system_update, s.last_user_update

   6: FROM sys.indexes AS i WITH (NOLOCK)

   7: LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

   8: ON i.[object_id] = s.[object_id]

   9: AND i.index_id = s.index_id

  10: AND s.database_id = DB_ID()

  11: WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1

  12: ORDER BY s.user_updates DESC OPTION (RECOMPILE);                         -- Order by writes

  13:  

  14: -- Show which indexes in the current database are most active for Writes

Figure 2: Query #64 Overall Index Usage – Writes

This query shows you which indexes in the current database have the most cumulative writes since the instance was last restarted or the index was created. This helps you understand your workload, and shows you which indexes are the most volatile in your workload.  Another use for this query is to help identify possible indexes that you might consider dropping. If you find a non-clustered, non-key index on a table with a high number of writes and a very low number of reads, then you might want to drop that index, after doing some further investigation. You want to make sure that your instance has been running long enough so that you have seen your complete workload so that you don’t drop an index that is actually needed for queries that have not run yet.

SQL Server Diagnostic Information Queries Detailed, Day 27

For Day 27 of this series, we start out with Query #61, which is Volatile Indexes. This query retrieves information from the sys.objects object catalog view, the sys.stats object catalog view, and the sys.dm_db_stats_properties dynamic management function about the most volatile indexes and statistics in the current database. Query #61 is shown in Figure 1.

   1: -- Look at most frequently modified indexes and statistics (Query 61) (Volatile Indexes)

   2: SELECT o.name AS [Object Name], o.[object_id], o.type_desc, s.name AS [Statistics Name], 

   3:        s.stats_id, s.no_recompute, s.auto_created, 

   4:        sp.modification_counter, sp.rows, sp.rows_sampled, sp.last_updated

   5: FROM sys.objects AS o WITH (NOLOCK)

   6: INNER JOIN sys.stats AS s WITH (NOLOCK)

   7: ON s.object_id = o.object_id

   8: CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp

   9: WHERE o.type_desc NOT IN (N'SYSTEM_TABLE', N'INTERNAL_TABLE')

  10: AND sp.modification_counter > 0

  11: ORDER BY sp.modification_counter DESC, o.name OPTION (RECOMPILE);

Figure 1: Query #61 Volatile Indexes

This query shows you which indexes and statistics have had the most updates, which helps you understand your workload in more detail. Understanding which tables, indexes and statistics are most volatile is useful when you are thinking about how to layout your database files, how to design and configure your storage subsystem, and how to handle your index tuning and maintenance.

 

Query #62 is Index Fragmentation. This query retrieves information from the sys.dm_db_index_physical_stats dynamic management function and the sys.indexes object catalog view about the fragmentation status for all of the indexes larger than 2500 pages in the current database. Query #62 is shown in Figure 2.

   1: -- Get fragmentation info for all indexes above a certain size in the current database  (Query 62) (Index Fragmentation)

   2: -- Note: This query could take some time on a very large database

   3: SELECT DB_NAME(ps.database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name], 

   4: i.name AS [Index Name], ps.index_id, ps.index_type_desc, ps.avg_fragmentation_in_percent, 

   5: ps.fragment_count, ps.page_count, i.fill_factor, i.has_filter, i.filter_definition

   6: FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , N'LIMITED') AS ps

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

   8: ON ps.[object_id] = i.[object_id] 

   9: AND ps.index_id = i.index_id

  10: WHERE ps.database_id = DB_ID()

  11: AND ps.page_count > 2500

  12: ORDER BY ps.avg_fragmentation_in_percent DESC OPTION (RECOMPILE);

  13:  

  14: -- Helps determine whether you have framentation in your relational indexes

  15: -- and how effective your index maintenance strategy is

Figure 2: Query #62 Index Fragmentation

This query shows you the current fragmentation status for your heap tables and all indexes above 2500 pages in the current database. This helps you evaluate your index maintenance effectiveness. It is also useful when you are thinking about how you do your index maintenance and whether you might want to consider changing the fill factor on some indexes to reduce how often you need to rebuild or reorganize them.

I would say that most of the customers that I see fall into one of three categories regarding their index maintenance practices:

  1. Organizations with “Accidental DBAs” who don’t even know what index maintenance is, much less the difference between rebuild and reorganize, or about things like fill factors
    1. These organizations often have extremely high index fragmentation across most of their indexes and databases
  2. Organizations that are using the existing SQL Maintenance Plan Wizard (which is evil, BTW), which makes it far too easy for them to do redundant things like reorganize, then rebuild, then update statistics, etc.
    1. The organizations sometimes have less fragmentation, but they also often have a lot of extra transaction log activity, from redundant index maintenance activity
    2. The new SQL Maintenance Plan Wizard in SQL Server 2016 is a huge improvement, at least as far as the UI goes
  3. Organizations that are using something like Ola Hallengren’s free SQL Server Maintenance Solution for their index maintenance
    1. These organizations are usually in the best shape