Posted by rcamarda on 03/10/07 12:37

I thought I would delve into index fragmentation and I found some
great sql from many posters (thanks Erland!).
My question is how bad is bad? I know this is very subjective.
Some scripts I found would reindex if the LogicalFragmenation is over
30%.
I have some tables that are 98% (I'm guessing really bad). I know it
all depends..

more as a learning point: I found a table that had over 30%
logicalfragmentation, I dropped the indexes, created then ran the
script that used type code segment:
'DBCC SHOWCONTIG(' + @TableName + ') WITH TABLERESULTS, ALL_INDEXES,
NO_INFOMSGS')

In one case, the indexes for the table dropped below 30%, in another
case the index was still fragmented ever after I dropped and re-
created index.

SQL Server 2005 x64 SP2

This is the script I am running (I found this in another thread that
Erland posted):

SET NOCOUNT ON
USE ds_v6_source


DECLARE @TableName VARCHAR(100)


-- Create a table to hold the results of DBCC SHOWCONTIG
IF OBJECT_ID('Tempdb.dbo.#Contig') IS NOT NULL
DROP TABLE #Contig


CREATE TABLE #Contig ([ObjectName] VARCHAR(100), [ObjectId] INT,
[IndexName]
VARCHAR(200),
[IndexId] INT, [Level] INT, [Pages] INT , [Rows] INT ,
[MinimumRecordSize] INT,
[MaximumRecordSize] INT , [AverageRecordSize] INT,
[ForwardedRecords] INT ,
[Extents] INT, [ExtentSwitches] INT, [AverageFreeBytes]
NUMERIC(6,2)
,
[AveragePageDensity] NUMERIC(6,2), [ScanDensity]
NUMERIC(6,2) ,
[BestCount] INT ,
[ActualCount] INT , [LogicalFragmentation] NUMERIC(6,2) ,
[ExtentFragmentation] NUMERIC(6,2) )


DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
OPEN curTables
FETCH NEXT FROM curTables INTO @TableName
SET @TableName = RTRIM(@TableName)


WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #Contig EXEC('DBCC SHOWCONTIG(' + @TableName + ') WITH
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT FROM curTables INTO @TableName
END


CLOSE curTables
DEALLOCATE curTables

[Back to original message]