Beware Sneaky Reads with Unique Indexes

re: Beware Sneaky Reads with Unique Indexes

Matt Whitfield — Mon, 13 Dec 2010 15:55:50 GMT

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

re: Beware Sneaky Reads with Unique Indexes

Paul White — Mon, 13 Dec 2010 16:05:24 GMT

Wow, that was fast Matt! Thank you :)

re: Beware Sneaky Reads with Unique Indexes

Brad Schulz — Mon, 13 Dec 2010 17:49:23 GMT

I second Matt's comment... Excellent.

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

--Brad

re: Beware Sneaky Reads with Unique Indexes

hallidayd — Mon, 13 Dec 2010 18:07:24 GMT

Hey Paul

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

Otherwise, tip top stuff

Cheers

Dan

re: Beware Sneaky Reads with Unique Indexes

Chris Howarth — Mon, 13 Dec 2010 21:44:40 GMT

Great post Paul, as always.

Just curious - how do you come about this information?

Cheers

Chris

re: Beware Sneaky Reads with Unique Indexes

Paul White — Tue, 14 Dec 2010 02:27:02 GMT

Dan,

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

Thanks!

Paul

re: Beware Sneaky Reads with Unique Indexes

Paul White — Tue, 14 Dec 2010 02:45:06 GMT

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

re: Beware Sneaky Reads with Unique Indexes

Paul White — Tue, 14 Dec 2010 02:51:05 GMT

Brad,

Thanks - and right back at you :)

Paul

re: Beware Sneaky Reads with Unique Indexes

Uri Dimant — Tue, 14 Dec 2010 07:30:24 GMT

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

re: Beware Sneaky Reads with Unique Indexes

Paul White — Tue, 14 Dec 2010 07:45:06 GMT

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

re: Beware Sneaky Reads with Unique Indexes

Chris — Sun, 26 Dec 2010 13:11:38 GMT

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.

re: Beware Sneaky Reads with Unique Indexes

Paul White — Sun, 26 Dec 2010 18:31:45 GMT

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

re: Beware Sneaky Reads with Unique Indexes

Paul Randal — Mon, 21 Feb 2011 12:03:25 GMT

Excellent post Paul!

re: Beware Sneaky Reads with Unique Indexes

Paul White — Mon, 21 Feb 2011 13:14:46 GMT

Cheers, Paul :)

Enforcing Uniqueness for Performance

Paul White: Page Free Space — Thu, 28 Jul 2011 15:38:44 GMT

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