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