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

Consider the scenario in which a lot of processor cores are trying to insert data into the same page.
The i rst session to reach the page in question will obtain a PAGELATCH_EX latch; but in the same
moment, a large number of other threads might also be trying to acquire a PAGELATCH _ EX latch.
There would also be PAGELATCH _ SH latches acquired at the higher index levels, to allow these pages
to be traversed. If the insert needs to tip onto a new page, then a PAGELATCH _ EX would be required
at the next index level higher.
If sys.dm_os_waiting_tasks were queried during heavy inserts, it would likely show PAGELATCH _
EX waits, with the resource _ description column showing the page of note. The page could be
examined, with DBCC PAGE , and identii ed as the table under stress.
The point here is not to make a case against ever using an identity i eld for a clustered index. In
many systems, it's still an excellent idea. However, if you're seeing a large amount of latch conten-
tion during busy periods of insertion into such a table, then this design choice may certainly be a
contributor to the predicament.
The solution has to move the activity away from the hotspot of insertion. While this could be done
by simply replacing the identity i eld with a new uniqueidentifier i eld, populated with newid()
values, the same goal can be achieved in other ways. One way of spreading the load sufi ciently
without losing the benei ts of having a small clustered index, with the data nicely arranged in a
b-tree, is to introduce partitioning. This way, the table is spread across a number of b-tree
structures, instead of just one. With a bit of planning, the activity can be spread across the
partitions. There may still be a hotspot for each partition, but this could well be enough to relieve
the stress on the problem page.
The following example assumes that eight partitions are wanted, but you could choose whatever
number suited your needs. All the partitions can be put on the same i legroup; this exercise is not
designed to use partitions to spread the table across multiple i legroups, but merely to make
additional b-tree structures to store the table.
CREATE PARTITION FUNCTION pf_spread (TINYNT) AS RANGE LEFT FOR VALUES
(0,1,2,3,4,5,6);
CREATE PARTITION SCHEME ps_spread AS PARTITION pf_spread ALL TO (PRIMARY);
To spread the data across your various partitions, you simply need to introduce into the table a
column that causes the data to be distributed. In this case, ID % 8 will do nicely:
ALTER TABLE MyStressedTable
ADD PartID AS CAST(ID % 8 AS TINYINT) PERSISTED NOT NULL;
Once this is done, the clustered index simply needs to be created on the partitions:
CREATE UNIQUE CLUSTERED INDEX cixMyStressedTable (ID, PartID) ON ps_spread(PartID);
Now, inserts will be cycled around the eight partitions, which should enable many more inserts to
be done before latch contention occurs. Going back to the analogy using the chairs at a party, this
partitioning provides seven more chairs. If the number of threads being used to perform the inserts
is such that there is now a very small number of threads per b-tree, then the likelihood of contention
is very much reduced.