When to use ALS

You can enable ALS on any specified database that has at least one of the following performance issues, so long as your systems runs 4 or more online engines:

Heavy contention on the last log page.

You can tell that the last log page is under contention when the sp_sysmon output in the Task Management Report section shows a significantly high value. For example:

Table 13-4: Log page under contention
Task Management

per sec

per xact

count

% of total

Log Semaphore Contention

58.0

0.3

34801

73.1
Heavy contention on the cache manager spinlock for the log cache.

You can tell that the cache manager spinlock is under contention when the sp_sysmon output in the Data Cache Management Report section for the database transaction log cache shows a high value in the Spinlock Contention section. For example:

Table 13-5:
Cache c_log

per sec

per xact

count

% of total

Spinlock Contention

n/a

n/a

n/a

40.0%
Underutilized bandwidth in the log device.

**Table 13-4: Log page under contention**
Task Management	per sec	per xact	count	% of total
Log Semaphore Contention	58.0	0.3	34801	73.1

**Table 13-5:**
Cache c_log	per sec	per xact	count	% of total
Spinlock Contention	n/a	n/a	n/a	40.0%

You should use ALS only when you identify a single database with high transaction requirements, since setting ALS for multiple databases may cause unexpected variations in throughput and response times. If you want to configure ALS on multiple databases, first check that your throughput and response times are satisfactory.