Check Index Fragmentation…Further

01_Vault_101_Citizenship_Award

Pipboy level utility!

I found a really cool example for getting all the fragmentation on a database.  The example was so cool that I decided to adapt my previous script to this one and made a couple adjustments.

This new revision of the script will output the TSQL that you can use to either Rebuild, Reorganize the index or in the case of heaps, rebuild the table.  The scripts that are generated are based on > 30% fragmentation, 5% fragmentation

UPDATE: 02122014 – I just discovered a bug (or error) in this script. The script is not accounting for heaps. I found this out while I was performing some load testing experiments (yay for testing). I have changed the output of the rebuild statement while I debug

UPDATE: 09032014 – OK, so I think I got this bad boy working. I ended up adding a check for when the table is a heap by the sys.indexes.type_desc = ‘HEAP’

SELECT DISTINCT
sc.name AS [Schema Name] ,
DB_NAME(ps.database_idAS [Database Name] ,
OBJECT_NAME(ps.OBJECT_IDAS [Table Name] ,
ISNULL(ix.name'HEAP'AS [Index Name] ,
ps.avg_fragmentation_in_percent AS [Frag %] ,
ps.page_count AS [Page Count] ,
ix.is_disabled AS [Disabled] ,
ix.is_hypothetical AS [Hypothetical] ,
ix.fill_factor ,
CASE WHEN ps.avg_fragmentation_in_percent 30
AND ix.TYPE  0
AND ix.name IS NOT NULL
THEN 'ALTER INDEX ' ix.name ' ON ' DB_NAME(ps.database_id)
'.' sc.name '.' OBJECT_NAME(ps.OBJECT_ID)
' REBUILD'
WHEN ps.avg_fragmentation_in_percent 30
AND ix.TYPE  0
AND ix.name IS NOT NULL
THEN 'ALTER INDEX ' ix.name ' ON ' DB_NAME(ps.database_id)
'.' sc.name '.' OBJECT_NAME(ps.OBJECT_ID)
' REORGANIZE'
WHEN ix.type_desc 'HEAP'
THEN 'ALTER TABLE ' DB_NAME(ps.database_id) + '.' sc.name
'.' OBJECT_NAME(ps.OBJECT_ID) + ' REBUILD'
ELSE 'If you have reached this message, please email dbassassin@codepimp.org with as much information as possible'
END AS [Fix TSQL]
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.tables tb ON tb.OBJECT_ID ps.OBJECT_ID
INNER JOIN sys.schemas sc ON tb.schema_id sc.schema_id
INNER JOIN sys.indexes ix ON ps.OBJECT_ID ix.OBJECT_ID
AND ps.index_id ix.index_id
WHERE   ps.database_id DB_ID()
AND 
avg_fragmentation_in_percent 5
ORDER BY page_count DESC
--OPTION (MAXDOP 1)
  

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.