Your database is like a machine.
Much like your car, your database needs maintenance to ensure it runs smoothly. The indexes slowly become fragmented as you insert, delete, and update data within your tables.
As indexes become fragmented, the performance of your queries begins to suffer. With the Enterprise Edition of SQL Server, indexes can be de-fragmented automatically, but with other versions, you must keep on top of things.
Of course, you should include an automated housekeeping job to ensure this is done to some sort of cadence.
How do you determine which indexes are fragmented?
As with many parts of SQL Server, it maintains information about your indexes, tables, stored procedures, views and – well, everything – inside its own tables.
You must run queries against those SQL Server tables to access that information.
SQL Code for fragmentation information
The code below accesses that information and displays a list of indexes and their associated tables with the fragmentation percentage for each one: –
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20 and page_count > 100You can change the page_count to be > 1 to see all indexes.
The ‘DETAILED’ literal provides a more accurate percentage but is slow to run. You can also use ‘SAMPLED’ to get your results faster, although they aren’t entirely as accurate. You can decide for yourself whether you want pinpoint accuracy over speed.
