February 23, 2004 at 8:04 am
Hello all,
I need to diagnose a problem, this Sunday a regular Database Maintenance plan which is supposed to rebuild indexes took exactly 6 hours and 32 minutes. Now that’s a hell lot of time and during all that process users were denied access to those tables. This is a production server(SQL server 2000 Sp3) on which this plan runs on weekly basis. I want to know what caused that plan to run for so long. I know DBCC INDEXDEFRAG doesn’t lock tables but how can I make Database Maintenance plan to run DBCC INDEXDEFRAG instead of DBCC DBREINDEX but more importantly why it took 6 hours. One more thing this plan is scheduled to run at off-peak hours and database is no way huge its just 200 Mb
Thanks all
February 23, 2004 at 9:01 am
When executing xp_sqlmaint there is a limited number of options to use. RebldIdx is the option to rebuild indexes. I don't think you can change it to execute INDEXDEFRAG rather than DBREINDEX. You can try this without a maintenance plan. Write your own reindex job. There are lots of samples in the scripts section. It may be as simple as:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*
** DS_REINDEX.SQL
**
** This script creates a procedure that reindexes
** SQL Server data tables
**
*/
ALTER PROCEDURE DS_REINDEX
AS
SET NOCOUNT on
DECLARE @next char(30)
SELECT @next = ' '
WHILE @next is not null
BEGIN
SELECT @next = MIN(name)
FROM sysobjects
WHERE type = 'U'
AND name > @next
IF @next IS NOT null
BEGIN
EXEC ("dbcc dbreindex (" + @next + ", pk_" + @next + ", 0, sorted_data_reorg)")
END
END
SET NOCOUNT off
You can change this dbreindex to INDEXDEFRAG if you want. By the way I ran this script on a 20G database and it takes about 17 minutes to run. If you want to understand why it took so long. I think you will need to run the job while using Profiler to monitor what is going on.
Francis
February 23, 2004 at 9:05 am
The maintenance plan runs sqlmaint.exe which could hung in your system. We have seen similar issues before.
February 23, 2004 at 12:53 pm
Thanks fhanlon and Allen Cui
I will write a stored procedure and will schedule it as a job. One thing I forgot to mention same plan ran last week and without any trouble. Its hard to believe how could data in one week can cause such a halt, must be some thing else involved too.
Although fhanlon has described it but still any guidelines about how should I architect that particular procedure, should I first estimate which tables require indexing and just re index those or just run through all the tables. And should any care be taken for clustered indexes?
Thanks again
February 23, 2004 at 1:00 pm
I will simply reindex all tables for this small database.
If you want to find out how fragmentation the data and indexes of the specified table, check dbcc showconfig.
February 23, 2004 at 1:17 pm
In the code sample I gave you I assumed you would only reindex the primary key index named pk_tablename. In doing this I assume this is the clustered index. If it is not you may need to specifically find the clustered index. (see sp_helpindex). There are some neat scripts for reindexing in this website. If you want something more complex than my sample scan through some of the scripts for more info.
Francis
February 24, 2004 at 12:20 am
I don't use sqlmaint, but use my own dbreindex-generator. The main reason is that on our ERP-system (24/7) there is always a lock on a certain table.
On our MES-system, I schedule the same job, using indexdefrag, and run dbreindex only when the production machinery are out of service for maintenance (+- 1 day every 8 - 10 weeks).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 24, 2004 at 7:59 am
Hi Mohammad,
if your DB is really just 200 MB, IMHO you can reindex all and don't worry about it much... it is really a small database.
We have a DB of 100+ GB, and are using a procedure that runs daily and reindexes (DBCC INDEXDEFRAG) only those tables that really need it. It takes anywhere from 10 minutes to 1 hour, mostly around 20-30 minutes, and I've often worked during the evening hours when it is running, without any problems. Futher optimization - using sqlmaint - is performed only occasionally, when it is possible to switch the system off for at least 24 hours, and go into DBO Use Only and Simple recovery modes for the time of optimization, to increase the speed. That happens only rarely, once in two or three months.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply