Latches and Spinlocks - SQL Server 2012 Internals and Troubleshooting - page 205

Of course, additional partitions might translate into more work i nding data using the ID i eld. A
query that simply i lters on the ID i eld would need to search all eight partitions, despite the fact that
you can see a correlation between the ID and the partition. To avoid having to search across all the
partitions, code such as

SELECT *
FROM dbo.MyStressedTable
WHERE ID = @id;

should be changed to

SELECT *
FROM dbo.MyStressedTable
WHERE ID = @id
AND PartID = CAST(@id % 8 AS TINYINT);

Queuing
Another typical scenario that can exhibit large amounts of latch contention is a system designed to
allow queuing, for similar reasons to the last example, although exhibited in a slightly different way,
and certainly resolved with a different method.

Most queues are handled using a table, with numerous inserts used to push items onto the queue,
and deletes using TOP to enable quickly locating the earliest row in the table. Techniques such as
using the OUTPUT clause can help with concurrency, but as the load increases this kind of design can
still end up showing latch contention issues.

Certainly there would be PAGELATCH_EX waits in the leaf levels, as in the last example; but from time
to time, activity in the leaf levels would cause similar activity through the higher levels of the b-tree,
even up to the root. This means there is potential for contention between the inserts and deletes, even
if they are at opposite sides of the b-tree. A representation of this can be seen in Figure 7-22.

HOBT_ROOT

Min Max

FIGURE 7-22