November 14, 2008 at 2:03 pm
I have a SQL Server 2000 database that is approximately 40 GB in size. It's been a while we run optimization plan. Now we want to optimize the database but only on the tables that are badly fragmented. We run Fragmented analysis to find out wich tables are index fragmented. I executed a script to find out all the tables that are fragmented.
I want to rebuild only those indexes that have become badly
fragmented. This will reduce the amount of work to be done, and in turn reduce the need to grow the database.
I wrote a script that should do the analysis on the tables and only defrag that are fragmented. When i run following script i dont see much improvement on the density scan. i ran many times but i dont see any imrpovement... Please help.
Here is the code.
begin
INSERT INTO ShowcontigResults
(
ObjectName , ObjectID , IndexName , IndexID , Level , PagesScanned , TableRowCount , MinimumRecordSize , MaximumRecordSize, AverageRecordSize , ForwardRecords ,
ExtentsScanned , ExtentSwitches , AverageBytesFreePerPage , AveragePageDensity , ScanDensity , ScanDensity_BestCount , ScanDensity_ActualCount , LogicalScanFragmentation, ExtentScanFragmentation
)
EXEC('DBCC SHOWCONTIG WITH TABLERESULTS')
end
begin
DECLARE tempcursor CURSOR READ_ONLY FOR
SELECT DISTINCT ObjectName FROM ShowcontigResults WHERE
ObjectName NOT LIKE 'sys%' AND
ObjectName NOT LIKE 'qs%' AND
ObjectName NOT LIKE 'dt%' AND
ExtentSwitches > 0 AND
(
ScanDensity< 80 OR
(LogicalScanFragmentation+ExtentScanFragmentation)>100)
OPEN tempcursor
DECLARE @name sysname
FETCH NEXT FROM tempcursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
begin
DBCC DBREINDEX(@name," ",100) WITH ALL_INDEXES, NO_INFOMSGS
end
FETCH NEXT FROM tempcursor INTO @name
END
DEALLOCATE tempcursor
End
Thank You.
November 14, 2008 at 2:54 pm
Have you verified that the tables that you don't see improvement on have clustered indexes?
November 14, 2008 at 3:05 pm
Yes. They are all clustered index. On one of the table i noticed that only one index is clustered and all others are non clustered.
Is that why i'm not seeing any improvement on Scan Density?
Thank you!
November 25, 2008 at 12:12 pm
Any table can only have ONE clustered index.
November 25, 2008 at 12:16 pm
How big were the tables in question?
Small tables don't show changes in fragmentation, and there's no need to worry about fragmentation on small tables. (~100 pages)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply