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
simultaneously.
Createa4Kpool for the transaction log:
n
To reduce log writes in environments with high transaction rates or trans-
actions that create large log records
n
If your database is using 2 K log I/O
n
If the number of log writes per second is high
n
If the average number of writes per log page is slightly above one
sp_sysmon
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
Cache
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

Get Administrator's Guide to Sybase ASE 15 now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.