DBCC Reindex suddenly takes 5 times longer than it has

I have a 400 gb database that I reindex once monthly through a
maintenance plan. The reindexing has consistently taken 13hrs for the
last year or so, and this last month it took almost 66hrs.
I haven't had any drastic changes in my DB structure, any massive data
loads or deletions, there were no scheduled jobs at the time of the
reindex (at least not during the time it usually takes), no one is
fessing up to any adhoc queries or data modifications during the normal
reindexing time, and there are no errors reported in either the job
log, or the SLQ error logs.
I have worked a bit with Microsoft PSS on this, but they really can't
do any post mortem on this and essentially wished me luck in a
reduction in my reindexing time next month.
Where else would you look for clues if this were your DB?
I am working on redesigning my reindexing plans and potentially moving
to a dbcc indexdefrag method for the future, but just leaving it at 'I
don't know" isn't sitting well with me or my CIO.
Any ideas?
Meredith Ryan-Smith
My guess is that it was waiting for a lock and there was a query that hadn't
been committed that was holding it. No way to find that out for the last run
but if it happens again, look at sp_lock to see if its blocked.
I'd be happy to help you work out a better fragmentation removal plan (I
wrote DBCC INDEXDEFRAG) - take out the 'nospam' and drop me a line if you're
interested.
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"mryan" <coffegrl@.gmail.com> wrote in message
news:1169149741.244719.118910@.51g2000cwl.googlegro ups.com...
>I have a 400 gb database that I reindex once monthly through a
> maintenance plan. The reindexing has consistently taken 13hrs for the
> last year or so, and this last month it took almost 66hrs.
> I haven't had any drastic changes in my DB structure, any massive data
> loads or deletions, there were no scheduled jobs at the time of the
> reindex (at least not during the time it usually takes), no one is
> fessing up to any adhoc queries or data modifications during the normal
> reindexing time, and there are no errors reported in either the job
> log, or the SLQ error logs.
> I have worked a bit with Microsoft PSS on this, but they really can't
> do any post mortem on this and essentially wished me luck in a
> reduction in my reindexing time next month.
> Where else would you look for clues if this were your DB?
> I am working on redesigning my reindexing plans and potentially moving
> to a dbcc indexdefrag method for the future, but just leaving it at 'I
> don't know" isn't sitting well with me or my CIO.
>
> Any ideas?
> Meredith Ryan-Smith
>
|||Meredith,
Even a 13 hour window is probably too long for a maintenance job anyway.
More than likely, some of your tables do not need reindexing. One approach
is to schedule reindex jobs more often, maybe on weekends, and only reindex
a couple of tables at a time. Use DBCC SHOWCONTIG to see which tables are in
the worst shape, and also which tables might not need re-indexing.
Very definitely, take advantage of Paul's offer to explore defrag. It far
less resuource intensive and in many cases is all you need. And you sure
won't find a better qualified mentor.
-- Bill
"mryan" <coffegrl@.gmail.com> wrote in message
news:1169149741.244719.118910@.51g2000cwl.googlegro ups.com...
>I have a 400 gb database that I reindex once monthly through a
> maintenance plan. The reindexing has consistently taken 13hrs for the
> last year or so, and this last month it took almost 66hrs.
> I haven't had any drastic changes in my DB structure, any massive data
> loads or deletions, there were no scheduled jobs at the time of the
> reindex (at least not during the time it usually takes), no one is
> fessing up to any adhoc queries or data modifications during the normal
> reindexing time, and there are no errors reported in either the job
> log, or the SLQ error logs.
> I have worked a bit with Microsoft PSS on this, but they really can't
> do any post mortem on this and essentially wished me luck in a
> reduction in my reindexing time next month.
> Where else would you look for clues if this were your DB?
> I am working on redesigning my reindexing plans and potentially moving
> to a dbcc indexdefrag method for the future, but just leaving it at 'I
> don't know" isn't sitting well with me or my CIO.
>
> Any ideas?
> Meredith Ryan-Smith
>
|||I am very much looking forward to taking advantage of Paul's offer for
help, and I would agree that 13hrs is too long for a single maintenance
job. I've only recently inherited this system from a DBA that recently
left the company, and I am in the process of slowly discovering all the
little quirks and making changes as needed.
Meredith
AlterEgo wrote:[vbcol=seagreen]
> Meredith,
> Even a 13 hour window is probably too long for a maintenance job anyway.
> More than likely, some of your tables do not need reindexing. One approach
> is to schedule reindex jobs more often, maybe on weekends, and only reindex
> a couple of tables at a time. Use DBCC SHOWCONTIG to see which tables are in
> the worst shape, and also which tables might not need re-indexing.
> Very definitely, take advantage of Paul's offer to explore defrag. It far
> less resuource intensive and in many cases is all you need. And you sure
> won't find a better qualified mentor.
> -- Bill
>
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1169149741.244719.118910@.51g2000cwl.googlegro ups.com...

DBCC Reindex suddenly takes 5 times longer than it has

I have a 400 gb database that I reindex once monthly through a
maintenance plan. The reindexing has consistently taken 13hrs for the
last year or so, and this last month it took almost 66hrs.
I haven't had any drastic changes in my DB structure, any massive data
loads or deletions, there were no scheduled jobs at the time of the
reindex (at least not during the time it usually takes), no one is
fessing up to any adhoc queries or data modifications during the normal
reindexing time, and there are no errors reported in either the job
log, or the SLQ error logs.
I have worked a bit with Microsoft PSS on this, but they really can't
do any post mortem on this and essentially wished me luck in a
reduction in my reindexing time next month.
Where else would you look for clues if this were your DB?
I am working on redesigning my reindexing plans and potentially moving
to a dbcc indexdefrag method for the future, but just leaving it at 'I
don't know" isn't sitting well with me or my CIO.
Any ideas?
Meredith Ryan-SmithMy guess is that it was waiting for a lock and there was a query that hadn't
been committed that was holding it. No way to find that out for the last run
but if it happens again, look at sp_lock to see if its blocked.
I'd be happy to help you work out a better fragmentation removal plan (I
wrote DBCC INDEXDEFRAG) - take out the 'nospam' and drop me a line if you're
interested.
--
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"mryan" <coffegrl@.gmail.com> wrote in message
news:1169149741.244719.118910@.51g2000cwl.googlegroups.com...
>I have a 400 gb database that I reindex once monthly through a
> maintenance plan. The reindexing has consistently taken 13hrs for the
> last year or so, and this last month it took almost 66hrs.
> I haven't had any drastic changes in my DB structure, any massive data
> loads or deletions, there were no scheduled jobs at the time of the
> reindex (at least not during the time it usually takes), no one is
> fessing up to any adhoc queries or data modifications during the normal
> reindexing time, and there are no errors reported in either the job
> log, or the SLQ error logs.
> I have worked a bit with Microsoft PSS on this, but they really can't
> do any post mortem on this and essentially wished me luck in a
> reduction in my reindexing time next month.
> Where else would you look for clues if this were your DB?
> I am working on redesigning my reindexing plans and potentially moving
> to a dbcc indexdefrag method for the future, but just leaving it at 'I
> don't know" isn't sitting well with me or my CIO.
>
> Any ideas?
> Meredith Ryan-Smith
>|||Meredith,
Even a 13 hour window is probably too long for a maintenance job anyway.
More than likely, some of your tables do not need reindexing. One approach
is to schedule reindex jobs more often, maybe on weekends, and only reindex
a couple of tables at a time. Use DBCC SHOWCONTIG to see which tables are in
the worst shape, and also which tables might not need re-indexing.
Very definitely, take advantage of Paul's offer to explore defrag. It far
less resuource intensive and in many cases is all you need. And you sure
won't find a better qualified mentor.
-- Bill
"mryan" <coffegrl@.gmail.com> wrote in message
news:1169149741.244719.118910@.51g2000cwl.googlegroups.com...
>I have a 400 gb database that I reindex once monthly through a
> maintenance plan. The reindexing has consistently taken 13hrs for the
> last year or so, and this last month it took almost 66hrs.
> I haven't had any drastic changes in my DB structure, any massive data
> loads or deletions, there were no scheduled jobs at the time of the
> reindex (at least not during the time it usually takes), no one is
> fessing up to any adhoc queries or data modifications during the normal
> reindexing time, and there are no errors reported in either the job
> log, or the SLQ error logs.
> I have worked a bit with Microsoft PSS on this, but they really can't
> do any post mortem on this and essentially wished me luck in a
> reduction in my reindexing time next month.
> Where else would you look for clues if this were your DB?
> I am working on redesigning my reindexing plans and potentially moving
> to a dbcc indexdefrag method for the future, but just leaving it at 'I
> don't know" isn't sitting well with me or my CIO.
>
> Any ideas?
> Meredith Ryan-Smith
>|||I am very much looking forward to taking advantage of Paul's offer for
help, and I would agree that 13hrs is too long for a single maintenance
job. I've only recently inherited this system from a DBA that recently
left the company, and I am in the process of slowly discovering all the
little quirks and making changes as needed.
Meredith
AlterEgo wrote:
> Meredith,
> Even a 13 hour window is probably too long for a maintenance job anyway.
> More than likely, some of your tables do not need reindexing. One approach
> is to schedule reindex jobs more often, maybe on weekends, and only reindex
> a couple of tables at a time. Use DBCC SHOWCONTIG to see which tables are in
> the worst shape, and also which tables might not need re-indexing.
> Very definitely, take advantage of Paul's offer to explore defrag. It far
> less resuource intensive and in many cases is all you need. And you sure
> won't find a better qualified mentor.
> -- Bill
>
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1169149741.244719.118910@.51g2000cwl.googlegroups.com...
> >I have a 400 gb database that I reindex once monthly through a
> > maintenance plan. The reindexing has consistently taken 13hrs for the
> > last year or so, and this last month it took almost 66hrs.
> > I haven't had any drastic changes in my DB structure, any massive data
> > loads or deletions, there were no scheduled jobs at the time of the
> > reindex (at least not during the time it usually takes), no one is
> > fessing up to any adhoc queries or data modifications during the normal
> > reindexing time, and there are no errors reported in either the job
> > log, or the SLQ error logs.
> > I have worked a bit with Microsoft PSS on this, but they really can't
> > do any post mortem on this and essentially wished me luck in a
> > reduction in my reindexing time next month.
> >
> > Where else would you look for clues if this were your DB?
> > I am working on redesigning my reindexing plans and potentially moving
> > to a dbcc indexdefrag method for the future, but just leaving it at 'I
> > don't know" isn't sitting well with me or my CIO.
> >
> >
> > Any ideas?
> >
> > Meredith Ryan-Smith
> >

