Microsoft SQL Sample Code

Function for Getting a List of Fragmented Indexes

This function is very useful for finding indexes in a database that have a fragmentation level greater than a percent you specify. Fragmented indexes can affect performance of your SQL Server or Azure database. After identifying these indexes, you can then tune them so that your database performs more effectively and effeciently.

CREATE Procedure [dbo].[uspIndexFragmentation] As SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent , 'ALTER INDEX ' + ind.name + ' ON ' + OBJECT_NAME(ind.OBJECT_ID) + ' REBUILD ' + char(13) + char(10) + 'GO' AS IndexRebuild FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 --Order By TableName ORDER BY indexstats.avg_fragmentation_in_percent DESC

If you need any help implementing this function or modifications made for your needs, please Click Here to get a free quote for your Microsoft SQL Development needs. 

Click Here to learn more about the WSI quoting process.