During minimally logged DML, insert, update, delete, and slow bcp-in commands are performed with minimal or no logging. If a statement fails (for example, from an internal error or a user-issued rollback), the portion of the work already completed stays committed, and is not rolled back. However, when configured for minimally logged DML, Adaptive Server must maintain logical consistency for changed tables (for example, rolling back a data row insert must result in a rollback of the related index rows). To ensure this consistency, minimally logged DML commands are divided into elementary unit operations named subcommands, which are all the data changes required to perform a single row change, including those for indexes and any text, image, or off-row columns. To maintain the logical consistency at the subcommand level, Adaptive Server logs minimally logged DML commands in the user log cache (ULC). Once a subcommand completes, Adaptive Server discards the log records for the subcommand from the ULC, there is no need to flush the ULC to syslogs.
If a ULC is not large enough to contain all of a subcommands’ log records, Adaptive Server may be unable to discard the log records. This generally happens if the data row affected by the DML is very large, or if a table contains many indexes. If Adaptive Server cannot discard the ULC, it flushes the ULC log records to syslogs, which:
Increases log contention in a busy system
Impedes transaction throughput
Increases the amount of log space required, which offsets any benefits gained by using minimally logged DMLs
Make sure that ULCs are large enough to contain the log records for most subcommands. Sybase recommends that your ULC be twice as large as the default server page size:
declare @ulc_size int select @ulc_size = @@maxpagesize * 2 exec sp_configure "user log cache size", @ulc_size
For in-memory temporary databases or relaxed-durability temporary databases for which the durability has been explicitly set, improve the efficiency of minimal logging and avoid ULC flushes to syslogs by creating the sessions tempdb-specific ULC twice the size as the server logical page size:
declare @ulc_size int select @ulc_size = @@maxpagesize * 2 exec sp_configure "session tempdb log cache size", @ulc_size
Generally, these scripts configure a ULC sized to contain all changes from one subcommand entirely in-memory, and yield significant improvement in concurrent DML performance.
Changing the ULC size requires you to restart Adaptive Server.
To determine if minimal logging is efficient, view the Transaction Management section of sp_sysmon output.
This example shows efficient minimally logged DML, because Adaptive Server discards most log records generated from subcommands:
ML-DMLs ULC Efficiency per sec per xact count % of total ------------------------ ------- ------------ ---------- ---------- Discarded Sub-Commands 33383.9 11087.8 3071323 100.0 Logged Sub-Commands 0.4 0.1 37 0.0
The Transaction Detail section presents a summary of DML commands performed in fully-logged versus minimally-logged mode for the specified sample. In this output, Adaptive Server performed nearly all the inserts in minimally-logged mode on a data-only locked table:
Transaction Detail per sec per xact count % of total ------------------------ ------------ ------------ ------- ---------- Inserts Fully Logged APL Heap Table 57.8 173.5 694 0.7 % APL Clustered Table 0.0 0.0 0 0.0 % Data Only Lock Table 0.7 2.0 8 0.0 % Fast Bulk Insert 0.0 0.0 0 0.0 % Minimally Logged APL Heap Table 0.0 0.0 0 0.0 % APL Clustered Table 0.0 0.0 0 0.0 % Data Only Lock Table 7775.8 23327.5 93310 99.3 %
The Transaction management section provides details about how Adaptive Server logs subcommands instead of discarding them. The output below shows the events causing ULC flushes to the transaction log, with a break-up of ULC flushes caused by fully-logged and minimally-logged DML. For the minimally-logged DML section, nearly an equal amount of flushes were due to a Full ULC and to the end of a sub-command:
ULC Flushes to Xact Log per sec per xact count % of total ------------------------ ------------ ------------ ------- ---------- Any Logging Mode DMLs by End Transaction 0.3 1.0 4 11.1 % by Change of Database 0.0 0.0 0 0.0 % by Unpin 0.0 0.0 0 0.0 % by Other 0.0 0.0 0 0.0 % Fully Logged DMLs by Full ULC 0.2 0.5 2 5.6 % by Single Log Record 0.0 0.0 0 0.0 % Minimally Logged DMLs by Full ULC 1.3 4.0 16 44.4 % by Single Log Record 0.0 0.0 0 0.0 % by Start of Sub-Command 0.0 0.0 0 0.0 % by End of Sub-Command 1.2 3.5 14 38.9 % ------------------------ ------------ ------------ ------- ---------- Total ULC Flushes 3.0 9.0 36
If the value of the count column
in the ULC Flushes to Xact Log by Full ULC section for Minimally
Logged DMLs is high compared with the number of rows being affected,
increase the value for the user log cache or
the session tempdb log cache size configuration
parameters.
The output below indicates the efficiency of ULC operations for minimally-logged commands: the system incurs a small amount of logging overhead from minimal logging because nearly all the logging activity is entirely contained within the ULC, and very little flushing to syslogs.
ML-DMLs ULC Efficiency per sec per xact count % of total ------------------------ ------------ ------------ ------- ---------- Discarded Sub-Commands 7774.7 23324.0 93296 100.0 Logged Sub-Commands 1.2 3.5 14 0.0 ------------------------- ------------ ------------ ------- Total ML-DML Sub-Commands 7775.8 23327.5 93310