DBCC Reindex suddenly takes 5 times longer than it has

I have a 400 gb database that I reindex once monthly through a
maintenance plan. The reindexing has consistently taken 13hrs for the
last year or so, and this last month it took almost 66hrs.
I haven't had any drastic changes in my DB structure, any massive data
loads or deletions, there were no scheduled jobs at the time of the
reindex (at least not during the time it usually takes), no one is
fessing up to any adhoc queries or data modifications during the normal
reindexing time, and there are no errors reported in either the job
log, or the SLQ error logs.
I have worked a bit with Microsoft PSS on this, but they really can't
do any post mortem on this and essentially wished me luck in a
reduction in my reindexing time next month.
Where else would you look for clues if this were your DB?
I am working on redesigning my reindexing plans and potentially moving
to a dbcc indexdefrag method for the future, but just leaving it at 'I
don't know" isn't sitting well with me or my CIO.
Any ideas?
Meredith Ryan-SmithMy guess is that it was waiting for a lock and there was a query that hadn't
been committed that was holding it. No way to find that out for the last run
but if it happens again, look at sp_lock to see if its blocked.
I'd be happy to help you work out a better fragmentation removal plan (I
wrote DBCC INDEXDEFRAG) - take out the 'nospam' and drop me a line if you're
interested.
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"mryan" <coffegrl@.gmail.com> wrote in message
news:1169149741.244719.118910@.51g2000cwl.googlegroups.com...
>I have a 400 gb database that I reindex once monthly through a
> maintenance plan. The reindexing has consistently taken 13hrs for the
> last year or so, and this last month it took almost 66hrs.
> I haven't had any drastic changes in my DB structure, any massive data
> loads or deletions, there were no scheduled jobs at the time of the
> reindex (at least not during the time it usually takes), no one is
> fessing up to any adhoc queries or data modifications during the normal
> reindexing time, and there are no errors reported in either the job
> log, or the SLQ error logs.
> I have worked a bit with Microsoft PSS on this, but they really can't
> do any post mortem on this and essentially wished me luck in a
> reduction in my reindexing time next month.
> Where else would you look for clues if this were your DB?
> I am working on redesigning my reindexing plans and potentially moving
> to a dbcc indexdefrag method for the future, but just leaving it at 'I
> don't know" isn't sitting well with me or my CIO.
>
> Any ideas?
> Meredith Ryan-Smith
>|||Meredith,
Even a 13 hour window is probably too long for a maintenance job anyway.
More than likely, some of your tables do not need reindexing. One approach
is to schedule reindex jobs more often, maybe on weekends, and only reindex
a couple of tables at a time. Use DBCC SHOWCONTIG to see which tables are in
the worst shape, and also which tables might not need re-indexing.
Very definitely, take advantage of Paul's offer to explore defrag. It far
less resuource intensive and in many cases is all you need. And you sure
won't find a better qualified mentor.
-- Bill
"mryan" <coffegrl@.gmail.com> wrote in message
news:1169149741.244719.118910@.51g2000cwl.googlegroups.com...
>I have a 400 gb database that I reindex once monthly through a
> maintenance plan. The reindexing has consistently taken 13hrs for the
> last year or so, and this last month it took almost 66hrs.
> I haven't had any drastic changes in my DB structure, any massive data
> loads or deletions, there were no scheduled jobs at the time of the
> reindex (at least not during the time it usually takes), no one is
> fessing up to any adhoc queries or data modifications during the normal
> reindexing time, and there are no errors reported in either the job
> log, or the SLQ error logs.
> I have worked a bit with Microsoft PSS on this, but they really can't
> do any post mortem on this and essentially wished me luck in a
> reduction in my reindexing time next month.
> Where else would you look for clues if this were your DB?
> I am working on redesigning my reindexing plans and potentially moving
> to a dbcc indexdefrag method for the future, but just leaving it at 'I
> don't know" isn't sitting well with me or my CIO.
>
> Any ideas?
> Meredith Ryan-Smith
>|||I am very much looking forward to taking advantage of Paul's offer for
help, and I would agree that 13hrs is too long for a single maintenance
job. I've only recently inherited this system from a DBA that recently
left the company, and I am in the process of slowly discovering all the
little quirks and making changes as needed.
Meredith
AlterEgo wrote:[vbcol=seagreen]
> Meredith,
> Even a 13 hour window is probably too long for a maintenance job anyway.
> More than likely, some of your tables do not need reindexing. One approach
> is to schedule reindex jobs more often, maybe on weekends, and only reinde
x
> a couple of tables at a time. Use DBCC SHOWCONTIG to see which tables are
in
> the worst shape, and also which tables might not need re-indexing.
> Very definitely, take advantage of Paul's offer to explore defrag. It far
> less resuource intensive and in many cases is all you need. And you sure
> won't find a better qualified mentor.
> -- Bill
>
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1169149741.244719.118910@.51g2000cwl.googlegroups.com...sql

