Database

Defrag/Rebuild Index on MS SQL DB

Sometimes your index may be defragmanted. It could make your system run slowly. You should rebuild/defrag you index.
But what happend when you have a lot of index ?
Are you want to rebuild it one by one ?
So wasting time huh ? You may use my script to defrag your index on your MS SQL Database


IF EXISTS(SELECT name FROM sysobjects WHERE name=’sp_defragindexes’ AND xtype=’P’ )
DROP PROCEDURE sp_defragindexes
GO

— Create Procedure
CREATE PROCEDURE sp_defragindexes
@DBName varchar(30) = ”
AS
BEGIN
DECLARE @TableID int
DECLARE @IndexName varchar(50)
— List of tables in Database @DBName
DECLARE table_cursor CURSOR FOR
SELECT id
FROM sysobjects
WHERE xtype=’U’
ORDER BY name

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @TableID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE index_cursor cursor FOR
SELECT name
FROM sysindexes
WHERE id = @TableID AND
indid NOT IN (0,255)
OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC INDEXDEFRAG (@DBName,@TableID,@IndexName)
FETCH NEXT FROM index_cursor INTO @IndexName
END
CLOSE index_cursor
DEALLOCATE index_cursor

FETCH NEXT FROM table_cursor INTO @TableID
END
CLOSE table_cursor
DEALLOCATE table_cursor
END

And how to use ?

You may type this script from your Query Analyzer

sp_defragindexes [Database_Name]

Advertisements

Discussion

2 thoughts on “Defrag/Rebuild Index on MS SQL DB

  1. N00b question:

    How do I set this up on my MS-SQL server?

    Posted by theitjuggler | June 26, 2008 12:01:01, 12:01 am
  2. Copy that script to your query analyzer and execute sp_defragindexes [Database_Name]

    Posted by hnawri | June 26, 2008 10:25:51, 10:25 am

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives

Twitter Updates

Error: Twitter did not respond. Please wait a few minutes and refresh this page.

%d bloggers like this: