find index fragmentation and perform defragment

Question

Post reply

find index fragmentation and perform defragment

gary1

SSC-Insane

Points: 21152
More actions
March 14, 2010 at 3:07 am

#139295

Hi,
We have SQL Server 2005 EE x64 with SP3 having MOSS 2007 SP1 databases.
To find Index Fragmentation in our content database, I have ran the below query:
SELECT database_id,object_id, index_id, index_type_desc,avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (8, NULL, NULL, NULL, 'limited')
where avg_fragmentation_in_percent>10 AND page_count>1000 AND Inex_id>0
order by page_count desc
Results: Just one index got fragmented as below:
database_id object_id index_id index_type_desc avg_fragmentation_in_percent page_count
----------- ----------- ----------- ------------------------------------------------------------
8 53575229 2 NONCLUSTERED INDEX 65.31 1332
If ran the above query by chaning page_count>100, we are getting 5 indexes:
8 53575229 2 NONCLUSTERED INDEX 65.31 1332
8 277576027 1 CLUSTERED INDEX 40.49 563
8 277576027 3 NONCLUSTERED INDEX 88.53 506
8 277576027 6 NONCLUSTERED INDEX 88.27 503
8 277576027 2 NONCLUSTERED INDEX 88.44 502
(5 row(s) affected)
So performing Index defrag for indexes that are having page_count>1000 only is a valid/acceptable/best practice method or not?
Can we use the below procedure to defragment content database indexes
Query:
-- Ensure a USE statement has been executed first.
SET NOCOUNT ON;
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);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
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;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GTR SSCrazy Eights Points: 8007 More actions · Answer 1

There is no standard protocol, but Rebuilding index with 1000 or more pages is recommended.

EnjoY!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

GTR (3/14/2010)
There is no standard protocol, but Rebuilding index with 1000 or more pages is recommended.

What is the link for this recommendation, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 3

For a highly flexible, configurable and well regarded index maintenance script which does what you appear to be looking for try http://sqlfool.com/2009/06/index-defrag-script-v30/

gorachandindia 30156 Old Hand Points: 363 More actions · Answer 4

If pages >1000 then you can do defragmentation

I suggest instaead of looping throgh sysobjects and doing defragmentation you can take a maintenance window and run this command

use [DatabaseName]

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ',90 )"

GO