DBCC DBREINDEX Status

I have inherited a SQL SERVER 2000 project from a colleague and we have a monthly SQL SERVER job that reindexes all the indexes in a reporting table (it contains archived records up to 1 year old, averaging close to 15 million rows). This table receives fresh data from another table in a different database every night (close to 30000 rows).

Since there is a monthly job that rebuilds all the indexes, there will be a single day every month when the rebuilding of indexes will clash with insertion of those new rows. I haven't been able to find a specific TSQL construct that I can use in my INSERT stored procedure to see if the table is currently locked for rebuilding indexes before trying to insert the rows. Specifically, I wouldn't insert the rows that night because the table is being reindexed.

Please let me know if there is such a facility in Server 2000 to find out the current indexing status. Also, I wouldn't mind alternate solutions to the above problem.

Thanks in advance!
Your ETL Job could check to see if the re-indexing Job is running, and if so, have the ETL job take a pass for an hour [ WAITFOR DELAY '001:00:00' ], then check again, etc.|||

Sorry to ask the obvious, but how to check if the job is running? Specifically, from within SQL Server and outside SQL Server? I guess at this point in time I am just curious to find out both alternatives.

Thanks for the reply!

|||

With SQL 2005, you could use sp_helpjobactivity.

However, with SQL 2000, it is a bit more trouble. You can check the msdb.dbo.sysjobhistory, column run_status, looking for run_status = 4 (in progress).

|||

Great tip!

OK, here is a more reliability-related question... I have never dealt with the SQL Server Job Agent (believe me, I have complete faith in SQL Server 2005, but this is 2000's job agent), but I am hoping to find out how reliable the Agent is in 2000? I only bring up this question because you mentioned in your previous post that "with SQL Server 2000, it is a bit more trouble." Of course looking at the schema of that table (sysjobhistory), this doesn't seem to be too hard!

Thanks again for the quick replies, Arnie!

|||

My experience with SQL 2000 SQL Agent is that it is very reliable.

However, sometimes the JobHistory table seems to have some latency. If a Job 'should' be running, and the run_status <> 4, I will also cross check a the start_time against the end_time. Keep in mind that an entry is added to the JobHistory table when the Job starts, and if the end_time is NULL, then it has not finished.

|||

Alternatively you could use the stored procedure 'sp_help_job' to determine the job's status.

See here for more info:

http://www.databasejournal.com/features/mssql/article.php/10894_3491201_2

...or here (note that this link refers to SQL Server 7 but the syntax should still apply to SQL Server 2000):

http://doc.ddart.net/mssql/sql70/sp_help_27.htm

Chris

|||Thanks, Chris!