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
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!!!
Hi,
I have created the reindexing job from 1st option of reindexing.
After executing the job I am getting the Job failure alert when I check with the steps Its completed successfully.
We always get result like this
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I believe jaob is failing due to above alert.
When I go with second option of reindexing which is through procedure.
Output is coming
Command completed succesfully.
So we can assume Reindexing have been completed on all the tables or Is there any way to know about reindexing status whether it is completed or not…
Second question is above two method is also applicable for SQL 2000.
Thanks in advance..
Hi,
What is the difference between both Methods.
sp_MSforeachtable is described in many places as an “undocumented stored procedure”, which to me means they don’t tell you if there are bugs in it, you’re not even supposed to know it’s there. A phantom error message could be that kind of a bug, I think, whether or not there’s a real error.
If you’re anxious, this may be a prudent test for problems in your database: DBCC CHECKDB() WITH DATA_PURITY
(The WITH part is only for SQL Server 2005 and for databases where it doesn’t run by default, including databases that were created on SQL Server 2000, and if it runs without errors it becomes the default for that database. It can also be reversed.)
Incidentally, databases from 2000 also really need DBCC UPDATEUSAGE done. It may also ¤ix performance problems. I forget whether I read that in Microsoft documentation, but it’s genuine although apparently not written prominently enough in upgrade documentation for the colleague who upgraded some of our servers to be aware of it.
Otherwise, both versions do basically the same thing: repeatedly construct and execute a string containing a SQL statement referring to each table (user table?) in a database in turn – table1, table2, table3.
If you don’t frequently add or remove tables in your database, you can use a similar design to construct and print out a command for each table in a database, and make a stored procedure containing all the commands. That means you won’t be doing the fancy stuff during your maintenance process, only the actual maintenance commands.
Of course when I say “¤ix” I mean “fix”. It’s this keybo¼rd.
I wonder what actual error can occur in this process? Disk fills up, maybe.
In method 2, instead of a PRINT statement you could do something to log the fact that the command is about to be run, a table somewhere is the obvious idea, and immediately after running it, capture and store the error state – to a variable is a good way. Something like this:
DECLARE @returnerror int, @start datetime;
SET @start = GETDATEUTC();
INSERT LogOfReindexesByTable (tableName, startTime)
VALUES (@MyTable, @start);
DBCC DBREINDEX(@MyTable, ”, 80);
SET @returnerror = @@ERROR;
UPDATE LogOfReindexesByTable
SET endTime = GETDATEUTC(), errorCode = @returnerror
WHERE ( tableName = @MyTable AND startTime = @start );
In SQL Server 2005 you can use an ALTER TABLE statement for this operation instead of DBCC.
Thanks. Option 1 works… &(‘-‘)&
Hi, sorry for my english!
do you know why the update statistic task has past from 10 min to more than 33 min from one day to another:
monday: 10min
Tuesday: 10min
Wednesday: 33min
Thursday: 33min
Friday: 33 min
Thanks
Sylvain Dionne