Check for SQL Index Fragmentation

Check for SQL Index Fragmentation

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 > 100

You 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.

Stephen

Hi, my name is Stephen Finchett. I have been a software engineer for over 30 years and worked on complex, business critical, multi-user systems for all of my career. For the last 15 years, I have been concentrating on web based solutions using the Microsoft Stack including ASP.Net, C#, TypeScript, SQL Server and running everything at scale within Kubernetes.