3. Time the long-running transactions that are rolled back.
4. Size the growth rate of the log during the first three items listed here.
Use sp_spaceused at regular intervals to estimate how fast the log grows.
To size the log for maximum length of time:
(Deferred update: 5 min.) * (Log growth: 125pp/min.)
= (Pages allocated while transaction executes = 625pp)
or size the log for average length of time if few transactions or queries are
run for a long period of time.
Choosing the I/O Size
Group commit holds partially filled transaction log pages of 2 K for a very
brief span of time so that records of several transactions can be written to disk
Createa4Kpool for the transaction log:
To reduce log writes in environments with high transaction rates or trans-
actions that create large log records
If your database is using 2 K log I/O
If the number of log writes per second is high
If the average number of writes per log page is slightly above one
The sp_sysmon stored procedure reports on the ratio of transaction log writes
to transaction log allocations.
per sec per xact count % of total
Transaction Log Writes 22.5 458.0 1374 n/a
Transaction Log Alloc 20.8 423.0 1269 n/a
Avg # Writes per Log Page n/a n/a 1.08274 n/a
Benefits of Assigning tempdb to Its Own Data
Assigning tempdb to its own data cache reduces heavy use of the data cache
when temporary tables are created, populated, and then dropped. If tempdb is
heavily in use and you are experiencing memory contention, you can split off
a lot of the work of the hash buffers by separating tempdb I/O from the other
I/O by binding tempdb to its own named cache.
288 Chapter 12: Named Caches