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.
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.
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:
- 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
- These organizations often have extremely high index fragmentation across most of their indexes and databases
- 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.
- The organizations sometimes have less fragmentation, but they also often have a lot of extra transaction log activity, from redundant index maintenance activity
- The new SQL Maintenance Plan Wizard in SQL Server 2016 is a huge improvement, at least as far as the UI goes
- Organizations that are using something like Ola Hallengren’s free SQL Server Maintenance Solution for their index maintenance
- These organizations are usually in the best shape