Smart Indexing Part I - Analyzing Indexes
Introduction
There are three items that any SQL Server maintenance plan should cover at a minimum: Backup, DBCC and ReIndexing.
Indexes much like your old laptop disk drive will become fragmented. As data is inserted a “page split” may put data in a different part of the disk which will ultimately result in your disk heads doing the cha cha and index usage becoming dog slow.
With SQL 2005 the new maintenance plans allow us to run a DBCC on every database (very advisable on all but the largest data warehouses), and to Re-Index all the Indexes. The problem is that once your database is over about 10GB or so this re-indexing becomes a painful experience.
The solution is only to Re-Index those indexes that have become fragmented. SQL 2005 provides a new set of three dynamic user defined functions views to help analyze indexes.
Function |
Usage |
sys.dm_db_index_physical_stats |
Returns size and fragmentation information for the data and indexes of the specified table or view. Used to determine fragmentation and row counts |
sys.dm_db_index_usage_stats |
Returns counts of different types of index operations and the time each type of operation was last performed. Used to determine actual usage of indexes in particular comparing reads to writes can help determine likely candidates for dropping. |
sys.dm_db_index_operational_stats |
Returns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database. I haven’t used this yet on a production system, but there are some nice tips on the sqlcat blog (see end of article) |
Using the physical stats we can determine what indexes need rebuilding. Recommendations on this vary, but I like to start with Rebuilding Indexes which are more than 20% fragmented (10% on larger systems).
Using the index usage stats we can detect indexes which are not used, or indexes where there is much more write activity than read. Both are potential cases for dropping.
As an Example I have combined these three new index statistics into one new report (Index_Analysis.rdl). This RDL file can be saved to your hard drive and with SP2 you can access it as a custom report
Installing the Custom Report into SSMS
· Download attached report (Index_Analysis.rdl)
· Save file into “C:\Program Files\Microsoft SQL Server\90\Tools\MyReports”
· Run SSMS
· Right Click on a database and select [Reports][Custom Report] and navigate to custom report
Conclusion
Microsoft SQL Server 2005 and the new custom reports in SP2 allow you to add your own reports to automate DBA tasks. This is ideal for querying the new dynamic management views.
In My Next Article I’ll post a stored procedure that uses the physical stats to determine which indexes require re indexing and then automates the task.
For more information:
Microsoft SQL Server Developer Center
How can SQL Server 2005 Help me evaluate and manage Indexes (excellent cat team blog).
https://blogs.msdn.com/sqlcat/archive/2006/02/13/531339.aspx