Skip to Content

Why does "alter db off device" care about duplicate key entries?

Dec 20, 2017 at 03:52 PM


avatar image

In the manual for "alter database", under the "off -> with check_only" section it says:

When checking the indexes, if the results indicate that there could be enough duplicate key entries that the command will spend a significant amount of time sorting the index, the index is reported as a problem. The recommendation is that the index should be dropped before shrinking the database and the index be re-created after the database is shrunk.

In my case, the "alter database <mydb> off <mydevice> with check_only" gives warnings related to this issue for a big table with non-clustered non-unique indexes

I need to run performance tests to see how much these non-unique indexes slowdown the "alter database off" command.

Why does alter database off care about user indexes? I (erroneously) assumed it would do some sort of low level page moves transparent to upper level functions.

Thanks in advance

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Ben Slade Dec 20, 2017 at 10:07 PM

I found an explanation about indexes used and locking in Knowledge Base article # 2467562 (Why does shrinking of database devices take long ?). The explanation is as follows:

There are two primary reasons for slow shrink-db: one less common and one very common.
The less common reason is that the shrink is waiting to obtain locks. The process works by allocation unit, taking an exclusive lock on an object while it processes that object’s pages on the current alloc unit, then releasing that lock at the end of the unit. If other tasks have conflicting locks, or if level-0 scanners (dirty readers) are working on the object, the shrink will wait. (Shrink tries very hard to run politely, letting other database users have precedence.)
The more common reason is indexes with massive numbers of duplicate keys. Every time shrink moves a data page, it must rearrange the index keys for all rows on that data page. Before it can release its lock, every index must be restored to proper order showing the new data location. For non-unique indexes, this involves sorting index keys. If a key happens to be in a group containing lots of duplicate keys, the sort can take a very long time, sometimes even leading customers to think it’s hung. We routinely see customers who have indexes with millions of identical keys. Their problem is that every 256 pages, shrink needs to stop and re-sort the same index.
One way to get advance warning when index keys might be a problem is to run the shrink command “with check_only”. This will not do the shrink, but will instead look for common problems that might interfere with the command, including index selectivity. If the check reports any index problems, consider dropping the reported index(es) during the command and recreating them when the command is complete

It's hard to tell, but it sounds like if there are non-unique indexes with few duplicate values, then there won't be a big performance hit for leaving the indexes in place during the "alter db off". For me dropping and recreating big indexes means taking the database out of production.

I'm guessing that "must rearrange the index keys for all rows on that data page" could be clarified to say "must rearrange pages containing index keys values for all key values found on the moved data page"

10 |10000 characters needed characters left characters exceeded