More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
What Is Table Partitioning in Sybase ?
Table partitioning is a procedure that creates multiple page chains for a single table.
The primary purpose of table partitioning is to improve the performance of concurrent inserts to a table by reducing contention for the last page of a page chain.
Partitioning can also potentially improve performance by making it possible to distribute a table's I/O over multiple database devices.
Page Contention for Inserts
By default, ASE stores a table's data in one double-linked set of pages called a page chain. If the table does not have a clustered index, ASE makes all inserts to the table in the last page of the page chain.
When a transaction inserts a row into a table, ASE holds an exclusive page lock on the last page while it inserts the row. If the current last page becomes full, ASE allocates and links a new last page.
As multiple transactions attempt to insert data into the table at the same time, performance problems can occur. Only one transaction at a time can obtain an exclusive lock on the last page, so other concurrent insert transactions block each other.
Partitioning a table creates multiple page chains (partitions) for the table and, therefore, multiple last pages for insert operations. A partitioned table has as many page chains and last pages as it has partitions.
I/O Contention
Partitioning a table can improve I/O contention when ASE writes information in the cache to disk. If a table's segment spans several physical disks, ASE distributes the table's partitions across fragments on those disks when you create the partitions.
A fragment is a piece of disk on which a particular database is assigned space. Multiple fragments can sit on one disk or be spread across multiple disks.
When ASE flushes pages to disk and your fragments are spread across different disks, I/Os assigned to different physical disks can occur in parallel.
To improve I/O performance for partitioned tables, you must ensure that the segment containing the partitioned table is composed of fragments spread across multiple physical devices.
Caveats Regarding I/O Contention
Be aware that when you use partitioning to balance I/O you run the risk of disrupting load balancing even as you are trying to achieve it. The following scenarios can keep you from gaining the load balancing benefits you want:
* You are partitioning an existing table. The existing data could be sitting on any fragment. Because partitions are randomly assigned, you run the risk of filling up a fragment. The partition will then steal space from other fragments, thereby disrupting load balancing.
* Your fragments differ in size.
* The segment maps are configured such that other objects are using the fragments to which the partitions are assigned.
* A very large bcp job inserts many rows within a single transaction. Because a partition is assigned for the lifetime of a transaction, a huge amount of data could go to one particular partition, thus filling up the fragment to which that partition is assigned.
(Continued on next question...)
