Summary information |
|
|---|---|
Default value |
16 |
Valid values |
1 – 2147483647 |
Status |
Static |
Display level |
1 |
Required role |
System administrator |
Configuration groups |
DTM Administration, Memory Use |
Adaptive Server manages transactions as configurable server resources. Each time a new transaction begins, Adaptive Server must obtain a free transaction descriptor from a global pool that is created when the server is started. Transaction descriptors are internal memory structures that Adaptive Server uses to represent active transactions.
Adaptive Server requires one free transaction descriptor for:
The outer block of each server transaction. The outer block of a transaction may be created explicitly when a client executes a new begin transaction command. Adaptive Server may also implicitly create an outer transaction block when clients use Transact-SQL to modify data without using begin transaction to define the transaction.
Subsequent, nested transaction blocks, created with
additional begin transaction commands, do not
require additional transaction descriptors.
Each database accessed in a multidatabase transaction. Adaptive Server must obtain a new transaction descriptor each time a transaction uses or modifies data in a new database.
txn to pss ratio determines the total number of transaction descriptors available to the server. At start-up, this ratio is multiplied by the number of PSS structures to create the transaction descriptor pool:
# of transaction descriptors = PSS structures * txn to pss ratio
The default value, 16, ensures compatibility with versions of Adaptive Server earlier than 12.x which also allocated 16 transaction descriptors for each user connection. In version 12.x and later, the number of simultaneous transactions is limited only by the number of transaction descriptors available in the server.
You can have as many databases in a user transaction
as there are in your Adaptive Server installation. For example,
if your Adaptive Server has 25 databases, you can include 25 databases
in your user transactions.
During a peak period, use sp_monitorconfig to examine the use of transaction descriptors:
sp_monitorconfig "txn to pss ratio"
Usage information at date and time: Apr 22 2002 2:49PM. Name Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name ------------------------ ---------- ---------- ------- ----------- ---------- -------------------- txn to pss ratio 784 80 10.20 523 0 NULL
If the num_used value is zero or very low, transactions may be delayed as Adaptive Server waits for transaction descriptors to become free in the server. In this case, consider increasing the value of txn to pss ratio.
If the Max_Used value is too low, unused transaction descriptors may be consuming memory that can be used by other server functions. Consider reducing the value of txn to pss ratio.
