SQL SERVER 2005 uses ALTER INDEX syntax to reindex database. SQL SERVER 2005 supports DBREINDEX but it will be deprecated in future versions.
When any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. DBCC DBREINDEX statement can be used to rebuild all the indexes on all the tables in 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_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
Reference: Pinal Dave (http://www.SQLAuthority.com)
pinaldave
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 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.
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 .
Why wouldn’t you just use the maintenance plan in SQL and selecting revbuild index? What’s the advantages of running this manually?
Mike,
We’ve got a DB in our software product that needs some maintenance on our cusotmers systems. A few of our customers have databases with tables so large that thier servers cannot reindex fast enough with no impact the normal processing of data.
For us, we have to reindex the individual indexes on a schedule for the large tables. I’m not as familiar with SSIS and setting up maintenance plans, but based on what I know I don’t think you get that level of granularity by using the GUI creating a package, or/and especially by walking through the maintenance plan wizard. Hence, the need for some custom TSQL…
–Robert
Hi,
Was wondering if anyone have had this problem – everytime the server is stop and restarted, this script (method 1) needs to be executed again to get performance back up. When I say performance back up, I mean, we have a stored procedure that returns summary data and after running the method 1 script, the duration it takes to execute drops by 50% and when restarted, duration jumps back up until we execute script. Please help, am stuck and have not had much luck researching on this issue.
NS
Hi
I am using your solution for Reindexing but the problem is whenever I stop and start the SQLServer I need the Reindex to be run again, otherwise I don’t have a good performance on my SQL query.
Does anybody have a solution for it
hi there,
i am running MS SQL 2005, the problem is i always encounter this error
“The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.”
do i need to defragment or reindex the database? how am i going to do that?
is there a way i can reindex/defrag the whole database or do i need to reindex/defrag it per table?
size of database is 120GB – Hard Disk Size is 410GB
Memory used is 9+GB – Memory Size is 26GB
HP Proliant DL380 G5 quad core
we’re using SAP Business One application Patch Version PL45.
Pinal -
I am running SQL 2005 STANDARD – 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). so i decided to DEFRAG index. now my database grown to 110 GB.
1st quest: Trucation–>DEFRAG index–> Full backp (No update statistics yet) after that i am doing hourly Transaction log backup. please suggest me the best way to handle this.
Also i am getting “[SQLSTATE 01000] (Message 4035) A nonrecoverable I/O error occurred on file” on Transaction Log backup. please help.thanks
You rock You Rock You Rock
I work at an air force base and needed this command.
We are trying to search the 66 tables for a record
the structure of the table like below
scenario, seg1, seg2, seg3,seg4, period,qty,amt,price
and all the tables have similar structure .
Unique clustered index was created on each table.
When we try to search a record from 66 tables it was taking approximatley 7 minutes. How to reduce the time to run the query faster.
its a simple query like
We are trying to search the 66 tables for a record
the structure of the table ( sales_fact) like below
scenario, seg1, seg2, seg3,seg4, period,qty,amt,price
and all the tables have similar structure .
Unique clustered index was created on each table.
When we try to search a record from 66 tables it was taking approximatley 7 minutes. How to reduce the time to run the query faster.
its a simple query like
select top 1 from sales_Fact
where seg1 = 111
like above , 66 sql statements should run and each table has got around 10million records.
I appreciate for quick reply
Thanks in advance
Murali
I suppose you can re-index a copy of your online table and then rename the tables so that the new copy is now the online copy. Downtime, but 0 seconds, and renaming objects has drawbacks – in 2000 at least, SQL code attached with the renamed object (create statement, triggers) will contain its original name…
Does Microsoft even make 24/7 products? I think if you need 24/7 availability, you should build it from components that are not required to be 24/7 individually. Switch to a backup server. Do something like RAID hard disks do.
I’m also pursuing how to update statistics with fullscan. I’m not sure if I need it but I want to know how to get it.
Option 1 worked for me.. Thanks Dave
Hi,
Using SQL Server Express 2005
VB.NET 2005 app connecting to SQL database
Option 1 works beautifully for me, but only when I have SQL Server open.
As soon as I close the SQL session, my database performance falls to the floor again.
Any assistance could be greatly appreciated
Steven
Great advice thanks!!!