“Latch Contention” reports the number of times a task was switched out because it needed to wait for a latch.
If your user tables use only allpages-locking, this latch contention is taking place either on a data-only-locked system table or on allocation pages.
If your applications use data-only-locking, the contention reported here includes all waits for latches, including those on index pages and OAM pages as well as allocation pages.
In SMP environments where inserts and expanding updates are extremely high, so that page allocations take place very frequently, contention for the allocation page latch can reduce performance. Normally, Adaptive Server allocates new pages for an object on an allocation unit that is already in use by the object and known to have free space.
For each object, Adaptive Server tracks this allocation page number as a hint for any tasks that need to allocate a page for that object. When more than one task at a time needs to allocate a page on the same allocation unit, the second and subsequent tasks block on the latch on the allocation page.
You can specify a “greedy allocation” scheme, so that Adaptive Server keeps a list of eight allocation hints for page allocations for a table.
This command enables greedy allocation for the salesdetail table in database 6:
dbcc tune(des_greedyalloc, 6, salesdetail, "on")
To turn it off, use:
dbcc tune(des_greedyalloc, 6, salesdetail, "off")
The effect of dbcc tune(des_greedyalloc) are not persistent, so you need to reissue the commands after a reboot.
You should use this command only if all of the following are true:
You have multiple engines. It is rarely useful with fewer than four engines.
A large number of pages are being allocated for the object. You can use sp_spaceused or optdiag to track the number of pages.
The latch contention counter shows contention.
Greedy allocation is more useful when tables are assigned to their own segments. If you enable greedy allocation for several tables on the same segment, the same allocation hint could be used for more than one table. Hints are unique for each table, but uniqueness is not enforced across all tables.
Greedy allocation is not allowed in the master and tempdb databases, and is not allowed on system tables. Greedy page allocation is not applicable to partitioned tables.
The hints are allocation pages which potentially have free space and that the maximum number of hints maintained is 16.
