A few days ago, Sandra Mueller (twitter | blog) asked a question using twitter’s #sqlhelp hash tag: “Might SQL Server retrieve (out-of-row) LOB data from a table, even if
the column isn’t referenced in
the query?” Leaving aside trivial cases (like selecting a computed column that does reference
the LOB data), one might be tempted to say that no, SQL Server does not
read data you haven’t asked for. In general, that’s quite correct; however there are cases where SQL Server might sneakily retrieve a LOB column… Example Table Here’s a T-SQL script to create that table and populate it with 1,000 rows: CREATE TABLE dbo.LOBtest
(
pk INTEGER IDENTITY NOT NULL,
some_value INTEGER NULL,
lob_data VARCHAR(MAX) NULL,
another_column CHAR(5) NULL,
CONSTRAINT [PK dbo.LOBtest pk]
PRIMARY KEY CLUSTERED (pk ASC)
);
GO
DECLARE @Data VARCHAR(MAX);
SET @Data = REPLICATE(CONVERT(VARCHAR(MAX), 'x'), 65540);
WITH Numbers (n)
AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master.sys.columns C1,
master.sys.columns C2
)
INSERT LOBtest WITH (TABLOCKX)
(
some_value,
lob_data
)
SELECT TOP (1000)
N.n,
@Data
FROM Numbers N
WHERE N.n <= 1000;
Test 1: A Simple Update
Let’s run a query to subtract one from every value in
the some_value column:
UPDATE dbo.LOBtest
WITH (TABLOCKX)
SET some_value = some_value - 1;
As you might expect, modifying this integer column in 1,000 rows doesn’t take very long, or use many resources.
The STATITICS IO and TIME output shows a total of 9 logical reads, and 25ms elapsed time.
The query plan is also very simple:
Looking at
the Clustered Index Scan, we can see that SQL Server only retrieves
the pk and some_value columns during
the scan:
The pk column is needed by
the Clustered Index Update operator to uniquely identify
the row that is being changed.
The some_value column is used by
the Compute Scalar to calculate
the new value. (In case you are wondering what
the Top operator is for, it is used to enforce SET ROWCOUNT).
Test 2: Simple Update with an Index
Now let’s create a nonclustered index keyed on
the some_value column, with lob_data as an included column:
CREATE NONCLUSTERED INDEX
[IX dbo.LOBtest some_value (lob_data)]
ON dbo.LOBtest (some_value)
INCLUDE (
lob_data
)
WITH (
FILLFACTOR = 100,
MAXDOP = 1,
SORT_IN_TEMPDB = ON
);
This is not a useful index for our simple update query; imagine that someone else created it for a different purpose. Let’s run our update query again:
UPDATE dbo.LOBtest
WITH (TABLOCKX)
SET some_value = some_value - 1;
We find that it now requires 4,014 logical reads and
the elapsed query time has increased to around 100ms.
The extra logical reads (4 per row) are an expected consequence of maintaining
the nonclustered index.
The query plan is very similar to before (click to enlarge):
The Clustered Index Update operator picks up
the extra work of maintaining
the nonclustered index.
The new Compute Scalar operators detect whether
the value in
the some_value column has actually been changed by
the update. SQL Server may be able to skip maintaining
the nonclustered index if
the value hasn’t changed (see my previous post on non-updating updates for details). Our simple query does change
the value of some_data in every row, so this optimization doesn’t add any value in this specific case.
The output list of columns from
the Clustered Index Scan hasn’t changed from
the one shown previously: SQL Server still just reads
the pk and some_data columns. Cool.
Overall then, adding
the nonclustered index hasn’t had any startling effects, and
the LOB column data still isn’t being
read from
the table. Let’s see what happens if we make
the nonclustered index unique.
Test 3: Simple Update with a Unique Index
Here’s
the script to create a new unique index, and drop
the old one:
CREATE UNIQUE NONCLUSTERED INDEX
[UQ dbo.LOBtest some_value (lob_data)]
ON dbo.LOBtest (some_value)
INCLUDE (
lob_data
)
WITH (
FILLFACTOR = 100,
MAXDOP = 1,
SORT_IN_TEMPDB = ON
);
GO
DROP INDEX [IX dbo.LOBtest some_value (lob_data)]
ON dbo.LOBtest;
Remember that SQL Server only enforces uniqueness on index keys (the some_data column).
The lob_data column is simply stored at
the leaf-level of
the non-clustered index. With that in mind, we might expect this change to make very little difference. Let’s see:
UPDATE dbo.LOBtest
WITH (TABLOCKX)
SET some_value = some_value - 1;
Whoa! Now look at
the elapsed time and logical reads:
Scan count 1, logical reads 2016, physical reads 0, read-ahead reads 0,
lob logical reads 36015, lob physical reads 0, lob read-ahead reads 15992.
CPU time = 172 ms, elapsed time = 16172 ms.
Even with all
the data and index pages in memory,
the query took over 16 seconds to update just 1,000 rows, performing over 52,000 LOB logical reads (nearly 16,000 of those using read-ahead).
Why on earth is SQL Server reading LOB data in a query that only updates a single integer column?
The Query Plan
The query plan for test 3 looks a bit more complex than before:
In fact,
the bottom level is exactly
the same as we saw with
the non-unique index.
The top level has heaps of new stuff though, which I’ll come to in a moment.
You might be expecting to find that
the Clustered Index Scan is now reading
the lob_data column (for some reason). After all, we need to explain where all
the LOB logical reads are coming from. Sadly, when we look at
the properties of
the Clustered Index Scan, we see exactly
the same as before:
SQL Server is still only reading
the pk and some_value columns – so what’s doing
the LOB reads?
Updates that Sneakily
Read Data
We have to go as far as
the Clustered Index Update operator before we see LOB data in
the output list:
[Expr1020] is a bit flag added by an earlier Compute Scalar. It is set true if
the some_value column has not been changed (part of
the non-updating updates optimization I mentioned earlier).
The Clustered Index Update operator adds two new columns:
the lob_data column, and some_value_OLD.
The some_value_OLD column, as
the name suggests, is
the pre-update value of
the some_value column. At this point,
the clustered index has already been updated with
the new value, but we haven’t touched
the nonclustered index yet.
An interesting observation here is that
the Clustered Index Update operator can
read a column into
the data flow as part of its update operation. SQL Server could have
read the LOB data as part of
the initial Clustered Index Scan, but that would mean carrying
the data through all
the operations that occur prior to
the Clustered Index Update.
The server knows it will have to go back to
the clustered index row to update it, so it delays reading
the LOB data until then. Sneaky!
Why
the LOB Data Is Needed
This is all very interesting (I hope), but why is SQL Server reading
the LOB data? For that matter, why does it need to pass
the pre-update value of
the some_value column out of
the Clustered Index Update?
The answer relates to
the top row of
the query plan for test 3. I’ll reproduce it here for convenience:
Notice that this is a wide (per-index) update plan. SQL Server used a narrow (per-row) update plan in test 2, where
the Clustered Index Update took care of maintaining
the nonclustered index too. I’ll talk more about this difference shortly.
The Split/Sort/Collapse combination is an optimization, which aims to make per-index update plans more efficient. It does this by breaking each update into a delete/insert pair, reordering
the operations, removing any redundant operations, and finally applying
the net effect of all
the changes to
the nonclustered index.
Imagine we had a unique index which currently holds three rows with
the values 1, 2, and 3. If we run a query that adds 1 to each row value, we would end up with values 2, 3, and 4.
The net effect of all
the changes is
the same as if we simply deleted
the value 1, and added a new value 4.
By applying net changes, SQL Server can also avoid false unique-key violations. If we tried to immediately update
the value 1 to a 2, it would conflict with
the existing value 2 (which would soon be updated to 3 of course) and
the query would fail. You might argue that SQL Server could avoid
the uniqueness violation by starting with
the highest value (3) and working down. That’s fine, but it’s not possible to generalize this logic to work with every possible update query.
SQL Server has to use a wide update plan if it sees any risk of false uniqueness violations. It’s worth noting that
the logic SQL Server uses to detect whether these violations are possible has definite limits. As a result, you will often receive a wide update plan, even when you can see that no violations are possible.
Another benefit of this optimization is that it includes a sort on
the index key as part of its work. Processing
the index changes in index key order promotes sequential I/O against
the nonclustered index.
A side-effect of all this is that
the net changes might include one or more inserts. In order to insert a new row in
the index, SQL Server obviously needs all
the columns –
the key column and
the included LOB column. This is
the reason SQL Server reads
the LOB data as part of
the Clustered Index Update.
In addition,
the some_value_OLD column is required by
the Split operator (it turns updates into delete/insert pairs). In order to generate
the correct index key delete operation, it needs
the old key value.
The irony is that in this case
the Split/Sort/Collapse optimization is anything but. Reading all that LOB data is extremely expensive, so it is sad that
the current version of SQL Server has no way to avoid it.
Finally, for completeness, I should mention that
the Filter operator is there to filter out
the non-updating updates.
Beating
the Set-Based Update with a Cursor
One situation where SQL Server can see that false unique-key violations aren’t possible is where it can guarantee that only one row is being updated. Armed with this knowledge, we can write a cursor (or
the WHILE-loop equivalent) that updates one row at a time, and so avoids reading
the LOB data:
SET NOCOUNT ON;
SET STATISTICS XML, IO, TIME OFF;
DECLARE @PK INTEGER,
@StartTime DATETIME;
SET @StartTime = GETUTCDATE();
DECLARE curUpdate CURSOR
LOCAL
FORWARD_ONLY
KEYSET
SCROLL_LOCKS
FOR
SELECT L.pk
FROM LOBtest L
ORDER BY
L.pk ASC;
OPEN curUpdate;
WHILE (1 = 1)
BEGIN
FETCH NEXT
FROM curUpdate
INTO @PK;
IF @@FETCH_STATUS = -1 BREAK;
IF @@FETCH_STATUS = -2 CONTINUE;
UPDATE dbo.LOBtest
SET some_value = some_value - 1
WHERE CURRENT OF curUpdate;
END;
CLOSE curUpdate; DEALLOCATE curUpdate;
SELECT DATEDIFF(MILLISECOND, @StartTime, GETUTCDATE());
That completes
the update in 1280 milliseconds (remember test 3 took over 16 seconds!)
I used
the WHERE CURRENT OF syntax there and a KEYSET cursor, just for
the fun of it. One could just as well use a WHERE clause that specified
the primary key value instead.
Clustered Indexes
A clustered index is
the ultimate index with included columns: all non-key columns are included columns in a clustered index. Let’s re-create
the test table and data with an updatable primary key, and without any non-clustered indexes:
IF OBJECT_ID(N'dbo.LOBtest', N'U')
IS NOT NULL
DROP TABLE dbo.LOBtest;
GO
CREATE TABLE dbo.LOBtest
(
pk INTEGER NOT NULL,
some_value INTEGER NULL,
lob_data VARCHAR(MAX) NULL,
another_column CHAR(5) NULL,
CONSTRAINT [PK dbo.LOBtest pk]
PRIMARY KEY CLUSTERED (pk ASC)
);
GO
DECLARE @Data VARCHAR(MAX);
SET @Data = REPLICATE(CONVERT(VARCHAR(MAX), 'x'), 65540);
WITH Numbers (n)
AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master.sys.columns C1,
master.sys.columns C2
)
INSERT LOBtest WITH (TABLOCKX)
(
pk,
some_value,
lob_data
)
SELECT TOP (1000)
N.n,
N.n,
@Data
FROM Numbers N
WHERE N.n <= 1000;
Now here’s a query to modify
the cluster keys:
UPDATE dbo.LOBtest
SET pk = pk + 1;
The query plan is:
As you can see,
the Split/Sort/Collapse optimization is present, and we also gain an Eager Table Spool, for Halloween protection. In addition, SQL Server now has no choice but to
read the LOB data in
the Clustered Index Scan:
The performance is not great, as you might expect (even though there is no non-clustered index to maintain):
Table 'LOBtest'.
Scan count 1, logical reads 2011, physical reads 0, read-ahead reads 0,
lob logical reads 36015, lob physical reads 0, lob read-ahead reads 15992.
Table 'Worktable'.
Scan count 1, logical reads 2040, physical reads 0, read-ahead reads 0,
lob logical reads 34000, lob physical reads 0, lob read-ahead reads 8000.
SQL Server Execution Times:
CPU time = 483 ms, elapsed time = 17884 ms.
Notice how
the LOB data is
read twice: once from
the Clustered Index Scan, and again from
the work table in tempdb used by
the Eager Spool.
If you try
the same test with a non-unique clustered index (rather than a primary key), you’ll get a much more efficient plan that just passes
the cluster key (including uniqueifier) around (no LOB data or other non-key columns):
A unique non-clustered index (on a heap) works well too:
Both those queries complete in a few tens of milliseconds, with no LOB reads, and just a few thousand logical reads. (In fact
the heap is rather more efficient).
There are lots more fun combinations to try that I don’t have space for here.
Final Thoughts
The behaviour shown in this post is not limited to LOB data by any means. If
the conditions are met, any unique index that has included columns can produce similar behaviour – something to bear in mind when adding large INCLUDE columns to achieve covering queries, perhaps.
Paul White
Email:
[email protected]
Twitter: @PaulWhiteNZ
