Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Query Tuning Expand / Collapse
Author
Message
Posted Tuesday, December 27, 2011 7:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 36,789, Visits: 31,247
On my way to work... I'll have to answer your questions tonight.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1226910
Posted Tuesday, December 27, 2011 7:45 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
tabinsc (12/27/2011)
Excellent question Jeff! I do have one question: If clustered index columns are auto-appended to nonclustered indexes, what if the nonclustered index is unique? Adding the clustered index column(s) to an unique nonclustered index would change the unique constraint on it, right?


Edit (due to misread): Nope. Try it.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1226912
Posted Tuesday, December 27, 2011 7:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 1:04 PM
Points: 1,313, Visits: 1,055
There's a whole lot of learnin' to be had in this one Q and explanation. Thanks!
Post #1226917
Posted Tuesday, December 27, 2011 7:51 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
I have to admit, I spent a couple of minutes reading and re-reading the possible answers, trying to figure some way it could possibly be something I was missing. There was no way this one could be "that simple". Finally decided to go with what I thought the first time, and it was, indeed, "that simple". (And got it right.)

First QotD I've even attempted in about a year, and only bothered because of the author. Good question, good explanations.

Jeff: Quick suggestion. The mentions of beer popsicles, dust bunnies, and flying pigs (porkchops) are fun, but they are inside jokes, and might obfuscate the otherwise great explanations to anyone not in on them.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1226918
Posted Tuesday, December 27, 2011 8:19 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:13 PM
Points: 989, Visits: 1,821
I got it right and skipped past E not because it could be SARGable, but because the index is keyed to StartDate and that IS SARGable, and I figured that the system would seek out the rows based upon that and then "scan" the index for EndDate, even if it has to look at all values in the index that meet the start date requirement (but still technically a seek). I will be curious to see how the ISNULL can be SARGable as well.
Post #1226930
Posted Tuesday, December 27, 2011 9:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176, Visits: 778
Tks Jeff - great question
Post #1226995
Posted Tuesday, December 27, 2011 3:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 5,930, Visits: 8,179
Thanks for a great question, Jeff!

I had it wrong, for two reasons:
1. Not knowing that the optimizer is smart enough to rewrite an ISNULL of this form to a SARGable equivalent, and
2. Being too stupid to really check out the indexes given in the question to check if "doesn't include EventID" is a bluff.

So this question learned me one thing I didn't know yet, and attempted to learn me something I should know for a long time already.


EDIT: After looking at the question in yet a bit more detail, I realise that the ability to rewrite the ISNULL is completely irrelevant to the question. The index seek uses StartDate, not EndDate. I'm not saying that the optimizer will not rewrite an ISNULL in some cases, but it doesn't in this case! The seek includes only the StartDate in the Seek Predicate; the predicate on the EndDate is in the (non-seek) predicate of the operator.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1227126
Posted Tuesday, December 27, 2011 3:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 5,930, Visits: 8,179
Hugo Kornelis (12/27/2011)
EDIT: After looking at the question in yet a bit more detail, I realise that the ability to rewrite the ISNULL is completely irrelevant to the question. The index seek uses StartDate, not EndDate. I'm not saying that the optimizer will not rewrite an ISNULL in some cases, but it doesn't in this case! The seek includes only the StartDate in the Seek Predicate; the predicate on the EndDate is in the (non-seek) predicate of the operator.

So I decided to dig a bit deeper, and I found out that the explanation about the ISNULL is, in fact, incorrect. The optimizer will (at least in this case) NOT rewrite the ISNULL to make it SARGable.

Here's the code I used to test it - using the CREATE TABLE Jeff provided in the question, and the data generator he posted in the explanation.
CREATE INDEX ix1 ON dbo.Event(EndDate) INCLUDE (StartDate)

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

DECLARE @DesiredStartDate DATETIME,
@DesiredEndDate DATETIME,
@BeginningOfTime CHAR(8),
@EndOfTime CHAR(8);

SELECT @DesiredStartDate = '14 Jun 2012',
@DesiredEndDate = '16 Jun 2012',
@EndOfTime = '99991231';

SELECT EventID, StartDate, EndDate
FROM dbo.Event
WHERE (@DesiredEndDate <= ISNULL(EndDate,@EndOfTime))
OPTION (RECOMPILE);

I ran the last query with the actual execution plan turned on, and the result was an index SCAN instead of an index SEEK on index dbo.ix1.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1227130
Posted Tuesday, December 27, 2011 3:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 5,930, Visits: 8,179
440692 I am just a number (12/27/2011)
Could you expand on the explanation for option E

Several people asked this. The answer is that the SARGability of the ISNULL is irrelevant, because the engine will use the first column in the index (StartDate) for the seek, not the second column (EndDate).

In my previous post, I have demonstrated that -at least in this case, with a 1000-row test population-, the optimizer will in fact NOT rewrite the ISNULL to a more SARGable expression. But what I think Jeff had in mind (and he'll undoubtedly correct me if I'm wrong) is that the ISNULL expression could be rewritten as follows:

Step 1: Convert @DesiredEndDate <= ISNULL (EndDate, @EndOfTime) to
@DesiredEndDate <= EndDate
OR (EndDate IS NULL AND @DesiredEndDate <= CAST(@EndOfTime AS datetime))

Step 2: Pre-evaluate the part of the expression that contains only variables:
@DesiredEndDate <= EndDate
OR (EndDate IS NULL AND (True))

Step 3: Remove needless ballast and swap order for readability:
EndDate >= @DesiredEndDate
OR EndDate IS NULL

In an RDBMS that sorts NULL values *after* other values, this can be retrieved using a single seek (for the first qualifying row) + forward range scan (for the rest). But SQL Server orders NULL values first. That makes it a bit harder; in theory, the optimizer could use two seek / forward range scan combinations (one for the NULL values at the start; the other for the non-NULL values) and merge them. Maybe this will indeed happen if the table is large enough to warrant using all phases of the optimizer to do a full optimization. But for a thousand rows, the optimizer will simply scan the index instead.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1227133
Posted Tuesday, December 27, 2011 3:54 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
Love this question. I got A but missed B.

Converting oxygen into carbon dioxide, since 1955.

Post #1227134
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse