can dbcc indexdefrag reindex the whole database in one time?

Hi,
Normally i run the DBCC INDEXDEFRAG one table in one time, can run
all the tables in one time?
Thank you very much!
Best regards,
Florence
Hi,
This gives DBCC DBREINDEX. You can use this during off-peak hrs, as this is
not an online re-indexing operation. If you still want to use DBCC
INDEXDEFRAG, look at the usage of the same in BOL, and replace it with DBCC
DBREINDEX in given script.
/* Re-indexes the specified database */
CREATE PROCEDURE scr_defrag_database
@.dbname nvarchar(256)
AS
BEGIN
-- Quote the database name with brackets
DECLARE @.quoteddbname nvarchar(256)
SET @.quoteddbname = quotename( @.dbname )
-- The outer EXEC is so we can do USE, not allowed in stored procs
-- The inner EXEC does the actual reindex on each table in the
-- specified database
EXEC('
USE '+ @.quoteddbname +'
DECLARE @.stablename sysname
DECLARE PKMS_Tables CURSOR LOCAL FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'' ORDER BY 1
OPEN PKMS_Tables
FETCH NEXT FROM PKMS_Tables INTO @.stablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.stablename = quotename(@.stablename, ''[]'')
EXEC('' DBCC DBREINDEX ( ''+@.stablename+'') WITH NO_INFOMSGS'')
FETCH NEXT FROM PKMS_Tables INTO @.stablename
END
CLOSE PKMS_Tables')
END
Thanks
Yogish
|||CREATE PROC ukug2_op_defrag
@.is_reindex char(1) = 'Y'
AS
BEGIN
SET NOCOUNT ON
-- Basis of script taken from BOL
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
DECLARE tables_cur CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
OPEN tables_cur
DECLARE @.ObjectName sysname
FETCH NEXT FROM tables_cur INTO @.ObjectName
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC( 'DBCC UPDATEUSAGE( 0, [' + @.ObjectName + '] ) WITH COUNT_ROWS,
NO_INFOMSGS' )
INSERT #fraglist
EXEC ( 'DBCC SHOWCONTIG( [' + @.ObjectName + '] ) WITH
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' )
FETCH NEXT FROM tables_cur INTO @.ObjectName
END
CLOSE tables_cur
DEALLOCATE tables_cur
SELECT *
FROM #fraglist fo
WHERE LogicalFrag > 30 -- % fragmentation
AND IndexID BETWEEN 1 AND 254
AND ( IndexID = 1
OR NOT EXISTS (
SELECT * -- If clustered index to defrag then don't do
anything else (no point).
FROM #fraglist fi
WHERE fi.ObjectId = fo.ObjectId
AND fi.IndexID = 1
)
)
IF @.is_reindex = 'Y'
BEGIN
DECLARE defrag_cur CURSOR FOR
SELECT ObjectName,
IndexName
FROM #fraglist fo
WHERE LogicalFrag > 30 -- % fragmentation
AND IndexID BETWEEN 1 AND 254
AND ( IndexID = 1
OR NOT EXISTS (
SELECT * -- If clustered index to defrag then
don't do anything else (no point).
FROM #fraglist fi
WHERE fi.ObjectId = fo.ObjectId
AND fi.IndexID = 1
)
)
DECLARE @.IndexName sysname
DECLARE @.sql varchar(1000)
OPEN defrag_cur
FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Processing dbreindex on ' + RTRIM( @.ObjectName ) + ',
indexname=' + @.IndexName
SET @.sql = 'DBCC DBREINDEX( ''' + RTRIM( @.ObjectName ) + ''', '
+ @.IndexName + ')'
PRINT @.sql
EXEC( @.sql )
PRINT 'Complete.'
FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
END
DEALLOCATE defrag_cur
END
DROP TABLE #fraglist
END
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
|||This is a very timely thread for me as I'm just getting ready to implement a
SP that is based on the BOL sp.
I guess I'm confused by this a bit. From what I read in BOL in SQL 2000
DBCC INDEXDEFRAG will defragment clustered indexes. The SP you created from
the BOL base is a bit different and I guess I just want to know if the one
if BOL is incorrect or just different.
It determines the indexes to defrag based on the results of:
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
if you wanted to exclude clustered indexes I suppose you would be able to
modify to:
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
FROM jdit_fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
AND INDEXPROPERTY (ObjectId, IndexName, 'IsClustered') = 0
Thanks
Ivan
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
news:%23xN31fA9EHA.2112@.TK2MSFTNGP14.phx.gbl...
> CREATE PROC ukug2_op_defrag
> @.is_reindex char(1) = 'Y'
> AS
> BEGIN
> SET NOCOUNT ON
> -- Basis of script taken from BOL
> CREATE TABLE #fraglist (
> ObjectName CHAR (255),
> ObjectId INT,
> IndexName CHAR (255),
> IndexId INT,
> Lvl INT,
> CountPages INT,
> CountRows INT,
> MinRecSize INT,
> MaxRecSize INT,
> AvgRecSize INT,
> ForRecCount INT,
> Extents INT,
> ExtentSwitches INT,
> AvgFreeBytes INT,
> AvgPageDensity INT,
> ScanDensity DECIMAL,
> BestCount INT,
> ActualCount INT,
> LogicalFrag DECIMAL,
> ExtentFrag DECIMAL)
> DECLARE tables_cur CURSOR FOR
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> ORDER BY TABLE_NAME
> OPEN tables_cur
> DECLARE @.ObjectName sysname
> FETCH NEXT FROM tables_cur INTO @.ObjectName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC( 'DBCC UPDATEUSAGE( 0, [' + @.ObjectName + '] ) WITH
> COUNT_ROWS, NO_INFOMSGS' )
> INSERT #fraglist
> EXEC ( 'DBCC SHOWCONTIG( [' + @.ObjectName + '] ) WITH
> TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' )
> FETCH NEXT FROM tables_cur INTO @.ObjectName
> END
> CLOSE tables_cur
> DEALLOCATE tables_cur
> SELECT *
> FROM #fraglist fo
> WHERE LogicalFrag > 30 -- % fragmentation
> AND IndexID BETWEEN 1 AND 254
> AND ( IndexID = 1
> OR NOT EXISTS (
> SELECT * -- If clustered index to defrag then don't do
> anything else (no point).
> FROM #fraglist fi
> WHERE fi.ObjectId = fo.ObjectId
> AND fi.IndexID = 1
> )
> )
> IF @.is_reindex = 'Y'
> BEGIN
> DECLARE defrag_cur CURSOR FOR
> SELECT ObjectName,
> IndexName
> FROM #fraglist fo
> WHERE LogicalFrag > 30 -- % fragmentation
> AND IndexID BETWEEN 1 AND 254
> AND ( IndexID = 1
> OR NOT EXISTS (
> SELECT * -- If clustered index to defrag then
> don't do anything else (no point).
> FROM #fraglist fi
> WHERE fi.ObjectId = fo.ObjectId
> AND fi.IndexID = 1
> )
> )
> DECLARE @.IndexName sysname
> DECLARE @.sql varchar(1000)
> OPEN defrag_cur
> FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Processing dbreindex on ' + RTRIM( @.ObjectName ) + ',
> indexname=' + @.IndexName
> SET @.sql = 'DBCC DBREINDEX( ''' + RTRIM( @.ObjectName ) + ''', '
> + @.IndexName + ')'
> PRINT @.sql
> EXEC( @.sql )
> PRINT 'Complete.'
> FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
> END
> DEALLOCATE defrag_cur
> END
> DROP TABLE #fraglist
> END
>
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
|||Boy, I wish I could recall messages...I think I answered my own
question--this is for DBREINDEX, not INDEXDEFRAG.
"Ivan J. Lee" <ivan.lee@.jdinetsystems.com> wrote in message
news:uU4xUfB9EHA.2900@.TK2MSFTNGP09.phx.gbl...
> This is a very timely thread for me as I'm just getting ready to implement
> a SP that is based on the BOL sp.
> I guess I'm confused by this a bit. From what I read in BOL in SQL 2000
> DBCC INDEXDEFRAG will defragment clustered indexes. The SP you created
> from the BOL base is a bit different and I guess I just want to know if
> the one if BOL is incorrect or just different.
> It determines the indexes to defrag based on the results of:
> SELECT ObjectName, ObjectId, IndexId, LogicalFrag
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> if you wanted to exclude clustered indexes I suppose you would be able to
> modify to:
> SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
> FROM jdit_fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> AND INDEXPROPERTY (ObjectId, IndexName, 'IsClustered') = 0
> Thanks
> Ivan
> "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> news:%23xN31fA9EHA.2112@.TK2MSFTNGP14.phx.gbl...
>
|||Seems like a bit of confusion here. DBCC INDEXDEFRAG can defragment
clustered and non-clustered indexes. DBCC DBREINDEX can rebuild clustered
and non-clustered indexes. The example scripts in this thread are taken from
Example E in BOL for DBCC SHOWCONTIG - look there for more details.
You should read the whitepaper below on fragmentation and when you actually
need to do anything about it (don't just blindly defrag/rebuild everything
in your database - you're wasting resources).
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ivan J. Lee" <ivan.lee@.jdinetsystems.com> wrote in message
news:O3CQZhB9EHA.1228@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Boy, I wish I could recall messages...I think I answered my own
> question--this is for DBREINDEX, not INDEXDEFRAG.
> "Ivan J. Lee" <ivan.lee@.jdinetsystems.com> wrote in message
> news:uU4xUfB9EHA.2900@.TK2MSFTNGP09.phx.gbl...
implement[vbcol=seagreen]
to[vbcol=seagreen]
do[vbcol=seagreen]
''',
>