Jacob Luebbers (11/27/2008)
Hey Hugo,Hugo Kornelis (11/27/2008)
Point taken. Though the flipside is that TABLOCK increases the chance to get an unordered scan instead of an ordered one. More on that below.Maybe I'm being thick, but I don't see from your comments how TABLOCKX will increase the chance of an unordered scan... am I missing something?
Hi Jacob,
That's quite technical. Basically, there are several requirements before you get a scan that uses the IAM pages (accessing pages allocated to the table in their "physical" order -the quotes are because the file may be physically fragmented or striped in which case the real physical order might be different- to take full advantage of read ahead and to minimize head movement) instead of a scan that uses the PrevPage/NextPage pointers to read leaf pages in their logical order.
1. The optimizer has to ask for an unordered scan. It will ask for an ordered scan if it needs the rows to be sorted in the logical order imposed by the index, either for a later operation in the plan or because of an ORDER BY on the outermost query. In other cases, it will ask for an unordered scan, defering the decision to the query engine
2. The number of pages in the table has to be above a certain threshold. The IAM scan is faster, but there is a fixed overhead cost of setting it up, so if only a few pages are involved that overhead will be more than the efficiency gain and the IAM scan will not be chosen. This is why I used lots of rows in my example code.
3. Either no locking (dirty reads) or full table locking has to be in effect. I think that this is to prevent a row that has already been read from being moved to another page (due to an update of the value in the clustered index column, or due to page splits) while the scan is in process, and then being read once more. With table locking, this can't happen, and with dirty reads, you state that you are willing to take the risk. However, I never really understood this reasoning, for a change to the indexed column can also cause a row to be read twice or not at all if it happens during an ordered scan....
Jeff's final solution (bottom of the article) doesn't use an ordered subquery - just the clustered index hint to guarantee order.
Sure, but I was writing this as a reply to your reaction on the merry go round scan. However, I later saw that I didn't read Jeff's article good enough. He never mentions merry go round scans, but only a "merry go round index" - what I would probably describe as a heavily fragmented index. This is unrelated. A merry go round scan (aka advanced scan) occurs when the engine detects that a scan is already in progress - in this case, it will "piggyback" on the existing scan, and once that finished it will return to the start of the index and read from there until it arrives it the starting point. This is an Enterprise Edition only feature, and it's described at http://msdn.microsoft.com/en-us/library/aa175258(SQL.80).aspx.