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 INDEXDEFRAG stored procedure - new version

Due to the problem mr_mist found in the DBCC DBREINDEX stored procedure, I have an updated version of the DBCC INDEXDEFRAG sproc as well:

----------------------------------------------------------------------------------------------------
-- OBJECT NAME         : isp_DBCC_INDEXDEFRAG
--
-- AUTHOR               : Tara Duggan
-- DATE     : May 11, 2004
--
-- INPUTS    : @dbName - name of the database
-- OUTPUTS    : None
-- DEPENDENCIES         : None
--
-- DESCRIPTION         : This stored procedure runs DBCC INDEXDEFRAG for each of the indexes in the database.
--
-- EXAMPLES (optional)  : EXEC isp_DBCC_INDEXDEFRAG @dbName = 'GT'
----------------------------------------------------------------------------------------------------
CREATE   PROC isp_DBCC_INDEXDEFRAG
(@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 INDEXDEFRAG(' + @dbname + ',' + @objName + ', ' + @idxName + ') 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

 

Here's a link to the old version.

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

Feedback

Gravatar

# DBCC INDEXDEFRAG stored procedure

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

# re: DBCC INDEXDEFRAG stored procedure - new version

Hi Tara,
I have many indexes on tables in my database. I need to reindex them time to time. Can you tell me what to do for that.

I am using SQL server 7.0.

Thanks
Manoj

8/2/2004 8:22 PM | mk_garg20
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

Hi Tara,
Please ignore my previous comments.
Just checked your isp_DBCC_DBREINDEX stored procedure.

Can i use this for SQL server 7.0 without any change.

Thanks
Manoj
8/2/2004 8:36 PM | mk_garg20
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

I have not tested it on 7.0. I don't have any 7.0 servers available to see if it is backward compatible. I'd suggest running it with PRINT @SQL instead of EXEC to try it out first.
8/3/2004 9:09 AM | Tara
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

;-)

I haven't put it on my weblog yet as I'm not happy with the cursor. As soon as I rewrite it using a WHILE loop instead, I'll post it.
8/4/2004 10:51 AM | Tara
Gravatar

# Database maintenance routines

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

# re: DBCC INDEXDEFRAG stored procedure - new version

I like your proc. I need one like it for Sybase to do update stats and another for dbccs for tables.
I'm not a Sybase developer so I'm looking everywhere for such a procedure.
Thanks! naxos98@hotmail.com
10/6/2004 8:05 PM | BossHogg
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

This is great works on almost all my databases. You are the guru!! If you run it against the northwind database you can see the only problem my team found. It fails against the Order Details table. I think because there seems to be an issue with tables names that contain spaces. Anybody have any ideas on how to fix this? Did anybody else try this against northwind and get the same results?

Very cool..thanks for posting.
10/19/2004 3:52 PM | idigmail
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

Table names with spaces, the horror!

I didn't test this, but:

SET @SQL = 'DBCC INDEXDEFRAG(' + @dbname + ',' + @objName + ', ' + @idxName + ')

change to:

SET @SQL = 'DBCC INDEXDEFRAG(' + @dbname + ',' + '[' + @objName + ']' + ', ' + @idxName + ')

Just need square brackets around object name.
10/19/2004 4:00 PM | Tara
Gravatar

# Incorrect syntax near '.'

5/4/2005 3:14 PM | Richard Dudley
Gravatar

# Incorrect syntax near '.'

5/4/2005 3:15 PM | Richard Dudley
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

The stored procedure works great, though I don't quite understand the code yet.

Can you explain what is meaning of [sysindexes].[status] value here, since I couldn't find any reference about them.
and why the "EXEC sp_executesql @statement = @SQL" was used in the WHILE loop, since it's first EXECUTION should have all the indexes collected.

Thanks
6/16/2005 10:58 AM | Ken
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

Sysindexes.status is used as we don't want to bother with hypothetical indexes and statistics.

The first "EXEC sp_executesql @statement = @SQL" was used to populate the ##Indexes table. The second was used to execute the DBCC DBREINDEX command. The second is done in the loop since we want to repeatedly run DBCC DBREINDEX, number of loops around depends on ##Indexes values.
6/16/2005 11:05 AM | Tara
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

The Previous question about "EXEC sp_executesql" was a mistake.

Can you just explain the status meaning? I couldn't find any reference. Thanks

6/16/2005 11:06 AM | Ken
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

Did my answer help explain why sysindexes.status is used? If not, I can elaborate more.
6/16/2005 11:10 AM | Tara
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

Hello.

Thanks for a great script. Is the anyway this script can have another argument for running just for tables begining with some letters. For ex reindex all table begining with finance*. Why ? I have big databases with tables for different customers and want to spilt up this task.

Regards Bernt H. Jensen
DBA
7/25/2005 5:42 AM | Bernt
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

I have a version of DBCC DBREINDEX that reads a table to see which indexes to build that day. It can easily be modified for INDEXDEFRAG.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



