More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Disadvantages/Drawbacks of Using Identities in Sybase
Despite its efficacy of use, the Identity has some drawbacks:
* The mechanism that Sybase uses to allocate Identities involves a memory based prefetch scheme for performance. The downside of this is, during non-normal shutdowns of ASE (shutdown with nowait or flat out crashes) ASE will simply discard or "burn" all the unused identity values it has pre-allocated in memory. This sometimes leaves large "gaps" in your monotonically increasing identity columns and can be unsettling for some application developers and/or end users.
NOTE: Sybase 11.02.1 (EBF 6717) and below had a bug (bugid 96089) which would cause "large gaps to occur in identity fields after polite shutdowns." The Sybase 11.02.2 rollup (EBF 6886) fixed this problem. If you're at or below 11.02.1 and you use identities, you should definitely upgrade.
* (paraphrased from Sybooks P&T guide, Chapter 6): If you do a large number of inserts and you have built your clustered index on an Identity column, you will have major contention and deadlocking problems. This will instantly create a hot spot in your database at the point of the last inserted row, and it will cause bad contention if multiple insert requests are received at once. Instead, create your clustered index on a field that will somewhat randomize the inserts across the physical disk (such as last name, account number, social security number, etc) and then create a non-clustered index based on the identity field that will "cover" any eligible queries.
The drawback here, as pointed out in the Identity Optimization section in more detail, is that clustering on another field doesn't truly resolve the concurrency issues. The hot spot simply moves from the last data page to the last non-clustered index page of the index created on the Identity column.
* If you fill up your identity values, no more inserts can occur. This can be a big problem, especially if you have a large number of inserts and you have continually crashed your server. However this problem most often occurs when you try to alter a table and add an Identity column that's too small, or if you try to bcp into a table with an identity column thetas too small. If this occurs, follow the procedures for recovering from identity gaps.
* I've heard (but not been able to reproduce) that identities jump significantly when dumping and loading databases. Not confirmed.
NOTE: there are several other System 11 bugs related to Identities. EBF 7312 fixes BugId 97748, which caused duplicate identity values to be inserted at times. EBF 6886 fixed (in addition to the above described bug) an odd bug (#82460) which caused a server crash when bcping into a table w/ an identity added via alter table. As always, try to stay current on EBFs.
(Continued on next question...)
