SQL Server Performance Forum – Threads Archive

Difference between Re-indexing and Index Defrag

Hi all,
I am really confused when to perform Re-indexing and when to perform Index Defragmentation?
Could anyone please elaborate on this so that I can take decisions accordingly. Tnanks in advance. Durgesh.

See if this helps:http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Major things to bear in mind, that help drive this decision: Indexdefrag is an online utility, while reindex is not.
A very fragmented index might take considerably longer to defragment than to rebuild.
Also, DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index. The other factor I try to use to determine is the extend of the fragmentation – the worse it is, the more likely I am to try and arrange for a DBReindex. [warning]*****Long section ahead *****[/warning] The following may be of use to you – it is something I’m working on, so I’m also looking for input on it (from anyone). –I have a nightly job, in an offline environment, that has the following running via SQL Server Agent: Use [DatabaseMIS]
–************************************************** START of SCRIPT *****************************************
Set Nocount on
Print ‘Start point of script: WIP – dbcc showcontig report generations’
Select ‘Run on SQL Server : ‘+isnull(cast(@@ServerName as varchar(30)),cast(@@ServiceName as varchar(30)))
Select ‘Run on date :’, getdate()
–************************************************** DECLARE SECTION *****************************************
declare
–Reporting Variables
@RunTime datetime,
–Control Variables
@Error int,
@RowCountint,
@ErrorDescriptionvarchar (250)
–***************************************************INITIALIZE SECTION***************************************
select @RunTime = getdate()
[email protected] = ‘ >>> Script Completed Successfull <<<‘
–************************************************** BEGIN STEP SECTION **************************************
Print ‘Start of Script : ‘+convert( varchar(40),@Runtime,121) DECLARE @DBName varchar(255)
DECLARE server_cursor CURSOR FOR select name from master..sysdatabases
where name not in (‘model’,’master’,’tempdb’,’msdb’,’DatabaseMIS’) OPEN server_cursor
FETCH NEXT FROM server_cursor INTO @DBName WHILE (@@FETCH_STATUS <> -1)
BEGIN
exec (‘ use [‘[email protected]+’]
INSERT into DatabaseMis..DatabaseShowContigDetail
(ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)
EXEC (”DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES”)
Update DatabaseMis..DatabaseShowContigDetail Set DatabaseName = DB_NAME(DB_ID()) where DatabaseName is null
‘) FETCH NEXT FROM server_cursor INTO @DBName END
close server_cursor
DEALLOCATE server_cursor
Update DatabaseMis..DatabaseShowContigDetail Set ServerName = @@Servername select @Error = @@Error,@RowCount = @@RowCount
if @Error <> 0
begin
set @ErrorDescription = ‘*** ERROR Generating statistics for ShowContig for databases ***’
goto ExitScript
end
if @RowCount <> 0
begin
set @ErrorDescription = ‘*** WARNING – (0) rows inserted ***’
goto ExitScript
end
–************************************************** END of SCRIPT *******************************************
Abort:
ExitScript:–This is the common exit point for this script
[email protected]
print ‘End of Script. Runtime was : ‘+convert(varchar(40),(datediff(ss,@RunTime,getdate())),121)+’ seconds’
This populates the following table: create table DatabaseShowContigDetail
(UniqueIdintegeridentity not null
constraint [PK_DatabaseShowContigDetail] primary key clustered,
ObjectName char (255),
ObjectId integer,
IndexName char (255),
IndexId integer,
Lvl integer,
CountPages integer,
CountRows integer,
MinRecSize integer,
MaxRecSize integer,
AvgRecSize integer,
ForRecCount integer,
Extents integer,
ExtentSwitches integer,
AvgFreeBytes integer,
AvgPageDensity integer,
ScanDensity decimal,
BestCount integer,
ActualCount integer,
LogicalFrag decimal,
ExtentFrag decimal,
DateRunDateTime
constraint [DEF_DatabaseShowContigDetail_DateRun] default (getdate()),
DatabaseNamevarchar(255),
ServerNamevarchar(255))
Then the following is a report against that data: USE [DatabaseMIS] [email protected](255),
@DateToCheckdatetime [email protected] = ‘Centaur’
[email protected] = getdate()-1 –Show the top ten tables with the LOWEST average page density. SELECT
LEFT(DatabaseName,20) as DatabaseName,
LEFT(ObjectName,30) as ObjectName,
LEFT(IndexName,30) as IndexName,
AvgPageDensity,
case
when AvgPageDensity < 60 then ‘Strongly Consider DBCC DBREINDEX’
when AvgPageDensity < 70 then ‘Consider DBCC DBREINDEX’
when AvgPageDensity < 80 then ‘Consider DBCC INDEXDEFRAG’
when AvgPageDensity < 90 then ‘Between 80 and 90 is Acceptable’
else’Average Page density > 90 is GOOD’
end as ‘AveragePageDensitySuggestion’
FROM dbo.DatabaseShowContigDetail
WHERE ObjectName NOT LIKE ‘dt%’ AND
ObjectName NOT LIKE ‘sys%’
–andDatabaseName = @DatabaseName
andDateRun>@DateToCheck
ORDER BY AvgPageDensity ASC
— Generally, average page density should be high. Investigate low densities (for larger tables) closely. If you determine
— that fragmentation is an issue, recreating/rebuilding the clustered index will reorganize the data, resulting in full
— data pages (depending on the index fill-factor). If rebuilding the indexes or using DBCC DBREINDEX is not possible (the
— index is offline during the drop/re-create cycle ), consider the less effective DBCC INDEXDEFRAG. –List the top ten tables with the lowest scan density. SELECT
LEFT(DatabaseName,20) as DatabaseName,
LEFT(ObjectName,30) as ObjectName,
LEFT(IndexName,30) as IndexName,
ScanDensity,
case
when ScanDensity < 60 then ‘Strongly Consider DBCC DBREINDEX’
when ScanDensity < 70 then ‘Consider DBCC DBREINDEX’
when ScanDensity < 80 then ‘Consider DBCC INDEXDEFRAG’
when ScanDensity < 90 then ‘Between 80 and 90 is Acceptable’
else’Average Scan density > 90 is GOOD’
end as ‘AverageScanDensitySuggestion’ FROM dbo.DatabaseShowContigDetail
WHERE ObjectName NOT LIKE ‘dt%’ AND
ObjectName NOT LIKE ‘sys%’
–andDatabaseName = @DatabaseName
andDateRun>@DateToCheck
ORDER BY ScanDensity ASC — Scan density should be as high as possible. If low, consider recreating/rebuilding the clustered index or executing
–the less effective DBCC INDEXDEFRAG. –List the top ten tables with the highest amount of logical fragmentation. SELECT
LEFT(DatabaseName,20) as DatabaseName,
LEFT(ObjectName,30) as ObjectName,
LEFT(IndexName,30) as IndexName,
logicalfrag,
case
when logicalfrag > 20 then ‘Strongly Consider DBCC DBREINDEX’
when logicalfrag > 10 then ‘Consider DBCC DBREINDEX or INDEXDEFRAG’
else’Logical Fragmentation > 10 is GOOD’
end as ‘LogicalFragmentationSuggestion’ FROM dbo.DatabaseShowContigDetail
WHERE ObjectName NOT LIKE ‘dt%’ AND
ObjectName NOT LIKE ‘sys%’
–andDatabaseName = @DatabaseName
andDateRun>@DateToCheck
ORDER BY logicalfrag DESC — Investigate larger tables that appear on this list. Like the previous query, if you determine that fragmentation is an
— issue, adjust the fill-factor if necessary, recreate/rebuild the clustered index or execute the less effective
— DBCC INDEXDEFRAG. –List the top ten tables with the highest extent fragmentation. SELECT
LEFT(DatabaseName,20) as DatabaseName,
LEFT(ObjectName,30) as ObjectName,
LEFT(IndexName,30) as IndexName,
ExtentFrag,
case
when ExtentFrag > 20 then ‘Strongly Consider DBCC DBREINDEX’
when ExtentFrag > 10 then ‘Consider DBCC DBREINDEX or INDEXDEFRAG’
else’Logical Fragmentation > 10 is GOOD’
end as ‘ExtentFragmentationSuggestion’
FROM dbo.DatabaseShowContigDetail
WHERE ObjectName NOT LIKE ‘dt%’ AND
ObjectName NOT LIKE ‘sys%’
–andDatabaseName = @DatabaseName
andDateRun>@DateToCheck
ORDER BY ExtentFrag DESC — Extent fragmentation should be as low as possible. Investigate larger tables that appear on this list. Like the
— previous query, if you determine that fragmentation is an issue, recreate/rebuild the clustered index or execute
— the less effective DBCC INDEXDEFRAG. SELECT
LEFT(DatabaseName,20) as DatabaseName,
LEFT(ObjectName,30) as ObjectName,
LEFT(IndexName,30) as IndexName,
AvgRecSize,
AvgFreeBytes,
MaxRecSize,
(cast((AvgFreeBytes*countpages)as dec(18,4))/1024)/1024 as ‘FreeSpaceInMB’,
CountRows,
(cast(AvgFreeBytes as dec(18,4))/8092)*100 as ‘%PageFree’,
case
when (cast(AvgFreeBytes as dec(18,4))/8092)*100 > 50
then ‘NOTE: > 50% of Page free seems Excessive – Consider whether datatypes and sizes are approriate – Space saving could be significant’
when (cast(AvgFreeBytes as dec(18,4))/8092)*100 > 30
then ‘Between 30% and 50% seems High – Consider whether datatypes and sizes are approriate’
else ‘Less than 30% Page free is generally acceptable’
end as ‘PageFreeRecommendations’
FROM dbo.DatabaseShowContigDetail
WHERE ObjectName NOT LIKE ‘dt%’ AND
ObjectName NOT LIKE ‘sys%’
–andDatabaseName = @DatabaseName
andDateRun>@DateToCheck
ORDER BY FreeSpaceInMB DESC — Generally, the average bytes free value should be low, so investigate high values (for larger tables). "Low" and "high"
— are relative terms to your database , but be sure to check your index fill-factor. Is your index fill-factor too low?
— If you’re not performing that many insert/update operations, consider increasing the fill-factor and rebuilding your
— indexes – thus decreasing I/O. Panic, Chaos, Disorder … my work here is done –unknown

Hi, What BOL says: <blockquote id="quote">quote:<hr height="1" noshade id="quote">DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table. By allowing an index to be rebuilt dynamically, indexes enforcing either PRIMARY KEY or UNIQUE constraints can be rebuilt without having to drop and re-create those constraints. This means an index can be rebuilt without knowing the table’s structure or constraints, which could occur after a bulk copy of data into the table. If either index_name or fillfactor is specified, all preceding parameters must also be specified. DBCC DBREINDEX is an offline operation. While this operation is running, the underlying table is unavailable to users of the database. DBCC DBREINDEX can rebuild all of the indexes for a table in one statement, which is easier than coding multiple DROP INDEX and CREATE INDEX statements. Because the work is done by one statement, DBCC DBREINDEX is automatically atomic, while individual DROP INDEX and CREATE INDEX statements would have to be put in a transaction to be atomic. Also, DBCC DBREINDEX can take advantage of more optimizations with DBCC DBREINDEX than it can with individual DROP INDEX and CREATE INDEX statements. DBCC DBREINDEX is not supported for use on system tables. —————————————————————————————– DBCC INDEXDEFRAG can defragment clustered and nonclustered indexes on tables and views. DBCC INDEXDEFRAG defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes, thus improving index-scanning performance. DBCC INDEXDEFRAG also compacts the pages of an index, taking into account the FILLFACTOR specified when the index was created. Any empty pages created as a result of this compaction will be removed. For more information about FILLFACTOR, see CREATE INDEX. If an index spans more than one file, DBCC INDEXDEFRAG defragments one file at a time. Pages do not migrate between files. Every five minutes, DBCC INDEXDEFRAG will report to the user an estimated percentage completed. DBCC INDEXDEFRAG can be terminated at any point in the process, and any completed work is retained. DBCC INDEXDEFRAG is an online operation. While this operation is running, the underlying table is available to users of the database. A relatively unfragmented index can be defragmented faster than a new index can be built because the time to defragment is related to the amount of fragmentation. A very fragmented index might take considerably longer to defragment than to rebuild. In addition, the defragmentation is always fully logged, regardless of the database recovery model setting (see ALTER DATABASE). The defragmentation of a very fragmented index can generate more log than even a fully logged index creation. The defragmentation, however, is performed as a series of short transactions and thus does not require a large log if log backups are taken frequently or if the recovery model setting is SIMPLE. Also, DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index. DBCC INDEXDEFRAG is not supported for use on system tables.<hr height="1" noshade id="quote"></blockquote id="quote"> [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

I’d be really interested in feedback (and corrections of assumptions) on the above process I outlined. Panic, Chaos, Disorder … my work here is done –unknown

Thanks Frank. That is really a very useful link —– "direct from the horses mouth" <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Durgesh.

]]>