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
just ran method 1 and it worked a treat, thanks a lot. I’ve relatively little experience in SQL and didnt fancy having to reindex hundreds of tables manually to free up space!
I noticed you’ve got fillfactor of 80 , is this intentional as a general guideline
The recommended fillfactor of an index is 80-90%, depending on eg. the usage of the table.
This is good trick but Microsoft says that this command DBCC DBREINDEX will be removed in there future versions So for this its better to use ALTER INDEX like below.
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
As Pinal sir used the DBCC DBREINDEX command in cusrsor same as we can use this commmand in the cursor.
DBCC DBREINDEX is an off line process. Apart from suggesting DBCC INDEXDEFRAG as a method to deal with online indices, are there any techniques which allow a index to be reindex without going offline
Does anybody know if reindexing a database can cause any performance impact?
We are supporting an application with sql server 2005 as the database. We have a weekly job that reindexes the database. However after the reindexing the performance goes down significantly. We are trying to determine the rootcause of this issue ? Any pointers would be highly appreciated
@Ritesh
Update stats after you have rebuilt indexes – performance will then go back up.
When you rebuild an index, SQL Server updates the index statistics as well. However, reorganize index does not do this. So you don’t have to update stats after a rebuild but you would want to after a reorg. I don’t think either touch column stats though.
I ran the application and it seems to get stuck at some point, perhaps due to a lock. Is there any way to modify the script to:
a. report in real time (no buffering) what table/index it is currently working on
b. report the size in records for the table it is about to start work on
c. avoid or at least indicate if there is a lock active that is blocking the reindex process?
We’re using above mentioned functionality to re-index and update statistic on a weekly based. The database is roughly 30GB. While the re-indexing/update statistic took 3 hours 6 month ago, it just increased to 8.5 to 10 hours over the last 8 weeks without significant change in the amount of data. Any idea what might cause the dramatic increase in processing time? we’re using SQL Server 2005.
Hello,
I am using SQL Server 2000 and I have the following issue:
After running the sp_updatestats the performance decreases too much.
To solve that I ran a index rebuild and stopped running sp_updatestats
I don’t why this is happening, and I could not reproduce this on a non production environment.
Do you have any suggestion?
Thanks in advance.
This script of yours just solved a problem I have been working on at work for about 1 1/2 months. We don’t have a real DBA here, so no one could figure out what was going wrong. I was sometimes thinking about quitting my job over this problem. But, once I ran your script, our database ran as fast as ever. Thank you so much. Your website is the best!
Ritesh,
The fill factor could also be an issue for you. If your table has a lot of writes, you should use a lower fill factor. 80% might work for you. If you do not specify the fill factor, then it is 100% by default. This could could a lot of page splits if there is a lot of IO.
Don’t set the fill factor too low though, because otherwise the query will have to read more pages. If your table is mostly reads, then 100% fill factor would be better. It all depends on how much IO your table has.
Victor, in 2000, sp_updatestats re-calculates the index statistics with the sample rate. This overwrites the full sample that was done with the rebuild index. Don’t update statistics after a rebuild.
In 2005 this is not a problem because sp_updatestats first checks if an update is necessary and skips the update if it’s not needed (this only true with the stored procedure and not the UPDATE STATISTICS command).
hi.
i would like to know if it is possible just to reindex 1 table from the entire database and is there a difference between the scripts for 2000 and 2005?
thanking you in Advance
Gavin
@ Gavin,
Question1 : I dont know if you mean Rebuild indexes ? what do you mean by reindex ?
Well for Rebuild indexes on a single table ?
Yes, you can Rebuild indexes on one table, and you have the option to rebuild one index among many index available on a table in a database.
you would be shocked if I say, there is no way that you can rebuild indexes of all tables in a database, You have to write script if you want to rebuild indexes on database level ( all tables).
script: SQL 2000
use pubs
DBCC DBreindex ( ‘dbo.authors’ ,UPKCL_auidind, 90)
– The above script will only rebuild one index UPKCL_audind on the table authors in pub database.
– If you want to rebuild all indexes on author table, then use below script
use pubs
DBCC DBreindex ( ‘dbo.authors’ ,’ ‘, 90)
Question2 : What do you mean by script in 2000 and script in 2005.
I could not understand the question.
Hope this helps,
Imran.
I love your comments all the times
I want to perform reindexing on my database, can I run the below script on my database…
Will it help for performance improvement?
USE MyDatabase
GO
CREATE PROCEDURE spUtil_ReIndexDatabase_UpdateStats
AS
DECLARE @MyTable VARCHAR(255)
DECLARE myCursor
CURSOR FOR
SELECT 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
Hi Pinal,
I need your guidance. I have just started working as a Associate DBA.I want to know what all the basic things which is needed to do for DAY-To-DAY activities. Being an Associate DBA what all I have to do once I login to my system. I do have the theoretical knowledge but doesn’t have practical. Plzz help me out.
I am responsible for BACK UP, Replication for 24/7 environment
Q:1 What kind of backup strategy I should maintain if the environment is 24/7.
A GOOD ONE.
Thanks a ton! I had been trying to figure out why my queries were so slow when I upgraded to SQL Server 2008 and this solved it. Now it’s running faster than ever.
Thanks, Pinal Dave.
Option 1 works for me .