February 22, 2012 at 7:10 am
Using SQL Server 2005 Standard Edition. Database is small (15GB), but busy with several services hitting it. When all activity is suspended, re-indexing typically takes ~ 5 minutes.
When periodically running the re-indexing job (which rebuilds or reorganizes based upon how fragmented the index is), re-indexing is causing blocks and deadlocks (sometimes being the victim). When the re-indexing gets to a specific table, the blocking can actually cause some of the services to drop offline.
Standard Edition prevents the use of online indexing operations. Any ideas for how to overcome this?
I've thought about running each index to be re-indexed through SQLCMD, and then aborting it after a period of time (say, 30 seconds). This would let some pages get done, and by re-starting the process, it would eventually get done. This seems like a last resort type of thing to me.
I'm looking for ideas on how to handle the re-indexing without upgrading to Enterprise Edition, and without down times.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 22, 2012 at 7:17 am
WayneS (2/22/2012)
I've thought about running each index to be re-indexed through SQLCMD, and then aborting it after a period of time (say, 30 seconds). This would let some pages get done, and by re-starting the process, it would eventually get done. This seems like a last resort type of thing to me.
Complete waste of time. Rebuild is a single, atomic operation. Abort and it rolls back.
Got quiet periods? Reorg acceptable instead of rebuild?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 22, 2012 at 7:29 am
During the blocking, is it always specific indexes that are having this problem?
If so, you might be able to do the majority of your indexes during that time and then do the heavy hitters later at an off time.
Is your DB 24 x 7 accessible?
February 22, 2012 at 7:45 am
GilaMonster (2/22/2012)
WayneS (2/22/2012)
I've thought about running each index to be re-indexed through SQLCMD, and then aborting it after a period of time (say, 30 seconds). This would let some pages get done, and by re-starting the process, it would eventually get done. This seems like a last resort type of thing to me.Complete waste of time. Rebuild is a single, atomic operation. Abort and it rolls back.
Really? I'd always heard that cancelling it would save what it has already done, which is why I was considering that method. Okay, scratch that idea.
Got quiet periods? Reorg acceptable instead of rebuild?
There are periods that are quieter than others, but no real quite periods. This is a 24x7 operation dealing with tracking RFID tag movements in a hospital environment. Reorg might be a good choice for those bigger tables causing issues.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 22, 2012 at 7:51 am
WayneS (2/22/2012)
GilaMonster (2/22/2012)
WayneS (2/22/2012)
I've thought about running each index to be re-indexed through SQLCMD, and then aborting it after a period of time (say, 30 seconds). This would let some pages get done, and by re-starting the process, it would eventually get done. This seems like a last resort type of thing to me.Complete waste of time. Rebuild is a single, atomic operation. Abort and it rolls back.
Really? I'd always heard that cancelling it would save what it has already done, which is why I was considering that method. Okay, scratch that idea.
That's reorganise. Rebuild is a single atomic operation, cancel it and it rolls back completely.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 22, 2012 at 8:17 am
GilaMonster (2/22/2012)
WayneS (2/22/2012)
Really? I'd always heard that cancelling it would save what it has already done, which is why I was considering that method. Okay, scratch that idea.That's reorganise. Rebuild is a single atomic operation, cancel it and it rolls back completely.
And that might be your answer, Wayne. Reorg the problem children instead of rebuilding. But, ISTR that reorganize does not replace rebuild, which means you'll have to rebuild sooner or later even on the problem children.
February 22, 2012 at 8:25 am
This may sound a bit ridiculous, but if you aren't using index hints what about periodically building a redundent index and then drop the fragmented one? Not sure if it would cause blocking or deadlocks, but it may be an alternative.
February 22, 2012 at 11:19 am
Lynn Pettis (2/22/2012)
This may sound a bit ridiculous, but if you aren't using index hints what about periodically building a redundent index and then drop the fragmented one? Not sure if it would cause blocking or deadlocks, but it may be an alternative.
That may not be so ridiculous Lynn. I'll test that out. (Here's comes a 50 million row test table to see what happens.)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 22, 2012 at 11:34 am
Building an index is the same as rebuilding an index when it comes to locks take, it's creating/recreating the index and it's very much the same under the covers.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 22, 2012 at 11:35 am
Maybe another possible alternative is to disable the index and then rebuild it; some queries may time out (because the index isn't available) but nothing will have any lock clash with the rebuild.
Tom
February 22, 2012 at 11:44 am
L' Eomot Inversé (2/22/2012)
Maybe another possible alternative is to disable the index and then rebuild it; some queries may time out (because the index isn't available) but nothing will have any lock clash with the rebuild.
Doesn't stop blocking, because the index rebuild takes locks on the base table (to prevent concurrent inserts) unless the rebuild is done online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 22, 2012 at 11:45 am
Only reason I even thought about it was due to the fact I have suggested the changes to one database that would eliminate about 14 or so redundant or duplicate indexes. Seems like someone just followed the DTA or missing indexes dmv without really analyzing the suggested indexes.
February 22, 2012 at 12:04 pm
GilaMonster (2/22/2012)
L' Eomot Inversé (2/22/2012)
Maybe another possible alternative is to disable the index and then rebuild it; some queries may time out (because the index isn't available) but nothing will have any lock clash with the rebuild.Doesn't stop blocking, because the index rebuild takes locks on the base table (to prevent concurrent inserts) unless the rebuild is done online.
That almost makes me feel the way I used to a few years back when I was pretty unhappy that there was no chance we could peresuade our customers to buy Enterprise Edition licenses (for SQL 2000).
Tom
February 22, 2012 at 12:07 pm
Had the idea of setting a database to Read Committed Snapshot mode, and trying building an index on a table with some locks on some rows. No joy, still ends up with processing blocking each other and the index build/rebuild.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 22, 2012 at 12:08 pm
Only thing I can really suggest at this point is an old-style partitioned table, using a View instead of the table for code access to it, using the old rules for that. That still works, and the indexes on the underlying smaller tables are, by nature, smaller.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply