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
> >