Links and notes

A couple of times in this video, I use the word ‘escalate’ regarding a TABLOCK hint. In retrospect, I would have been more correct to say “take out the lock at a table level”.  Lock escalation is a different process and using the word ‘escalate’ wasn’t needed.

If you’d like to read the detailed article on Allocation Order Scans from Paul White, here you go.

Transcript

Other uses for NOLOCK. This is the most famous one that I’ve come across:

Allocation order scans

Sometimes this can be useful. It’s not always reliable and there are other ways to do it. I like the demo I’m showing because it’s showing something that’s important to remember in SQL Server. I’m not actually breaking out the debugger and proving that an allocation order scan is happening, I’m using a little undocumented commands here to show the result that indicate that an allocation order scan happened. And we’ll talk about what an allocation order scan is, I promise.

The table that we’re looking at in this demo is called agg.FirstNameByYearState

In order to get what’s called an allocation order scan, we have to have a certain minimum number of pages. So I needed a larger table, it’s still not huge. If I look at this table, the table has data going back to 1910. That’s when my state level data started being reported in the United States. The country level data goes back a little bit further.

And if we look at the clustered index on this table– there’s only one index on the table, there’s only one index which is the clustered index. Its a clustered primary key, so it is the table itself. The table itself is ordered by multiple columns, it has a compound key. The first key column in the compound key is ReportYear. So our data from 1910 is first, and then it’s ordered by state code and so on, then our data from 1911, then our data from 1912.

The table is physically ordered primarily by the ReportYear.

I am going to populate three temporary tables…

I’ve actually already run these queries because they’re a little bit slow. I’ll step you through what the queries do.

The first query populates the table named #readcommitted. It doesn’t have a NOLOCK hint or any other hint on the query. We are just reading this in read committed. We are using just one thread because what we’re going to look at is: what order does the data end up in when it gets into read committed? When we’re reading the data, what is the order that the data’s flowing through our query? I’m doing a little bit of extra stuff in here. I’m using the row number function so that I get row numbers on my data as it’s inserted to tell me here’s what went in first, and I’m using an undocumented command called fn_physlocformatter. This is not something that you should really generally ever need other than for something like a demo like this. What this does– and by the way it’s very slow and it can block– is it’s saying what page number is this row on. Where is this page physically located and located in memory, what is the page number that it’s on, and we’re going to store that in the temp table when we scan it first with read committed.

We then create another temp table called #TABLOCK, and this query is running in read committed but we have used a TABLOCK hint. We said whatever lock you need, go ahead and {take it out at the} table level. Take a lock on the whole table. And even if it is a shared lock, that means, hey, people can’t go changing the rows in the table while I’m doing this read.

When SQL Server knows the rows aren’t going to change in this table, it can do a special kind of scan called an allocation order scan

There are these pages called IAM pages which are index allocation maps– they say, okay for this index it’s got these pages in it. It doesn’t worry about the keys or the logical order that we’ve defined for the index. It doesn’t care how we’ve designed our cluster index, it just says okay, your data’s in these locations. So we can just go read that data in whatever order we want from the pages without having to follow the index sorting. In some cases, this can be faster depending on how if the data’s in memory, if the data’s fragmented, how big it is, all of these things contribute.

Sometimes it’s not faster. Scanning these pages by the allocation information can SOMETIMES be speedy in large situations.

So, using a TABLOCK hint has said: okay I’m not going to be doing locks by following the tree structure, I just want to read the allocated pages and {take out my locks at the} table {level} to make sure that data doesn’t move around underneath me. Because if data moves around underneath me, it can screw up my whole allocation order scan. So we populated the table TABLOCK with that hint still in read committed.

A third query that we ran did the exact same thing but this time, we used a NOLOCK hint

We said do it in read uncommitted. I don’t want to lock the whole table but what I’m saying here is: I don’t care if the data changes around underneath me. If the data changes around underneath me, then whatever. I don’t care if the data’s right. So if the data changes around underneath me, then whatever. That’s cool. So using the NOLOCK hint, it’s like okay, I can do the allocation order scan for you because you just don’t care. I’ll just find where the data is located.

There are other restrictions regarding when you can get an allocation order scan

It’s not just guaranteed by using NOLOCK, and it’s not guaranteed by using TABLOCK for that matter either. You have to have a minimum number of pages used, it has to be an unordered scan, and there are some other little details that have to be true. I have a link if you want to know all those details, I have a link I’ll show you for where to find them.

With these simple queries, did we qualify for it? Well, we can figure out indications as to whether we did by looking at the tables.

