More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Optimizing your Identity setup for performance and maintenance in Sybase
If you've chosen to use Identities in your database, here are some configuration tips to avoid typical Identity pitfalls:
* Tune the burn factor!: see the vulnerability section for a discussion on what happens to identity values upon ASE crashes. Large jumps in values can crash front ends that aren't equipped to handle and process numbers upwards of 10 Trillion. I've seen Powerbuilder applications crash and/or not function properly when trying to display these large identity values.
* Run update statistics often on tables w/ identities: Any index with an identity value as the first column in the search condition will have its performance severely hampered if Update statistics is not run frequently. Running a nightly update statistics/sp_recompile job is a standard DBA task, and should be run often regardless of the existence of identities in your tables.
* Tune the "Identity Grab Size": ASE defaults the number of Identity values it pre-fetches to one (1). This means that in high insert environments the Server must constantly update its internal identity placeholder structure before adding the row. By tuning this parameter up:
1. sp_configure "identity grab size", [number]
2. go
You can prefetch larger numbers of values for each user as they log into the server an insert rows. The downside of this is, if the user doesn't use all of the prefetched block of identity values, the unused values are lost (seeing as, if another user logs in the next block gets assigned to him/her). This can quickly accelerate the depletion of identity values and can cause gaps in Identity values.
* Do NOT build business rules around Identity values. More generally speaking the recommendation made by DBAs is, if your end users are EVER going to see the identity field during the course of doing their job, then DON'T use it. If your only use of the Identity field is for its advertised purpose (that being solely to have a uniquely identifying row for a table to index on) then you should be fine.
* Do NOT build your clustered index on your Identity field, especially if you're doing lots of inserts. This will create a hot spot of contention at the point of insertion, and in heavier OLTP environments can be debilitating.
(Continued on next question...)
