Kaz on Commerce!

Recently I had to brush up my skills on index fragmentation and as a result pulled this query together from a number of useful resources on the net. Just replace ‘DATABASE_NAME’ and run it against the specified database to receive a list of indexes that need attention.

SELECT 
	OBJECT_NAME(iStats.object_id),
	i.name,
	iStats.*,
	i.*
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') iStats
INNER JOIN sys.indexes i
ON iStats.object_id = i.object_id AND iStats.index_id = i.index_id AND iStats.index_id  0 
WHERE (avg_fragmentation_in_percent > 10 OR avg_page_space_used_in_percent < 75)
ORDER BY iStats.avg_fragmentation_in_percent DESC

Internal Fragmentation is represented by the ‘avg_page_space_used_in_percent’ column which occurs when there are INSERTS, UPDATES, DELETES to a table leaving empty space within an allocated page. The poor allocation of space leads to more I/O as more pages are needed to hold same data which can cause poor query performance. Internal fragmentation is indicated when this value falls below 75

External Fragmentation is represented by the ‘avg_fragmentation_in_percent’ column. One instance when it can occur is when all records from a page are deleted and the page gets de-allocated from the index which creates gaps and increase the fragmentation. External fragmentation is indicated when this value exceeds 10.

Here are some good resources to that explain the two types of fragmentation in more detail:
http://www.sqlservercentral.com/blogs/practicalsqldba/2012/04/05/sql-server-index-fragmentation-understanding-fragmentation/
http://blog.sqlauthority.com/2010/01/12/sql-server-fragmentation-detect-fragmentation-and-eliminate-fragmentation/

And here is a query that will generate either index rebuild statements or reorganisation statements.

SELECT DISTINCT *
FROM
(
	SELECT 'ALTER INDEX [' + i.name + '] ON [' + s.name + '].[' + t.name + '] REBUILD' AS stm
	FROM sys.indexes i 
	INNER JOIN sys.tables t
	ON t.object_id = i.object_id
	INNER JOIN sys.schemas s
	ON t.schema_id = s.schema_id
	INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') iStats
	ON t.object_id = iStats.object_id AND i.index_id = iStats.index_id
	WHERE  iStats.avg_fragmentation_in_percent > 10 AND i.name IS NOT NULL
) AS tmp