You can enable ALS on any database that has at least one of the following performance issues, as long as your systems runs four or more online engines:
Heavy contention on the last log page – sp_sysmon output in the Task Management Report section shows a significantly high value. This example shows a 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 – 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:
Task Management per sec per xact count % of total ----------------------- ---------- ---------- -------- -------- Spinlock Contention n/a n/a n/a 40.0
Underutilized bandwidth in the log device.
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.
