Home » Programming / Coding

Reindexing Microsoft SQL Databases to Optimize Performance

11 March 2008 14,709 views No Comment

Generally the indexing is done automatically in MS SQL database. If you need to reindex the database for any reason, you can use the tips here.

First of all backup the databse before starting the reindexing. So anything goes wrong, you can get your DB back.

We will use DBCC DBREINDEX, a helpful database maintenance command available for defragmenting indexes in Microsoft SQL Server. DBCC DBREINDEX can be used to rebuild one or more indexes for a specific table.

DBCC DBREINDEX locks the tables as it is operating on, tables would be unavailable to users when this command runs.

Primary Key or Unique constraints are preserved automatically during the rebuild. DBCC DBREINDEX completely rebuilds the indexes, so it restores the page density levels to the original fill factor (default). However, if you prefer, you can choose another target value for the page density. Running DBCC DBREINDEX is similar to using Transact-SQL statements to drop and re-create the indexes manually.

To reindex all the indexes in a Microsoft SQL database, follow these steps:

1. On the Start menu, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.

2. In the Connect to SQL Server dialog box, click OK.

3. On the Query menu, click Change Database.

4. In the Select Database of <ServerName> dialog box, click the Microsoft CRM database that you want to work on, and then click OK.

5. In the Query window, type the following commands:

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘base table’
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ‘Reindexing ‘ + @TableName
DBCC DBREINDEX(@TableName,’ ‘,90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor

6. Click the Execute Query button on the toolbar, and the results will show in the results pane.

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading ... Loading ...

Leave your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.