What did happen to the example in the last post? We’ll have to take a very high level look into some SQL internals to see what happened. A full description is completely out of scope for this entry but we’ll look to get a sense of the workload and what happened.
We’ll be looking into two different aspects that caused this issue to happen. The first is mentioned in the scripts as the workload created was going to generate a lot of Page Splits.
What is a Page Split?
A page split occurs when an insert/update needs to be performed but there is not enough room on the page for the action to succeed. The data on the page is then roughly halved, a request for a new page is made to the allocation cache, and the halved data is moved onto the new page. One thing to note for us is that the allocation of this new page is completely random in where it exists physically on disk. This comes into play when we look at the next action.
Next, we have to look at how the storage engine will gather the data. For our query there are two ways that the Storage Engine can gather the data; it can either use an Index Ordered Scan or an Allocation Ordered Scan. Now, this shouldn’t be confused with the index seek/scan operators that you see in executions plans but these are actions performed by the storage engine to fulfil this operators data requests.
(A very quick description of these two methods is below but a detailed explanation is out of scope for this post. For more in-depth information on the subject I would suggest looking at Paul Randal’s (Twitter|Blog) blog especially he’s series here: Inside the Storage Engine)
An Index Order Scan is when the storage engine scans the leaf level of the B-Tree following the logical order of the index that is defined by the PrevPage and NextPage fields in the Pages Header. This is pictured in my crude diagram below, Diagrams are not my specialty.
An Allocation Order Scan is when the storage engine will use the index IAM pages to scan the index in physical order. An IAM page contains a bitmap that tracks extents allocated to an index in 4GB chunks. The below diagram is a very simple contrived example of an Allocation Order Scan. For more information on IAM pages this blog post on IAM Pages by Paul Randal (Twitter|Blog) will help.
This type of scan can have a substantial performance benefit, however, there are a few rules put in place around the use of this type of scan to ensure data consistency. One is that the storage engine will only use the scan if it knows that the data underneath won’t change unless, like in our case, it’s been told not to care about data consistency by the use of NOLOCK or the READ_UNCOMMITED isolation level. SQL server will throw away this security feature and go for performance! So, with the query in script 2 where we’re performing a Clustered Index Scan, the storage engine is going perform an Allocation Order Scan and start at the first page in the index and continue in physical order until it hits the last one recorded.
So how do these two factors cause this side effect to happen?
Well, the best way I found to explain this is with an example from my little 2 year old. She loves Legos at the minute and let us suppose I put in front of her 4 buckets full of Legos to match the diagram above. I’ve made sure that each bucket contains 20 pink pieces. I now tell her to start at the first bucket and count all the pink Lego pieces in the four buckets going in order like the storage engine will when performing its Allocation Order Scan. She should come up with 80 pieces.
However, she starts to scan through the buckets for the pink pieces but while she is scanning through the third bucket, I come in like our page split and grab half the contents of bucket two (ten pink piece included) and put that into a bucket just after the fourth bucket. No when she finishes the forth bucket she continues onto the new fifth bucket and we end up with 90 pink pieces instead of 80.
If we take our query the events in the example would be that when she’s scanning bucket three, I perform our page split on bucket 4 and move half its content (ten pink piece) into a bucket between buckets one and two. Now when she continues onto bucket 4 she’ll only count ten pink pieces and end up with a count of 70 in total.
Hopefully this will help you put some extra thought into when to use the NOLOCK hint and but with some added weight against the ‘is it worth it?’ part of the question.