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

Pushing a message onto the queue is therefore as simple as incrementing the sequence, performing a
modulo 10,000 on the sequence number to discover into which slot the message should be pushed,
and running an UPDATE command to put the message into that appropriate slot:
DECLARE @pushpos INT = NEXT VALUE FOR dbo.seqQueuePush % 10000;
UPDATE dbo.MyQueue SET Message = @msg, Available = 0
WHERE ID = @pushpos;
To pop a message from the queue, code such as this could be used:
DECLARE @poppos INT = NEXT VALUE FOR dbo.seqQueuePop % 10000;
UPDATE dbo.Queue SET Message = '', Available = 1
OUTPUT deleted.Message
WHERE ID = @poppos;
Some testing could be performed to ensure that the queue is not empty, but this technique can cer-
tainly enable up to 10,000 messages in the queue at any one time, and spread a heavy load across a
large number of pages. Most important, negative impact on the higher levels of the b-tree, caused by
performing inserts and deletes, can be avoided.
An environment that leverages the efi ciency of updates in this way has already been seen in this
chapter. There was data that needs to be updated very quickly, and updates are used rather than
inserts — as shown in Figure 7-23, the DMV sys.dm_os_latch_stats :
FIGURE 7-23
It does not contain any kind of ID i eld. The only i elds are latch_class , waiting _ requests _
count , wait _ time _ ms , and max _ wait _ time _ ms ; and yet the data is always returned in order, and the
order is meaningful. The BUFFER class is always row 28. ACCESS _ METHODS _ HOBT _ VIRTUAL _ ROOT is
always row 5 (this is a non-buffer latch that exhibits waits when root splits are needed, which would
occur if a traditional delete/insert queue had been implemented).
You may have noticed when querying this DMV that many of the entries are zero, but the entries are
still there. This is different to, say, sys . dm _ db _ index _ usage _ stats , which only includes a row once
an index is used for a scan, seek, lookup, or update operation.
The sys.dm_os_latch_stats DMV is like your queue structure. It needs to be able to respond
extremely quickly, as do many of the internal mechanisms within SQL Server. To that end, it is