----------------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_DBCC_DBREINDEX
--
-- AUTHOR : Tara Duggan
-- DATE : January 26, 2005
--
-- INPUTS : @Debug - 0, 1; whether to execute the command or display it
-- OUTPUTS : None
-- DEPENDENCIES : None
--
-- DESCRIPTION : This stored procedure runs DBCC DBREINDEX for the tables in the Defrag table per
-- the day of the week.
--
-- EXAMPLES (optional) : EXEC isp_DBCC_DBREINDEX_DayOfWeek
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
--
----------------------------------------------------------------------------------------------------
ALTER PROC isp_DBCC_DBREINDEX_DayOfWeek
(@Debug int = 0)
AS

SET NOCOUNT ON

DECLARE @DayOfWeek int, @i int, @j int, @SQL nvarchar(4000), @dbName sysname, @tblName sysname
DECLARE @Reindex table
(
Reindex_ID int IDENTITY(1, 1) NOT NULL,
DatabaseName sysname NOT NULL,
TableName sysname NOT NULL
)

SELECT @DayOfWeek = DATEPART(dw, GETDATE()), @i = 0

INSERT INTO @Reindex(DatabaseName, TableName)
SELECT DatabaseName, TableName
FROM Reindex
WHERE DayOfWeek = @DayOfWeek
ORDER BY DatabaseName, TableName

SELECT @j = @@ROWCOUNT

IF @j > 0
SET @i = 1

WHILE @i >= 1 AND @i <= @j
BEGIN
SELECT @dbName = DatabaseName, @tblName = TableName
FROM @Reindex
WHERE Reindex_ID = @i

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

IF @Debug = 0
EXEC (@SQL)
ELSE
PRINT @SQL

SET @i = @i + 1
END





GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

7/25/2005 9:38 AM | Tara
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

Hello Again.

Regarding my earlier request I have now solved it.

I have added a new argument which is tablename
-------------------------
CREATE PROCEDURE dbo.isp_DBCC_INDEXDEFRAG
@dbName sysname,
@tablename varchar(100)
AS
SET NOCOUNT ON
---------------
SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0 AND '
SET @SQL = @SQL + 'i.name LIKE ' + ''''+ @tablename + ''''
---------------

Now you can call it like this and it will run for all table starting with : Finance Dep Oslo
dbo.isp2_DBCC_INDEXDEFRAG '[199_FIN01]', 'Finance Dep Oslo%'

Hope you find it useful, if not ignore it:-)

Bernt
DBA, Oslo Norway
sunbernt@hotmail.com
7/31/2005 6:04 PM | Bernt
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

@Ken -- reguarding what the status values relate to.

Take a look at
SELECT * FROM master.dbo.spt_values WHERE type = 'I'

10/12/2005 11:43 AM | Dennis
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

Was on the verge of writing a procedure to defrag all indexes on a particular database when I happened upon this article!

Quotes on the DBCC EXEC line had to be modified to make it work, but an excellent and useful procedure indeed.

Many thanks :-)

12/2/2005 7:00 AM | Paul Hills, SQL Programmer
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

Tara, this is a great procedure. Thanks so much! I did find I had a problem when the object is not owned by dbo. I had to add a join to the sysusers table to get the object owner name to use along with the object name in the DBCC IndexDefrag call.

I enjoy your blog and find many useful tidbits. Keep posting! :-)
12/28/2005 9:41 AM | Stan
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

Did you know this doesn't compile in Query Anaylzer? Cut and paste it...
4/5/2006 5:50 PM | Dana
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

Well you have to reformat it as it didn't post correctly in the blog. Comments got wrapped around to the next line.

So once you reformat it, it does compile and works great.
4/6/2006 8:54 AM | Tara
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

Not exactly:

(@dbName SYSNAME)
...
SET @SQL = 'DBCC INDEXDEFRAG(' + @dbname ...

Note the case difference (@dbName, #@dbname). The server I compiled this on must have a case-sensitive option set (not sure exactly which one, but I seem to recall that as an option).
4/6/2006 3:20 PM | Dana
Gravatar

# Index Fragmentation

4/11/2006 6:34 PM | Ramblings of a DBA
Gravatar

# Tara Duggan - Index Fragmentation

4/18/2006 11:53 AM | Professional Association for SQL
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

hi, can i run this sp with any database in my sql server 2000? just creating a new procedure and pasting your code?
4/28/2006 1:39 PM | jessica
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

Hello Great script... Can you tell me how can this script be modified to run this script on all indexes in one table?
Thanks
-Mohcine
8/14/2006 9:01 AM | Mohcine
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

Figured it out thx guys!
8/16/2006 4:39 PM | Mohcine
Gravatar

# re: DBCC INDEXDEFRAG stored procedure - new version

Hi ,
The new version Defragmentation is working fine and its really useful for dev/db support point of view.
10/16/2006 11:20 PM | Vivekanand
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET