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
Nice Post
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.
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
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.
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…
nobody for my error???
lovin this blog.
Tnx for you support.
Very Nice Pinal…
Your Ideas are very useful …
Thnx ….
Our slow query went from 40 secs to 1 sec !! Thanks !!
Thanks Dave! Our database now is like a rocket!
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?
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.
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?
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.
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
updating the statistics is to correct the information which is about the number of rows and data distribution in the table/Index
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.
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.
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.
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.
Thank you, your suggestion is great and I have updated the blog post according to the same.
Is method 1 still tried and true using SQL 2014?
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