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)

Best Practices, SQL Constraint and Keys, SQL Cursor, SQL Index, SQL Joins, SQL Scripts, SQL Server DBCC, SQL Stored Procedure
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

  • Nice Post

    Reply
  • Hi Pinal,
    I am using SQL server 2000 database and size of the table is 128 GB. We ask our team to done DBCC reindex on one table size 128 GB with 30 Index. We started this process 20 hrs. before but it is still running also we are not getting any idea whether we need kill it or cancel it. We want to resume our operation before 24 hrs. and not sure how much indexing has been completed on this database table.

    Please respond ASAP.

    Reply
  • Hi pinaldave,
    i’ve scheduled a sql2005job with only one step that is:

    USE eusebi
    GO
    EXEC sp_MSforeachtable@command1=”print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)”
    GO
    EXEC sp_updatestats
    GO

    the story starts regularly but i obtain for the first line the error:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘=’.

    why???

    tnx
    d_borghi

    Reply
    • Remove and re-add the ” (quotes) char.
      The quotes char was replaced by your copy/paste to a “nice quote”. just delete it and add it from your keyboard.

      Reply
  • Balakrishna.B
    March 28, 2013 7:43 pm

    Hi Pinal,

    The above command will run one table competes the rebuild and then the other table or all the tables at a time or index by index of the with in the table.

    Thanks
    Bala…

    Reply
  • lovin this blog.
    Tnx for you support.

    Reply
  • Very Nice Pinal…

    Your Ideas are very useful …

    Thnx ….

    Reply
  • Our slow query went from 40 secs to 1 sec !! Thanks !!

    Reply
  • Marcelo Minholi
    October 29, 2013 10:28 pm

    Thanks Dave! Our database now is like a rocket!

    Reply
  • hi,
    I have a sql 2005 database about 280gb but only have 179gb free and I need to reindex. is this enough space for it or do it require additional space?

    Reply
    • Depends on the size of the indices. But is sounds risky to me.
      You need to figure out the total disc space used by the index that uses the most disk space. If I did not have triple that total of free disk space I would not try re-indexing an index.

      Reply
  • Hey Hi Pinal After running the method 1 , it worked but i noticed that a similar file to mdf with size its equivalent has got created, what i see its like 40 GB and extension of that file is abc_1.L.. I am not sure what is this? Can someone advice please?

    Reply
  • Sir What do you mean by updating the statistics at the end after we run dbcc dbreindex.
    what actually Sql server does after we run dbcc sp_updatestatistics. What is done inside. What is the use of updating statistics.

    Reply
  • Sir What do you mean by updating the statistics at the end after we run dbcc dbreindex.
    what actually Sql server does after we run dbcc sp_updatestatistics. What is done inside. What is the use of updating statistics.Thanks

    Reply
    • updating the statistics is to correct the information which is about the number of rows and data distribution in the table/Index

      Reply
      • But Dave, doesn’t a reindex also update stats? Isn’t Update Stats only required after reorg? I am actually running update stats by itself once a week though auto update stats is on, it’s likely not required.

      • Re-indexing does that. It updates the stats related to index, not user created and auto stats.

  • Naveen Shetty
    April 26, 2015 2:23 pm

    Hi Pinal,
    i am no DBA, but we have a schedule that rebuilds index on a weekly basis which runs on a weekend as there is no activity . But the last run got stuck . usually it takes 3 hrs ut this one was showing status as executing for 2 days.Any reasons for it . Since it happening at night , nobody is accessing the database. The only other activity we did was to have a full backup of the system using acronis. The users were finding the system very slow after that so we stop the job and restarted the machine . Now it appears fine. we are planning to start the scehdule again for the next week.

    Reply
    • Rebuild index is known to do a lots of IO. If this happens again, you need to look at waits which are happening for rebuild index task.

      Reply
  • Hi Pinal,
    Thanks for this article. It’s great.

    Just one suggestion…
    Your “method 2” stored proc causes an error if tables are not in the default (dbo) schema. I suggest you modify for cursor declaration in method to as follows:

    DECLARE myCursor CURSOR FOR
    SELECT ‘[‘+TABLE_SCHEMA+’].[‘+TABLE_NAME+’]’
    FROM information_schema.tables
    WHERE table_type = ‘base table’

    Thanks.

    Reply
  • Is method 1 still tried and true using SQL 2014?

    Reply
  • Hi Pinal,

    I have the Stored_Procedure in one db by name PROC and all my source tables in different DB by name SOURCE and my output model in a different DB, DEST. Where exactly should I be running the Reindex and Update_Stats? The SP is running perfectly fine on Dev environment but running too slow on Prod.

    Thanks in Advance,
    Mohan

    Reply

Leave a Reply