More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Optimizing your home grown Sequential key generating process for any version of Sybase
max_rows_per_page/fillfactor/table padding to simulate row level locking: This is the most important tuning mechanism when creating a hand -made sequence key generation scheme. Because of Sybase's page level locking mechanism, your concurrency performance in higher-insert activity situations could be destroyed unless the server only grabs one row at a time. However since Sybase doesn't currently have row-level locking, we simulate row-level locking by creating our tables in such a way as to guarantee one row per 2048 byte page.
For pre-System 11 servers; Calculate the size of your rows, then create dummy fields in the table that get populated with junk but which guarantee the size of the row will fill an entire page. For example
1. create table keystorage
2. (tablename varchar(25),
3. lastkey int,
4. filler1 char(255) not null,
5. filler2 char(255) not null,
6. filler3 char(255) not null,
7. filler4 char(255) not null,
8. filler5 char(255) not null,
9. filler6 char(255) not null,
9. filler7 char(255) not null)
10> with fillfactor = 100
11> go
We use 7 char(255) fields to pad our small table. We also specify the fillfactor create table option to be 100. A fillfactor of 100 tells the server to completely fill every data page. Now, during your initial insertion of a line of data, do this:
1. insert into keystorage
2. (tablename,lastkey,
3. filler1,filler2,filler3,filler4,filler5,filler6,filler7)
4. values
5. ("yourtable",0,
6. replicate("x",250),replicate("x",250),
7. replicate("x",250),replicate("x",250),
8. replicate("x",250),replicate("x",250),
9. replicate("x",250))
10. go
This pads the row with 1750 bytes of junk, almost guaranteeing that, given a row's byte size limit of 1962 bytes (a row cannot span more than one page, thus the 2048 page size minus server overhead == 1962), we will be able to simulate row level locking.
In Sybase 11, a new create table option was introduced: max_rows_per_page. It automates the manual procedures above and guarantees at a system level what we need to achieve; one row per page.
1. create table keystorage
2. (tablename varchar(25),
3. lastkey int)
4. with max_rows_per_page = 1
5. go
# Create unique clustered indexes on the tablename/entity name within your keystorage table. This can only improve its performance. Remember to set max_rows_per_page or the fillfactor on your clustered index, as clustered indexes physically reorder the data.
# Break up the process into multiple transactions wherever possible; this will reduce the amount of time any table lock is held and will increase concurrency in high insertion environments.
# Use Stored Procedures: Put the SQL commands that update the keystorage table and then insert the updated key value into a stored procedure. Stored procedures are generally faster than individual SQL statements in your code because procedures are pre-compiled and have optimization plans for index usage stored in Sybase's system tables.
# Enhance the keystorage table to contain a fully qualified table name as opposed to just the tablename. This can be done by adding fields to the table definition or by just expanding the entity name varchar field definition. Then place the keystorage table in a central location/common database that applications share. This will eliminate multiple keystorage tables but might add length to queries (since you have to do cross-database queries to obtain the next key).
(Continued on next question...)
