SlingBlog.com

How Fragmented Is Your Data?

I’m over a very large database and it seems like every year reports take longer and longer to run. As more data gets put into the system, the longer and longer things take to run.

Turns out I have a major database fragmentation problem. I ran some code and found out that nearly all of my tables were well over 40% fragmented, some even 95%. No wonder it take so long for these things to run.

Here’s the code I used to find the problem:


SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
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


Granted this is a MSSQL server specific solution, but I’m pretty sure MySQL has something similar.

I got the code from Pinal Dave’s site. If you do a lot of DB work, go bookmark his site.