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

It's interesting to note at this point that some of the changes required at the higher levels of a b-tree
when performing inserts and deletes are simply not required when performing updates. Unless the
update causes a page split by being larger than the earlier page, and provided the clustered index
key values for the row being updated don't change, an update command should not need to affect
the higher levels of the clustered index at all. This is similar to changing information in the page of
a book. The table of contents need not change if only the information in a particular paragraph is
being updated, and no extra pages are being introduced.
To that end, one method to avoid this kind of latch contention is to pre-populate a table with a
number of i xed-length columns, and then cycle through them with updates, using two sequences to
help the queuing stored procedures to know which value is at the top of the queue, and which one is
at the end. It is important to gauge the maximum length of the queue. The impact on the b-tree of
needing to perform inserts is signii cant, and should be avoided with a little planning.
An approach such as this can work nicely:
CREATE SEQUENCE dbo.seqQueuePush START WITH 1 CACHE 1000;
CREATE SEQUENCE dbo.seqQueuePop START WITH 1 CACHE 1000;
Unless specii ed otherwise, sequences are created using the bigint type, starting at the lowest
possible. Because the maximum bigint is extremely large, it might be a little nicer to start with 1 and
work up. Either way, it's important to have your queue start empty, with both sequences at the same
number. A cache is used to avoid a bottleneck on generating the next number. You should
experiment to see what size cache suits your particular queuing system.
As well as markers to indicate the locations of the beginning and end of your queue, you need a
table structure to hold it. For example, if you anticipate needing to be able to handle 10,000
messages in the queue, you should create 10,000 positions using placeholder messages. This enables
the b-tree to grow to the appropriate size before the system is under load.
The following code will create the queue, and populate it with the 10,000 placeholder items.
CREATE TABLE dbo.MyQueue (ID INT, Available BIT, Message CHAR(7000));
INSERT dbo.MyQueue
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1, 1, ''
FROM sys.all_columns t1, sys.all_columns t2;
The message has been chosen at 7,000 characters, as it i ts nicely within a single page. Note that it is
CHAR(7000) , not VARCHAR(7000) , as the row should be i xed length. You do not want to implement
compression at this point either. A bit column is used to indicate whether or not the position in the
queue is taken, in case the queue i lls up completely.
These 10,000 slots are numbered from 0 to 9,999. Your ever-increasing sequences will far exceed
this range, but the modulo function will provide a mapping, enabling the sequence numbers to roll
around to the start every 10 thousand entries.
When message 3,549,232 arrives, it would be pushed into slot 9232. If message 3,549,019 is being
popped out at the time, it would be found in slot 9,019. After these two operations, the sequences
would be ready to tell the system that the next slot for a push would be position 3,549,233, and for
a pop it would be 3,549,020. Any delay in processing the messages that are being popped off the
queue would be i ne as long as the size of the queue doesn't stretch beyond 10,000.