THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Paul White: Page Free Space

A technical SQL Server blog from New Zealand.

Beware Sneaky Reads with Unique Indexes

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

image

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:

image

Looking at the Clustered Index Scan, we can see that SQL Server only retrieves the pk and some_value columns during the scan:

image

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):

image

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:

image

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:

image

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:

image

[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:

image

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:

image

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:

image

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):

image

A unique non-clustered index (on a heap) works well too:

image

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: SQLkiwi@gmail.com
Twitter: @SQL_Kiwi

Published Tuesday, December 14, 2010 4:34 AM by Paul White

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Matt Whitfield said:

Sir, once again I am in awe. Brilliant stuff.

December 13, 2010 10:55 AM
 

Paul White said:

Wow, that was fast Matt!  Thank you :)

December 13, 2010 11:05 AM
 

Brad Schulz said:

I second Matt's comment... Excellent.

Always a joy to read your posts... I always learn something.

--Brad

December 13, 2010 12:49 PM
 

hallidayd said:

Hey Paul

First NCI is supposed to be non-unique right? Looks identical to the second NCI...

Otherwise, tip top stuff

Cheers

Dan

December 13, 2010 1:07 PM
 

Chris Howarth said:

Great post Paul, as always.

Just curious - how do you come about this information?

Cheers

Chris

December 13, 2010 4:44 PM
 

Paul White said:

Dan,

Yes, you're absolutely right.  Well spotted!  I've updated the name and definition of the index used in test 2.

Thanks!

Paul

December 13, 2010 9:27 PM
 

Paul White said:

Chris,

That's a difficult question to answer, but generally it comes from digging into execution plans, and running experiments to try to understand why SQL Server does what it does.

The information on Split/Sort/Collapse comes from Conor Cunningham's excellent chapter in SQL Server 2008 Internals.

Paul

December 13, 2010 9:45 PM
 

Paul White said:

Brad,

Thanks - and right back at you :)

Paul

December 13, 2010 9:51 PM
 

Uri Dimant said:

Hi Paul

If we create a NCI on the LOB column, SQL Server creates copy of that index (thanks to Paul Randal) thus it takes time .. Usually I am not creating an index on LOB nor include them, as performance gain is too little... Little bit OT,sorry

http://dimantdatabasesolutions.blogspot.com/2010/11/blobs-and-covering-indexes.html

December 14, 2010 2:30 AM
 

Paul White said:

Hi Uri,

Yes, including large LOB columns in non-clustered index is perhaps a dubious practice, and not something I can recall doing often myself.

This post is less about LOBs, and more about included columns and unique indexes in general, but I take your point.

Thanks for taking the time to comment.

Paul

December 14, 2010 2:45 AM
 

Chris said:

Hi,

I'm curious - in Test 1 - why does the clustered index have to be updated, when we're only updating some_value?

An obvious answer, I'm sure - I just can't see it.

Thanks,

Chris.

December 26, 2010 8:11 AM
 

Paul White said:

Hi Chris,

The clustered index *keys* aren't being updated; the clustered index is being used to locate the row to change.  Make sense?

Paul

December 26, 2010 1:31 PM
 

Paul Randal said:

Excellent post Paul!

February 21, 2011 7:03 AM
 

Paul White said:

Cheers, Paul :)

February 21, 2011 8:14 AM
 

Paul White: Page Free Space said:

Just a quick blog entry today, leading up to the next one (which is going to be *awesome*).&#160; A little

July 28, 2011 11:38 AM
 

Chris Pursey said:

Wonderful post Paul.

I was just experimenting with removing a very large XML column from one of our indexes to save space (resulting in it no longer being a covering index).

I was expecting to see a key lookup operator appear, but it was nowhere to be seen.  I couldn't figure out how it was getting the extra column, it seemed to be magic!

It turns out that because the query is in a CTE with an update at the end, the Clustered Index Update was reading the extra column out.

Your post explained this beautifully and confirmed that it wasn't, in fact, magic.

Cheers,

Chris

January 24, 2014 11:16 AM
 

Paul White said:

Hi Chris,

Very interesting, thanks for that! Turns out there is very little actual magic in the query processor - though there is more than a little sneakiness :)

Paul

January 24, 2014 5:04 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement