It’s very easy to automate a report to check your index fragmentation levels on your databases. Here is a query that I use to check the fragmentation level before deciding whether or not to act on it.
General recommendation is to REORGANIZE if fragmentation is less than 30% and REBUILD if more than 30%. There is a “suggested action” column on this query that will give you the suggested action and appropriate script. Use it as you see fit. Remember though that you could alter this to generate the code to do the index rebuilds on line if you’re running Enterprise Edition.
Hope it’s useful for someone.
/**********************************************
A query to show index fragmentation levels on
the selected database.
***********************************************/
DECLARE @DatabaseName VARCHAR(8000) = 'DBA'
DECLARE @Mode VARCHAR(8000) = 'LIMITED' -- Can be LIMITED or DETAILED
/**********************************************
Execute some dynamic SQL to fetch the database
object info. Helps to avoid nasty cursors!
***********************************************/
DECLARE @sql VARCHAR(8000)IF OBJECT_ID('tempdb..#databaseObjects') IS NOT NULL DROP TABLE #databaseObjects
CREATE TABLE #databaseObjects
(
object_id INT,
schema_id INT,
name VARCHAR(8000)
)
SET @sql = 'SELECT object_id, schema_id, name FROM ' + @DatabaseName + '.sys.objects WHERE type = ''U'''
INSERT INTO #databaseObjects
EXEC(@sql)IF OBJECT_ID('tempdb..#databaseSchemas') IS NOT NULL DROP TABLE #databaseSchemas
CREATE TABLE #databaseSchemas
(
schema_id INT,
name VARCHAR(8000)
)
SET @sql = 'SELECT schema_id, name FROM ' + @DatabaseName + '.sys.schemas'
INSERT INTO #databaseSchemas
EXEC(@sql)IF OBJECT_ID('tempdb..#databaseIndexes') IS NOT NULL DROP TABLE #databaseIndexes
CREATE TABLE #databaseIndexes
(
object_id INT,
index_id INT,
name VARCHAR(8000),
fill_factor INT
)
SET @sql = 'SELECT object_id, index_id, name, fill_factor FROM ' + @DatabaseName + '.sys.indexes'
INSERT INTO #databaseIndexes
EXEC(@sql)
/**********************************************
Get the fragmentation details based on the user
selection above.
***********************************************/
SELECT
DB_NAME(database_id) AS [Database],
ds.name + '.' + do.name AS [Table],
di.name AS [IndexName],
CASE
WHEN index_type_desc = 'CLUSTERED INDEX' THEN 'Clustered Index'
WHEN index_type_desc = 'HEAP' THEN 'Heap'
WHEN index_type_desc = 'NONCLUSTERED INDEX' THEN 'Nonclustered Index'
END AS [Index Type],
page_count AS [Page Count],
avg_fragmentation_in_percent AS [Fragmentation (%)],
CAST(page_count * 8 /1024.0 as DECIMAL(28,2)) AS [IndexSize (MB)],
di.fill_factor AS [Fill Factor],
CASE
WHEN avg_fragmentation_in_percent < 5 THEN 'No action required'
WHEN avg_fragmentation_in_percent < 30 THEN 'ALTER INDEX ' + di.name + ' ON ' + @DatabaseName + '.' + ds.name + '.' + do.name + ' REORGANIZE;'
WHEN avg_fragmentation_in_percent > 30 THEN 'ALTER INDEX ' + di.name + ' ON ' + @DatabaseName + '.' + ds.name + '.' + do.name + ' REBUILD;'
END AS [Suggested Action]
FROM
sys.dm_db_index_physical_stats(DB_ID(@DatabaseName),NULL ,NULL ,NULL ,@Mode) i
JOIN #databaseObjects do ON do.object_id = i.object_id
JOIN #databaseSchemas ds ON ds.schema_id = do.schema_id
JOIN #databaseIndexes di ON di.object_id = i.object_id and di.index_id = i.index_id
ORDER BY
avg_fragmentation_in_percent DESC
Thanks,
Simon
