SQL SERVER 2005 uses ALTER INDEX syntax to reindex database. SQL SERVER 2005 supports DBREINDEX but it will be deprecated in future versions. Let us learn how to do ReIndexing Database Tables and Update Statistics on Tables.
When any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. The DBCC DBREINDEX statement can be used to rebuild all the indexes on all the tables in the database. DBCC DBREINDEX is efficient over dropping and recreating indexes.
Execution of Stored Procedure sp_updatestats at the end of the Indexes process ensures updating stats of the database.
Method 1: My Preference
USE MyDatabase
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GOMethod 2:
USE MyDatabase GO CREATE PROCEDURE spUtil_ReIndexDatabase_UpdateStats AS DECLARE @MyTable VARCHAR(255) DECLARE myCursor CURSOR FOR SELECT '['+TABLE_SCHEMA+'].['+TABLE_NAME+']' FROM information_schema.tables WHERE table_type = 'base table' OPEN myCursor FETCH NEXT FROM myCursor INTO @MyTable WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Reindexing Table: ' + @MyTable DBCC DBREINDEX(@MyTable, '', 80) FETCH NEXT FROM myCursor INTO @MyTable END CLOSE myCursor DEALLOCATE myCursor EXEC sp_updatestats GO
Though this is an old trick, it works pretty well on the most of the system and it gives very good results. I strongly encourage you to try this out if you have performance issue with SQL Server.
Reference: Pinal Dave (http://www.SQLAuthority.com)
111 Comments. Leave new
In VB 2005 the following command shows error, please help
Dim cmd As SqlCommand = New SqlCommand(“Exec sp_msForEachTable @COMMAND1=DBCC DBREINDEX (‘?’)”, con)
What is the error you are getting?
Here’s a sql statement that will reindex all tables in all databases. It skips standby databases and tempdb, you can also add additional dbs to skip if you wish – just mod the NOT IN clause.
——————————-
DECLARE @dbname VARCHAR(50) — database name
DECLARE @cmd VARCHAR(500)
DECLARE db_cursor CURSOR FOR
SELECT name as dbname
FROM master.sys.databases
WHERE name NOT IN (‘tempdb’) and is_in_standby = 0
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = ‘use ‘ + @dbname + ‘; exec sp_MSForEachTable ”DBCC DBREINDEX(””?””,””””,90)”;’
print @cmd
exec (@cmd)
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
——————————-
Sorry should also mention.. the original blog executes sp_updatestats at the end. You dont need to do this.. a reindex actually recreates stats based on 100% of the data while a sp_updatestats will replace the stats based on a small percentage of data. So it is better to not update stats if you have just reindexed.
Dear madhivanan,
Following are my source code and the error i get is “Incorrect syntax near the keyword ‘DBCC’. Please help me.
regards,
Public Overrides Sub GetUIData()
MyBase.GetUIData
If Me.RE_INDEX.SelectedValue = “Yes” Then
Dim con As SqlConnection = New SqlConnection(“Data Source=ACER\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=FAS”)
Dim cmd As SqlCommand = New SqlCommand(“Exec sp_msForEachTable @COMMAND1= DBCC DBREINDEX (‘?’)”, con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Else
Throw New Exception(“Indexing not done!”)
End If
End Sub
Exec sp_msForEachTable @COMMAND1= DBCC DBREINDEX (‘?’)”,
should be
Exec sp_msForEachTable @COMMAND1= ‘DBCC DBREINDEX (”?”)”,
Dear madhivanan
That is not working
PRASAD S J
Try this code
Dim cmd As SqlCommand = New SqlCommand(“Exec sp_msForEachTable @COMMAND1= ‘DBCC DBREINDEX (”?”)'”, con)
what is update statistics and how it will work
I have a very basic question to ask you all, can any one explain the concept of re-indexing, why and how it is related with the performance of database or exactly what makes the db perform faster after re-indexing??
Thanks Sagar
HI,
I am running SQL 2008 database about 40 GB. i need to have the database online 24/7. If i do RE_INDEX slowing down the user (grows 2 GB more). but the user asking no downtime requires. But we need to run the index rebuild in this situation wat will do? kindly advice any one about this.
1. Run the following command. Focus on the “avg_fragmentation_in_percen”, Higher the number on this column, these are table you need to consider rebuilding / reorganize index
SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
GO
2. Once you identfied the index with a higer number on the “avg_fragmentation_in_percen” column
3. Do some reading on Rebuild Index VS Reorganise
I don’t want to give you all the answers, the only way is you finding the best way…. hope the above hints are helpful
how do you determine the table names using the object _ID?
Pinal Dave
You are the man, you really helped. I spend more than two days trying to find out what the problem was with my queries, little did i know that I needed to reindex my database tables and then update the statistics.
You are one great DBA, I must say
Does anyone know how long does this task take to run and does it use much CPU / Memory?
Should this be copied and pasted to the query without editing? I got this message after execution, with no apparent changes made. Should ‘MyDatabase’ be edited to my database name?
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database ‘MyDatabase’. No entry found with that name. Make sure that the name is entered correctly.
[dbo].[Event_Classes]
You need to change MyDatabase to the name of your database.
I am planning to perform a rebuild of indexes every Sunday.
However should I be running,
1) sp_updatestats Mon – Sat
OR
2) reorg index job Mon – Sat
Or
something else you recommen ?
Hello. Are there any updated version of this for MSSQL 2008 R2 ? thank you.
Really good method for reducing dimension of a database which had a lot of insert/delete/update. It’s working in my SQL Server 2008 R2.
One of the Stored Procedures was taking around 17 Minutes to run and now after following the Re-Indexing Method it takes 3 Secs only!!!
Thanks for the article.
hi all
i have 25 Cr record in a table . At the time of select top 10000 record it will take 59 sec . so please tell how i can fast select statement.
Great post. I really needed this.
Thanks.
thank you master… great post….. it’s solve my sql server problem’s….
Very helpful!