Reorganize index pages never completes

I used to have this function turned on in our maint plan, running every
Sunday night. For the last two months or so it would never complete, and the
server would be non-responsive Monday morning.
Does anyone know what causes this to happen?
MauryThere are, unfortunately, MANY things that can cause poor indexing
performance. You're going to have to do some analysis. You don't tell
us how large your database is, but obviously the more data you have,
the larger the indexes will be, thus the more work this reindexing
process will have to do. Things to check:
- physical disk fragmentation of your database and log files
- I/O utilization of your disk hardware
- other processes running on the server causing additional load?
- other processes blocking the reindex?
- is your database configured to auto-grow when needed?
- are you shrinking your databases, either with a scheduled job or the
"autoshrink" DB option? You should not shrink a production database
unless you have concerns about disk space. The combination of
shrinking, then growing, then shrinking, then growing, will lead to
severe fragmentation of your database file, causing increasingly poor
performance.
There are other factors that will affect indexing speed, but these are
the common ones.
Maury Markowitz wrote:
> I used to have this function turned on in our maint plan, running every
> Sunday night. For the last two months or so it would never complete, and the
> server would be non-responsive Monday morning.
> Does anyone know what causes this to happen?
> Maury|||"Tracy McKibben" wrote:
> There are, unfortunately, MANY things that can cause poor indexing
> performance.
This isn't a performance issue, although I should have pointed this out the
first time.
If I run a complete reindex by hand it takes perhaps 3 minutes at the worst,
and averages about 1.5 minutes. The delay in question is eight hours. It is
not that it is proceeding slowly, it simply isn't proceeding at all.
However, the system offers no indication whatsoever that there is a problem,
let alone what the problem is.
> - other processes blocking the reindex?
This is the only thing that I think could be the issue. However, it is not
clear to me how one would debug this. There is no indication in any of the
logs I have found that a lock is causing a problem, even when I return to
work Monday and see the system is "frozen".
Is there some way I can guarentee that locks are released? My users often
leave their Access apps open "forever", so it is entirely possible (and not
that uncommon) for there to be locking issues.
> - is your database configured to auto-grow when needed?
Yes.
> - are you shrinking your databases, either with a scheduled job or the
> "autoshrink" DB option? You should not shrink a production database
> unless you have concerns about disk space.
OK, but like I said, this isn't a performance issue. I will turn this off
anyway.
> shrinking, then growing, then shrinking, then growing, will lead to
> severe fragmentation of your database file
The fact that NT allows this has always made me scratch my head.
Maury|||Maury Markowitz wrote:
> This is the only thing that I think could be the issue. However, it is not
> clear to me how one would debug this. There is no indication in any of the
> logs I have found that a lock is causing a problem, even when I return to
> work Monday and see the system is "frozen".
When you leave on Friday, open a Query Analyzer connection to the
server, and leave it open. When you come in on Monday, run "sp_who2"
in that session to see if there is blocking. The reason for leaving
the QA session open on Friday is so that you aren't locked out of SQL.
Also, if you connect to the server with RDP, or can physically access
the console, take a look at Perfmon, look at things like CPU %, Disk
Queue Length, etc., these will tell you if the machine is doing
anything, and what parts of it are "bound up".
Now that you've described things a little more, I really think blocking
is your problem. It could have been the auto-shrink setting, if SQL
decides to shrink the database while you're reindexing, well that would
be bad. Access is also a common thorn in the side for causing DB
problems.|||"Tracy McKibben" wrote:
> When you leave on Friday, open a Query Analyzer connection to the
> server, and leave it open. When you come in on Monday, run "sp_who2"
> in that session to see if there is blocking. The reason for leaving
> the QA session open on Friday is so that you aren't locked out of SQL.
Ok, I'll give this a try.
> Now that you've described things a little more, I really think blocking
> is your problem.
Me too. I also have "random" locks on what appear to be read-only requests,
so this certainly could be the issue here too.
Maury