Ramblings of a DBA

Tara Kizer
posts - 166, comments - 835, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

DBCC DBREINDEX Stored Procedure - new version

EDIT: This stored procedure has been updated.

Earlier this month, I posted a stored procedure that runs DBCC DBREINDEX for each of the indexes in the user database that is inputted into the sproc.  Well it turns out that the code was incorrect as it was only reindexing one index per table.  Thanks mr_mist for finding this!  This is an updated version of that sproc:

----------------------------------------------------------------------------------------------------
-- OBJECT NAME         : isp_DBCC_DBREINDEX
--
-- AUTHOR               : Tara Duggan
-- DATE     : May 11, 2004
--
-- INPUTS    : @dbName - name of the database
-- OUTPUTS    : None
-- DEPENDENCIES         : None
--
-- DESCRIPTION         : This stored procedure runs DBCC DBREINDEX for each of the indexes in the database.
--
-- EXAMPLES (optional)  : EXEC isp_DBCC_DBREINDEX @dbName = 'GT'
----------------------------------------------------------------------------------------------------
CREATE            PROC isp_DBCC_DBREINDEX
(@dbName SYSNAME)
AS
SET NOCOUNT ON

DECLARE @objName SYSNAME
DECLARE @idxName SYSNAME
DECLARE @SQL NVARCHAR(4000)
DECLARE @ID INT
DECLARE @RowCnt INT

CREATE TABLE ##Indexes
(
 Indexes_ID INT IDENTITY(1, 1) NOT NULL,
 IndexName SYSNAME NOT NULL,
 ObjectName SYSNAME NOT NULL
)

SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, ObjectName) '
SET @SQL = @SQL + 'SELECT i.name, o.name '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i '
SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o '
SET @SQL = @SQL + 'ON i.id = o.id '
SET @SQL = @SQL + 'WHERE indid > 0 AND indid < 255 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND '
SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0'

EXEC sp_executesql @statement = @SQL

SELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectName
FROM ##Indexes
ORDER BY Indexes_ID

SET @RowCnt = @@ROWCOUNT

WHILE @RowCnt <> 0
BEGIN

 SET @SQL = 'DBCC DBREINDEX(''' + @dbName + '.dbo.' + @objName + ''', ' + @idxName + ', 0) WITH NO_INFOMSGS'

 EXEC sp_executesql @statement = @SQL

 SELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectName
 FROM ##Indexes
 WHERE Indexes_ID > @ID
 ORDER BY Indexes_ID
 
 SET @RowCnt = @@ROWCOUNT

END

DROP TABLE ##Indexes

RETURN 0

Print | posted on Friday, July 30, 2004 3:08 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# DBCC INDEXDEFRAG stored procedure - new version

7/30/2004 6:13 PM | Ramblings of a DBA
Gravatar

# DBCC INDEXDEFRAG stored procedure - new version

7/30/2004 6:15 PM | Ramblings of a DBA
Gravatar

# DBCC DBREINDEX stored procedure

7/30/2004 6:27 PM | Ramblings of a DBA
Gravatar

# re: DBCC DBREINDEX Stored Procedure - new version

Hi Tara,

Will you please tell me what are you doing in following statements

SET @SQL = @SQL + 'WHERE indid > 0 AND indid < 255 SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0'

Thanks
Manoj
8/3/2004 7:29 PM | mk_garg20
Gravatar

# re: DBCC DBREINDEX Stored Procedure - new version

Hi Tara,
In sql server 2000 you can create index on views as well. So you should not add in the stored procedure that as well. I think not many people create index on views.

Thanks
Manoj
8/3/2004 8:05 PM | mk_garg20
Gravatar

# re: DBCC DBREINDEX Stored Procedure - new version

The statement is just finding the indexes. You have to exclude statistics and hypothetical indexes. So that's what that code is doing. I haven't yet used indexed views and since this code was developed for the systems that I support, well it reindexes the ones that I care about. Let me know what would need to be changed for indexed views.
8/4/2004 9:23 AM | Tara
Gravatar

# re: DBCC DBREINDEX Stored Procedure - new version

What are statistics and hypothetical indexes?

Are you using following statement to excluse these indexes
SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0'

Thanks
8/4/2004 6:38 PM | mk_garg20
Gravatar

# re: DBCC DBREINDEX Stored Procedure - new version

Maybe i'm missing something, but why are you iterating each index? Why not just iterate your base tables and leave the index param blank. If you leave out the param to DBCC DBREINDEX it will reindex all indexes on that tbale...
<quote>index_name<br/>
Is the name of the index to rebuild. Index names must conform to the rules for identifiers. If index_name is not specified or is specified as ' ', all indexes for the table are rebuilt.
</quote>

In which case all you need to do is iterate from information_schema.tables where Table_Type = 'Base Table'

Far more simpler. Also, since your then iterating by table, it gives you a chance to Update Statistics for that table (if auto update isn't being used).


9/10/2004 8:04 AM | Frank O'Connor
Gravatar

# re: DBCC DBREINDEX Stored Procedure - new version

In the version that I run, I have a WAITFOR DELAY between each index to slow the job down so that the tlog doesn't grow out of control during the maintenance. We backup the tlog every 15 minutes already and if we just had them all run without delays, then the log gets very large quickly. We could still use the WAITFOR DELAY if we did one table at a time, but that would hit too many indexes without a pause between them.
9/10/2004 9:11 AM | Tara
Gravatar

# Database maintenance routines

9/23/2004 6:37 PM | Ramblings of a DBA
Gravatar

# re: DBCC DBREINDEX Stored Procedure - new version

Some of our tables are owned by dbo and some have different owners ...thats just our ERP system
how to change above to use "owner of the table and not the dbo"
11/5/2006 5:38 AM | tracey
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET