Life is full of incorrect assumptions, many of which
concern things that we take for granted and never
bother to check, just because they seem so trivial,
or because we make wrong conclusions based on our
observations and understanding of reality, or because
we misinterpret other explanations. Try answering the
following questions before you read on, and when you’re
done reading the article, answer them again: When you use
the Read Uncommitted isolation (e.g., when specifying the
NOLOCK hint) to query data, what would change in SQL
Server’s treatment of SELECT queries compared with the
default Read Committed isolation? When you query data
using the Read Uncommitted or Read Committed isolation,
can you get the same row multiple times? When you
query data using the Read Uncommitted or Read Committed
isolation, is it possible that your query will skip
a row? When you examine a query’s execution plan and
see an Index Scan or Clustered Index Scan operator with
Ordered: False, how will SQL Server scan the data?
In this article, I’ll try to shed some light on the way
reads behave in different scenarios and the consistency
level you should expect in each scenario. Most of this
article’s findings are
fairly recent—in the
past year or so—
and my answers to
the questions above
before those findings were wrong or incomplete. I found
myself surprised by the true answers to these questions (at
least true to the best of my current understanding), and
my current understanding led me to revisit my past choices
and recommendations regarding reading data. I described
some of these findings in my blog, but here I’ll provide
a more complete picture, as well as some new findings.
But first, I’ll set some groundwork with a few important
fundamentals.
Some Important Fundamentals
Bear with me if you’re already familiar with the concepts
that follow. I believe they’re integral to understanding this
article’s contents.
Heap table. A heap table is an organization of a table
with no clustered index. SQL Server maps heap data with
one or more Index Allocation Map (IAM) pages that map
the table’s data in file order. No logical ordering is enforced
in a heap. When a new row is inserted into a heap, SQL
Server uses a special type of page called Page Free Space
(PFS) to locate free space in which to insert the new row.
When you update a variable-length column in a row in a
heap with a longer value such that the row doesn’t have
room to expand in the original page, SQL Server will move
the row to another page, leaving a forwarding pointer
in the row’s original location, pointing to the row’s new
address. It’s important to note that neither inserts nor
updates can cause page splits (which I’ll describe in a
moment) in a heap.
Clustered table. A clustered table is an organization of
a table with a clustered index in a B-Tree data structure.
For the purpose of this discussion, I’ll focus on the leaf
level of the index, which contains the full data rows. The
pages in the leaf level are organized in a doubly linked
list, maintaining logical index key ordering. Besides the
linked list, SQL Server also maps the leaf level’s data
with IAM pages in file order. The logical order of the
pages maintained by the linked list (index key order) can
be different from the order of the pages in the files. The
discrepancy between index order of pages and file order of
pages is called logical fragmentation. This fragmentation is
measured as the percentage of out-of-order pages as you
scan the pages in index order. An out-of-order page is one
that appears in the file before the page that points to it in
the linked list.
Nonclustered index. Like a clustered index, a nonclustered
index is organized as a B-Tree. The leaf level contains
as many rows as the number of rows in the table, each with
a copy of a subset of columns from the corresponding
data row, plus a row locator that’s used to locate the full
data row. The index leaf level organizes the data in a doubly linked list, maintaining logical index key ordering.
SQL Server also maps the nonclustered index leaf level’s
data with IAM pages in file order. The aforementioned
fragmentation is also relevant to nonclustered indexes.
Page splits. When a new row is inserted into an index
(clustered or nonclustered), SQL Server identifies the target
page based on the new row’s index key order. If the target
page has no room to accommodate the new row, SQL
Server will split the page—that is, it will allocate a new page
(before or after the current page in the file or in another
file), move half the rows from the original page to the new
one, insert the row to one of the two pages depending on
the index key order of the new row, and update the pointers
in the linked list to preserve logical index key ordering
among the pages. The only exception to this rule is when
the new row has a higher index key value than the highest
existing key, in which case SQL Server won’t move half
the rows from the original page to the new one. A similar
split process will take place if a row is expanded as a result
of an update to a variable-length column when there’s no
room for the row to expand in the page.
Allocation-order scan. This type of scan can be used to
scan heap or index (clustered and nonclustered) data in file
order based on IAM pages. With heap data, that’s the only
type of scan that can be used. With index data, that’s one
of the two possible alternatives. The performance of allocation-
order scans isn’t affected by logical fragmentation
because this scan is done in file order, not in index order.
However, the performance of allocation-order scans is
affected by file system fragmentation of the database data
files. SQL Server Enterprise Edition supports Advanced
Scanning (aka merry-go-round scans), which is applicable
only to allocation-order scans (not to index-order scans,
which I’ll describe next). Advanced scanning allows a
request for an allocation-order scan to piggyback an
already-running allocation-order scan, and when both
processes being served by the same scan are done, the one
that joined later will scan the remaining data (before the
point it joined the scan).
Index-order scan. This type of scan can be used to scan
index data in logical index key order—that is, scanning
the leaf-level pages by following the linked list either from
head to tail (forward scan) or from tail to head (backward
scan). When this type of scan is used, the data is returned
to the next operator in the plan (or to the caller, if that’s
the last operator being processed) in logical index key
order. The performance of index-order scans is affected by
logical fragmentation since out-of-order pages require the
disk arm to go back and forth. With zero fragmentation,
the performance of an index-order scan should be similar
to that of an allocation-order scan: The higher the fragmentation,
the worse the performance of an index-order
scan compared with an allocation-order scan—up to
several times slower. The only exception is when the table
is very small (up to 64 pages); an allocation-order scan
can be more expensive than an index-order scan because
of some overhead involved with this type of scan.
Consistency Problems
with Read Uncommitted
The use of the Read Uncommitted isolation level (e.g.,
with the NOLOCK hint) is a common practice in attempt
to overcome blocking problems under the default Read
Committed isolation. However, with Read Uncommitted,
there are several changes in the way SQL Server handles
reads that you might not be aware of. This month and
next, I’ll describe read-consistency problems under the
Read Uncommitted isolation, resulting from inserts running
while processes are reading data. In a later article,
I’ll cover read-consistency problems under both Read
Uncommitted and Read Committed isolations resulting
from updates running while processes are reading data.
A common perception programmers have regarding
behavioral changes of reads when working with Read
Uncommitted is that the only change is that SQL Server
won’t request shared locks when reading data. Therefore,
readers can get “dirty data”—that is, uncommitted
changes. But the reality is that there are other behavioral
changes of readers besides not acquiring shared locks
that can result in reading the same row multiple times or
skipping rows that existed when the read started.
Continued on page 2.
Prev. page
[1]
2
next page 
