SQL SERVER – ReIndexing Database Tables and Update Statistics on Tables

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.

SQL SERVER - ReIndexing Database Tables and Update Statistics on Tables reindexdb-800x227

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
GO

Method 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)

, , , , , , ,
Previous Post
SQL SERVER – Shrinking Truncate Log File – Log Full
Next Post
SQL SERVER – Primary Key Constraints and Unique Key Constraints

Related Posts

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)

    Reply
  • 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

    ——————————-

    Reply
  • 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.

    Reply
  • 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

    Reply
    • Exec sp_msForEachTable @COMMAND1= DBCC DBREINDEX (‘?’)”,

      should be

      Exec sp_msForEachTable @COMMAND1= ‘DBCC DBREINDEX (”?”)”,

      Reply
  • Dear madhivanan
    That is not working

    PRASAD S J

    Reply
    • Try this code

      Dim cmd As SqlCommand = New SqlCommand(“Exec sp_msForEachTable @COMMAND1= ‘DBCC DBREINDEX (”?”)'”, con)

      Reply
  • what is update statistics and how it will work

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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

    Reply
  • Does anyone know how long does this task take to run and does it use much CPU / Memory?

    Reply
  • 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]

    Reply
  • 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 ?

    Reply
  • Hello. Are there any updated version of this for MSSQL 2008 R2 ? thank you.

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • Great post. I really needed this.
    Thanks.

    Reply
  • thank you master… great post….. it’s solve my sql server problem’s….

    Reply
  • Werksarztzentrum Germany
    June 14, 2012 2:34 pm

    Very helpful!

    Reply

Leave a Reply

Menu