Low-durability databases have their durability set to no_recovery and at_shutdown. Because low-durability databases are not recovered, they provide these performance benefits:
Low-durability databases do not flush the user log cache (ULC) due to unpinning, therefore increasing transaction throughput in high-transaction systems.
ULC unpinning occurs when two transactions attempt to update the same datarows-locked data page, causing the second transaction to flush the log records from the first transaction’s ULC to syslogs, before the second transaction uses the data page. Increasing ULC flushes to syslogs increases log contention, adversely affecting transaction throughput in high-transaction systems.
The Transaction Management section in sp_sysmon output shows that the values for ULC Flushes to Xact Log, by Unpin, and by Single Log Record (all actions associated with unpinning) are noticeably lower for low-durability databases than in full-durability databases. This increases the transaction throughput in high-transaction systems. This sp_sysmon output from a low-durability database shows no ULCs flushed from unpinning:
ULC Flushes to Xact Log per sec per xact count % of total ----------------------- ---------- ------------ ------- --------- Any Logging Mode DMLs by End Transaction 40.3 0.0 2416 100.0 % by Change of Database 0.0 0.0 1 0.0 % by Unpin 0.0 0.0 0 0.0 % by Other 0.0 0.0 0 0.0 %
Low-durability databases do not perform transaction log flushing – during a commit, because transactions are not written to disk.
Transaction log flushing is typically unnecessary for relaxed-durability databases; they do not require reliably recorded committed transactions because they are not recovered during a restart. Adaptive Server typically flushes the transaction log when a transaction completes and, for relaxed-durability databases, during the buffer wash.
The Device Activity Detail section in sp_sysmon output shows the number of writes for log devices of relaxed-durability databases. This shows the sp_sysmon output for a disk-resident, version 15.0.3 database:
Device: /disk_resident/device/1503esd2/drdb_device.dat drdb_device per sec per xact count % of total ------------------- ----------- ------------ --------- ---------- Reads APF 0.0 0.0 0 0.0 % Non-APF 14.3 0.0 2380 0.2 % Writes 9247.3 1.5 1544304 99.8 % ------------------- ----------- ------------ --------- ---------- Total I/Os 9261.6 1.5 1546684 100.0 %
This shows the same output for a relaxed durability database from a version 15.5 Adaptive Server. Because this is a relaxed-durability database, the writes are about 23 percent of the writes for the disk-resident database:
Device: /relaxed_durability/IMDB/devices/ariesSMP/rddb_device.dat rddb_device per sec per xact count % of total ------------------- ----------- ------------ --------- ---------- Reads APF 0.0 0.0 0 0.0 % Non-APF 14.7 0.0 1031 0.7 % Writes 2106.4 0.1 147446 99.3 % ------------------- ----------- ------------ --------- ---------- Total I/Os 2121.1 0.1 148477 99.9 %
Low-durability databases do not log transactions contained in the ULC. Adaptive Server discards log records for transactions in low-durability databases when it commits transactions, if all log records for each transaction are fully contained within the ULC, and the transaction does not require the log records for any post-commit processing.
Because you need not transfer the log records, the amount of contention on the log decreases, which increases throughput in high-transaction systems. Generally, logging transactions in the ULC favors smaller transactions because large transactions cannot contain all their log records in the ULC at commit time; large transactions that log more records than fit in the ULC must flush the ULC to syslogs when it fills.
ULC flushing may also occur when small transactions are contained within the ULC at commit time because Adaptive Server requires these log records to perform post-commit work. For example, ULC flushing occurs in any transaction that deallocates any space within the database when the transaction requires post-commit work.
See “ULC Flushes to Xact Log” in Chapter 2, “Monitoring Performance with sp_sysmon,” in the Performance and Tuning Series: Monitoring Adaptive Server with sp_sysmon.
Low-durability databases do not flush partially logged changes to disk. Adaptive Server must occasionally flush data pages for full-durability databases to disk because the corresponding log records do not fully describe the changes that took place. Some examples are:
Any index- or data page split for a table with a clustered index
A sort
A writetext command
Fast bcp
alter table to change a table’s locking scheme
alter table to change a table’s partitioning scheme or a table’s schema
A full table reorg rebuild
alter table...unparition
In-memory databases need not flush data pages to disk because they do not use disks. Relaxed-durability databases do not flush data pages to disk because there is no need for database recovery. Not having to flush data pages to disk can significantly improve performance when the number of disk I/Os would be high, for example, where changed pages are flushed to disk at the end of a sort operation.
Additional improvements to low-durability databases that are not directly related to their durability, occur internally, and require no administration:
Improvements for updating the database timestamp (a frequent operation in a high-transaction database)
Improvements to deletes using an index scan for data-only-locked tables
Improvements for bulk inserts into a data-only-locked table with non-unique indexes
These improvements also apply to temporary databases that have durability explicitly set to no_recovery (through create database or alter database); they do not apply to temporary databases with durability implicitly set to no_recovery.
