| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2009-10-05 : 08:44:46
|
| If an ALTER INDEX with REORGANIZE is performed online, how can it be a deadlock victim due to a page lock? I didn't think online reindexing held the type of lock that could lead to a deadlock.Thanks, Dave |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2009-10-06 : 00:22:45
|
| Hi Tara,What does BOL mean when it says "...long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction."? Does this mean that it won't cause blocking, but deadlocks are still possible?That's what made me question why a deadlock was possible.Does READ_COMMITTED_SNAPSHOT cause more overhead in terms of tempdb use as the snapshot is created?Thanks for the suggestion. I'll run a few tests.Dave |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-10-06 : 00:45:51
|
| I think the key part is the "long-term blocking table locks are not held", so it infers that short-term table locks happen.Yes READ_COMMITTED_SNAPSHOT causes more tempdb overhead, but the performance boost that you get from is worth it. We are switching to this isolation level on all of our systems per our lengthy discussions with Microsoft. It is the recommended isolation level for OLTP systems even though READ_COMMITTED is the default.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2009-10-06 : 08:09:37
|
| So you are seeing a performance boost with reindexing when this option is used? We definitely need to give that a try. We have some indexes that are over 140GB and when they need to be defragmented our reindexing job runs for several hours.Are you aware of any issues/risks, outside of heavy tempdb use, this option presents?We've been experimenting with SORT_IN_TEMPDB and setting our larger databases to BULK_LOGGED during reindexing to help speed things up and offload some work to tempdb. Our latest issue is a 13+ hour update stats job on one database due to several auto created stats on one large table. 45 minutes per statistic.Thanks, Dave |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-10-06 : 12:34:55
|
| Not a performance boost with reindexing, but an overall performance boost of all queries. I'm only aware of the heavy tempdb usage. We've been using the option on many of our production systems for many months now. Be very careful with BULK_LOGGED if you have very important data as it limits your point in time recovery capabilities. It is not recommended for mission critical systems that need to be able to recover to any point in time in case of data recovery.We use SORT_IN_TEMPB and ONLINE option where possible during the reindexing. We use Enterprise Edition.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2009-10-06 : 13:25:33
|
| When we use BULK_LOGGED it's only for reindexing. At the end of the reindexing job we set the database back to FULL recovery. A Microsoft engineer recently recommended this to us saying that several large companies he has consulted with use this method for reindexing. We figured it's worth a try.Thanks, Dave |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-10-07 : 01:08:14
|
| But switching to BULK_LOGGED loses your ability to restore to a point in time, so regardless of how large of a company it is, you need to take this into consideration. If you have a period where you wouldn't care about the recovery of data, then by all means switch to BULK_LOGGED. Our data is much too critical to lose any point in time recovery options.I discussed this topic at length with Microsoft and other MVPs as well. I received concrete information from Paul Randal, which is why I decided to only use FULL recovery model.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2009-10-07 : 07:48:57
|
| Sorry. I should have clarified our situation. On this particulare system we have a maintenace window during our Sunday reindexing operations where no critical functions are being performed. We've been told a full log restore is acceptable given that our log backups occur every 15 minutes. On all other systems we do not use BULK_LOGGED because we cannot restore to a point-in-time.Dave |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Deadlock question regarding reindexing