SQL fragmentation

dthansen used Ask the Experts™
We have a large SQL database (grows 200gb) per month. 95% of the rows are in a single table.

This table has 1 clustered index and 5 non-clustered indices.

I am looking for recommendations on the following...

1. What is the best command to view the current fragmentation level of each index?

2. What level of fragmentation is generally considered acceptable?

3. What is the best approach to reduce fragmentation? Rebuild/ reorganize

Note: we have SQL enterprise and have maintenances windows available on the weekend.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
1. sys.dm_db_index_physical_stats is a good start.
2. Best practices recommend: 5-30% REORGANIZE, >30% REBUILD.
3. http://ola.hallengren.com/
Top Expert 2012

Read up on "Reorganizing and Rebuilding Indexes" in SQL Server BOL.
You can schedule the script I am using to do it automatically on weekends.

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @dbid smallint;

SET @dbid = DB_ID();
    [object_id] AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag, page_count
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0  -- Allow limited fragmentation
AND index_id > 0 -- Ignore heaps
AND page_count > 25; -- Ignore small tables

DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;
OPEN partitions;
	FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
	SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
	FROM sys.objects AS o
	JOIN sys.schemas as s ON s.schema_id = o.schema_id
	WHERE o.object_id = @objectid;
	SELECT @indexname = QUOTENAME(name)
	FROM sys.indexes
	WHERE object_id = @objectid AND index_id = @indexid;
	SELECT @partitioncount = count (*)
	FROM sys.partitions
	WHERE object_id = @objectid AND index_id = @indexid;
	-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
	IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
	IF @frag >= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
	IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
	EXEC (@command);

	PRINT N'Executed: ' + @command;
CLOSE partitions;
DEALLOCATE partitions;
DROP TABLE #work_to_do;

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

That table stores historic data?
If so you should think in partitioning table.
Good luck
Aaron ShiloChief Database Architect

this query will reaturn the best practice i have been folowing for years.

1.25% frag and higher.
2. no sys objects
3. the syntax for rebuild or reorgenize are generated automatically in the query.

SELECT db_name(a.database_id) dbname	,	object_name(s.OBJECT_ID) TableName,
i.name,page_count*8/1024 sizeMB	 ,a.avg_fragmentation_in_percent,
'ALTER INDEX ['+  i.name +'] ON ['+ object_name(s.OBJECT_ID) +'] REORGANIZE WITH ( LOB_COMPACTION = ON )' DDL_reorg,
'ALTER INDEX ['+  i.name +'] ON ['+ object_name(s.OBJECT_ID) +'] rebuild with (online=on)' DDL_rebuild
FROM sys.dm_db_partition_stats s join
sys.indexes i on (i.object_id=s.object_id and i.index_id=s.index_id)
join sys.dm_db_index_physical_stats (DB_ID(), null, NULL, NULL, NULL) AS a 
ON a.object_id = i.object_id 	AND a.index_id = i.index_id
where a.database_id = db_id()
and avg_fragmentation_in_percent > 25
And object_name(i.OBJECT_ID) not like 'sys%'
and (page_count*8/1024) > 0
and i.name is not null
order by  sizeMB desc ,avg_fragmentation_in_percent desc 

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial