| Author |
Topic |
|
shiloh
Starting Member
48 Posts |
 Posted - 2007-11-11 : 17:14:00
|
We have a create index script as part of regular index maintenance. The index script is as follows:CREATE INDEX .... ON ..WITH SORT_IN_TEMPDB, DROP_EXISTING, FILLFACTOR = 90, STATISTICS_NORECOMPUTE ON ... Usually this index gets created in under 5 mins. Last night it ran for over 3 hrs and our monitoring team had to cancel it. Then tried the script again couple of times and each time they had to cancel it after 2 hrs. They tried the same script in our DR site and it finished in 3 mins, same table size. This morning, I dropped the index first with DROP INDEX. then re-ran the CREATE INDEX and removed the DROP_EXISTING and it finished in 35 mins. Not the best case but atleast the index got created and our application is back online to users. Now am wondering why CREATE INDEX ...WITH DROP_EXISTING would not finish even in 2-3 hrs..it used to work fine earlier as the same job is run every week. There were no other jobs going on at that time. server was totally idle. Yet this job wouldnt finish. Dropping the index explicitly and recreating though crawled for 36 mins still finished. Any ideas on the strange behaviour?Thanks,don |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-11 : 17:51:15
|
| Is your database on Auto-Grow at 10% or on auto shrink or anything that would slow up the time? Did the log show any errors?Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
shiloh
Starting Member
48 Posts |
Posted - 2007-11-11 : 17:53:32
|
| Yes, the db is on auto growth @ 10%. The same index finishes in 3 mins on the same table in DR site..I looked at sp_who2 and the CPU cycles and Disk IO are increasing so its doing something...just not sure why its taking so long..and nothing in the log.. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-11 : 18:00:58
|
| Grow by 10% isn't the best idea. Set it to 100 MB or something more fixed. As it cycles, it has to first figure out how much 10% is, then add that space, and repeat during the operation. Especially since it keeps the old index while dropping it and recreating, so if the index taxes XX amount of space, it needs to add XX amount of space before creating the new index (and then cleans up once done).Since you are reindexing the table, it is safe to assume that some type of either large or periodic update has happened, which also increases the size of the DB and the log file. Something else is likely going on though. How long did it "usually run in 5 minutes" before this happened? How often is the index being recreated..and is it a separate job. If it is scheduled, could it possibly be overlapping with operations which are not yet complete?Poor planning on your part does not constitute an emergency on my part. |
|
|
|
shiloh
Starting Member
48 Posts |
Posted - 2007-11-11 : 18:15:05
|
| There are 3 more indexes on the same table including a clustered index. The clustered index and other 2 indexes finished in a few mins and this one got stuck. The same job was run last week with no issues. The table increased in size a little but the same script ran in a few mins in the DR site..It is part of a step (the last step). Usually the monitoring team runs jobs in parallel. when they got stuck with this job, the stopped the job after 3 hrs.. let other jobs finish and re-ran this step after all other maintenance was done. So the server was pretty much idle. And still, the job (the particular step) wouldnt finish after 3 hrs. So they copied the script into Query analyzer and tried it again only to stop it after an hr. Other indexes on the same table finished quickly. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-11 : 20:14:13
|
| Maybe blocking in the table. |
|
|
|
shiloh
Starting Member
48 Posts |
Posted - 2007-11-11 : 21:26:01
|
| Nope..nothing else is going on on the server when the script was being run from query analyzer..I just wanted to try something different and dropped the index explicitly first and recreated it and it finishd in 35 mins.. now I am wondering what to explain to the management the "Strange behaviour" ... |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-11 : 22:07:20
|
| Is it on big column or multiple columns? How much free space in the db? Checked disk i/o and other resources usage on the server? |
|
|
|
shiloh
Starting Member
48 Posts |
Posted - 2007-11-11 : 23:05:36
|
| The index is just on one column. There is no issue of disk space/resource usage as there is a monitoring team of 4 ppl just looking at monitors loaded with NetIQ graphs ranging from CPU usage to tempdb contention to IIS perf coutners...24/7...And as I said earlier other indexes on the same table finished in a few minutes.. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-11 : 23:24:16
|
| If that's the only process running on the server as you said, it should use as much resource as possible. Othersiwe, something is not correct. In this case, I'll monitor server myself and maybe setup trace in profiler. |
|
|
|
|
Create index script