I first analyze the #readcommitted table in a query

I’m not saying this is good SQL, but it gets the job done. What I’m doing is, I’m using the read committed table and I’m cross applying the string split function because this loc rocator, I said that wrong, my physical locator for the row, it has the file number, colon, the page number, and then the row number, and I just want the page number. So I’m doing string split and a predicate to say I only want the page number. There are some parenthesis in there too.

What my query returns is the distinct ReportYear and page number in the order that they ended up in my temp table. This is an indication of what order did I read the data in.

Under read committed, I had to follow the index tree. This was read committed with no hints, right. No TABLOCK, it’s just read committed, so it went to the root of the index, it went to one end of the clustered index, and it read through the table, starting in 1910, it was on page number 100800 and then we went through to 1911, we have different pages happening. Here in the 1930s, we’re on page 108869, if we go down, our page numbers, here we’re back on 105, we’re following the ReportYear, we’re following the index, which leads on ReportYear and other things, we are not following the page numbers.

Let’s run the same command against the #tablock table

What order did the data go into the #tablock table in? The data went into the #tablock table in a different order. It doesn’t start in 1910. It starts in 1958.

This was {also} an unordered scan.

I selected all the data from the table but when I populated my temp table, I did not have an ORDER BY in the query

It was an unordered scan, and when you don’t have an order by in your query in SQL Server, order is not guaranteed. I can’t just assume that because the clustered index starts at 1910 that that’s the day that I’m going to get first. And that’s the thing that’s really important to remember in SQL Server is when we write SQL, order is only guaranteed if we actually use an order by. It’s not always going to be in index order.

This started on page number 100768. We have the data for 1958 here, when I scroll down, oh here’s some data from 1984, here’s some data from 1964, it’s all over the place because it was doing an index allocation map scan, because I used in this case the TABLOCK hint.

It said okay, I’m {taking out} your lock at the table level so people aren’t going to be moving around rows in here.

For the NOLOCK query, which is named the #dirtyreads table, we’re going to look at the same thing

Because I said I don’t care if the data is correct, it could do that allocation order scan using the IAM page and say I’m going to start on the page 100768 and just follow the pages. Can we even see how that works, this is kind of the cool thing in SQL Server.

I can look at that index allocation map page

I’m going to use the sys.dm_db_database_page_allocations dynamic management view. I’m saying for my current database, I want to look at our agg.FirstNameByYearState and I’m looking at the clustered index, index ID one, the only index we have on the table. I want detailed results.

This is technically an undocumented dynamic management view. It can be slow if running against large objects, and I only want page type equals ten. I only want the IAM pages for this. I can see that I only have one IAM page for this table, it’s not large enough to want more than one. My IAM page’s page number 522. Let’s look directly at that page. Turning on trace flag 3604 for my session, that says if I run the undocumented DBCC PAGE command, please put the results in my messages tab here.

I’m running the undocumented DBCC PAGE which is just for kind of geeking out for the most part. I want to look at this page in the database BabbyNames in data file one, page 522, and I’m using dump style three. There are different ways that this can return data. Dump style three shows me for my IAM page, if I scroll down, it actually shows me the IAM page has a bitmap of all these different things that are allocated to the index and hey, here is a familiar number.

This first set of pages is not allocated, starting at page 100768, hey that’s the page that our NOLOCK scan and our read uncommitted scan start at, that is a set of allocated pages. In some cases it can be faster if SQL already knows the data is not going to be changed, or if it knows you don’t care if the data’s garbage, it can just go and say okay, I’m just going to grab this stuff really quickly here, I’m not going to follow the index at all. That can, in some data warehouses, be an effective tool to use.

Personally- because often NOLOCK comes up as a recommendation for this, personally in most data warehouses, people aren’t okay if the data’s garbage.

If you want to do an allocation order scan, I would prefer to use the TABLOCK hint, personally

You don’t just have the NOLOCK hint for that. If you really don’t care if the data’s garbage, maybe it’s for an administrative query, then maybe you might use it for that case but do know when people bring up NOLOCK for the allocation order scan, know that you do have another alternative, it is not the only way to get an allocation order scan. The blog post that has a ton of detail on this is Paul White has an excellent post on sqlperformance.com.

Search for ‘Paul White allocation order scan’. He has more details on little things that might prevent allocation order scan as well as information on what they can be good for. So just a little teaser here, Paul’s post is excellent if you think hey, maybe allocation order scans could help for a specific situation I have where we need to scan a lot of data.