DBCC INDEXDEFRAG , DBREINDEX

I modified the DBCC INDEXDEFRAG script from SQL Server
BOL. I added the DBCC DBREINDEX to the script. I used in
the script listed below if the logical fragmentation is
greater than 75 then used DBCC DBREINDEX, else then
between 5 and 75 then use DBCC INDEXDEFRAG.
The script
INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
eliminates all cluster indexes.
Should this statement be eliminate from my script?
I would like to re-index or defragment all indexes in my
database without using the SQL Server maintenance
Optimization job.
Thanks,
Don
SET NOCOUNT ON
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.IndexName VARCHAR (255)
DECLARE @.maxfrag DECIMAL
DECLARE @.CmdType VARCHAR (255)
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 5.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255) NULL,
ObjectId INT NULL,
IndexName CHAR (255) NULL,
IndexId INT NULL,
Lvl INT NULL,
CountPages INT NULL,
CountRows INT NULL,
MinRecSize INT NULL,
MaxRecSize INT NULL,
AvgRecSize INT NULL,
ForRecCount INT NULL,
Extents INT NULL,
ExtentSwitches INT NULL,
AvgFreeBytes INT NULL,
AvgPageDensity INT NULL,
ScanDensity DECIMAL NULL,
BestCount INT NULL,
ActualCount INT NULL,
LogicalFrag DECIMAL NULL,
ExtentFrag DECIMAL NULL)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @.tablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @.tablename
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT#FragList. ObjectName
, #FragList. ObjectId
, #FragList. IndexId
, #FragList. LogicalFrag
,#FragList. IndexName
FROM #fraglist
WHERE LogicalFrag >= @._iMinDefrag
AND INDEXPROPERTY (ObjectId,
IndexName, 'IndexDepth') > 0
ORDER BY LogicalFrag desc
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
+ ', ' + RTRIM(@.IndexName) + ') - fragmentation
currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
if @.frag > 75
select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
(@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
else if @.frag between @.maxfrag AND 75
SELECT @.Cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
(@.objectid) + ', ' + RTRIM(@.indexid) + ')'
EXEC (@.CmdType)
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag,
@.IndexName
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
Don,
My reading of INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 is that
it tells how many levels there are in the index tree (levels above the
leaves). That should not _specifically_ target clustered indexes.
In any case, your use of this property depends on what you are wanting to
do. (I wouldn't use it, personally.)
Russell Fields
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1b6eb01c44fbf$b00964d0$a001280a@.phx.gbl...
> I modified the DBCC INDEXDEFRAG script from SQL Server
> BOL. I added the DBCC DBREINDEX to the script. I used in
> the script listed below if the logical fragmentation is
> greater than 75 then used DBCC DBREINDEX, else then
> between 5 and 75 then use DBCC INDEXDEFRAG.
>
> The script
> INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> eliminates all cluster indexes.
> Should this statement be eliminate from my script?
> I would like to re-index or defragment all indexes in my
> database without using the SQL Server maintenance
> Optimization job.
> Thanks,
> Don
>
> SET NOCOUNT ON
> DECLARE @.tablename VARCHAR (128)
> DECLARE @.execstr VARCHAR (255)
> DECLARE @.objectid INT
> DECLARE @.indexid INT
> DECLARE @.frag DECIMAL
> DECLARE @.IndexName VARCHAR (255)
> DECLARE @.maxfrag DECIMAL
> DECLARE @.CmdType VARCHAR (255)
> -- Decide on the maximum fragmentation to allow
> SELECT @.maxfrag = 5.0
> -- Declare cursor
> DECLARE tables CURSOR FOR
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> -- Create the table
> CREATE TABLE #fraglist (
> ObjectName CHAR (255) NULL,
> ObjectId INT NULL,
> IndexName CHAR (255) NULL,
> IndexId INT NULL,
> Lvl INT NULL,
> CountPages INT NULL,
> CountRows INT NULL,
> MinRecSize INT NULL,
> MaxRecSize INT NULL,
> AvgRecSize INT NULL,
> ForRecCount INT NULL,
> Extents INT NULL,
> ExtentSwitches INT NULL,
> AvgFreeBytes INT NULL,
> AvgPageDensity INT NULL,
> ScanDensity DECIMAL NULL,
> BestCount INT NULL,
> ActualCount INT NULL,
> LogicalFrag DECIMAL NULL,
> ExtentFrag DECIMAL NULL)
> -- Open the cursor
> OPEN tables
> -- Loop through all the tables in the database
> FETCH NEXT
> FROM tables
> INTO @.tablename
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- Do the showcontig of all indexes of the table
> INSERT INTO #fraglist
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> FETCH NEXT
> FROM tables
> INTO @.tablename
> END
> -- Close and deallocate the cursor
> CLOSE tables
> DEALLOCATE tables
> -- Declare cursor for list of indexes to be defragged
> DECLARE indexes CURSOR FOR
> SELECT #FragList. ObjectName
> , #FragList. ObjectId
> , #FragList. IndexId
> , #FragList. LogicalFrag
> , #FragList. IndexName
> FROM #fraglist
> WHERE LogicalFrag >= @._iMinDefrag
> AND INDEXPROPERTY (ObjectId,
> IndexName, 'IndexDepth') > 0
> ORDER BY LogicalFrag desc
> -- Open the cursor
> OPEN indexes
> -- loop through the indexes
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
> + ', ' + RTRIM(@.IndexName) + ') - fragmentation
> currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> if @.frag > 75
> select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
> (@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
> else if @.frag between @.maxfrag AND 75
> SELECT @.Cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
> (@.objectid) + ', ' + RTRIM(@.indexid) + ')'
>
> EXEC (@.CmdType)
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag,
> @.IndexName
> END
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>
|||The INDEXPROPERTY clause I included in the BOL example removes all heaps
from consideration. All indexes (clustered or non-clustered) have depth > 0.
You have to keep that clause in there or you may get errors.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:e$$F$T#TEHA.3872@.TK2MSFTNGP10.phx.gbl...
> Don,
> My reading of INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 is
that
> it tells how many levels there are in the index tree (levels above the
> leaves). That should not _specifically_ target clustered indexes.
> In any case, your use of this property depends on what you are wanting to
> do. (I wouldn't use it, personally.)
> Russell Fields
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:1b6eb01c44fbf$b00964d0$a001280a@.phx.gbl...
>