How efficient is your covered index?

Question

How efficient is your covered index?

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Good stuff, Simon.

If people want to see the impact more clearly, pushed non-SARGable predicates can be separated out into a separate Filter operator by enabling undocumented trace flag 9130 (OPTION (QUERYTRACEON 9130) is the easiest way to do this).

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SimonLiew SSCrazy Eights Points: 8293 More actions · Answer 2

Paul White (8/15/2013)
Good stuff, Simon.
If people want to see the impact more clearly, pushed non-SARGable predicates can be separated out into a separate Filter operator by enabling undocumented trace flag 9130 (OPTION (QUERYTRACEON 9130) is the easiest way to do this).

Thank you Paul for the addition info.

Simon Liew
Microsoft Certified Master: SQL Server 2008

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

james.of.rivendell (8/12/2013)
Pretty sure it's called 'covering index' or less commonly 'cover index', but not 'covered index'. The index is not the one being covered.

+1

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Beugen SSC Journeyman Points: 94 More actions · Answer 4

SimonLiew (8/12/2013)
gfey (8/12/2013)
Hi,
... On the other hand the resultset of first solution depends on the option ANSI_NULLS. If the option is set to ON the query returns only rows where carriertrackingnumber is null, otherwise it returns an empty resultset. ..
Regargs
Gregor
Hi Gregor,
Thanks for your comment.
Microsoft has announced ANSI_NULLS deprecation in SQL 2012. ANSI_NULLS OFF will not be supported in future version of SQL. Hence, I haven’t evaluated this as an option.

Hi

Thanks for the article.

However, I had the same question as Gregor, and I am not sure this answers his question?

Since ANSI_NULLS is ON, in the original Query the @Carrier would just be ignored if it for some reason was set to NULL (@Carrier = NULL).

But in the first solution you have CarrierTrackingNumber = @Carrier and that should not Return any rows if @Carrier is set to NULL.

Or am I missing something?

Anyway, good article 🙂

br

Gert

JustMarie SSCertifiable Points: 7771 More actions · Answer 5

And the argument for SARGable is once again reinforced. We can keep preaching to the choir and then fixing the slow performing queries to the unconverted. 😎

To keep your query as similar as possible I'm wondering why you didn't set your final @Carrier value to the ISNULL(@Carrier, CarrierTrackingNumber) that was in the original query?

David.Poole SSC Guru Points: 76000 More actions · Answer 6

romanilic (8/13/2013)
Interesting article.
I have index with 3 columns.
I get Index Seek with 2 columns in Seek Predicates.
And one column in Predicates: [T_Encounter].[sysDeleted] as [e].[sysDeleted]='N'
There are no hidden implicit conversions. sysDeleted and 'N' are both same type.
No mater what I do, I can't get sysDeleted column into Seek Predicates?

The following possibilities occur to me

1. If the values can either be Y or N then that is low cardinality hence poor selectivity so inclusion in an index won't achieve much.

2. Collation sequence differences. Session vs DB.

3. If you are using an ORM tool then check the data types and lengths.

LinkedIn Profile

Andre Ranieri SSCrazy Points: 2819 More actions · Answer 7

Can I get clarification on SARGability related to the predicate CarrierTrackingNumber = ISNULL(@Carrier, CarrierTrackingNumber)? I would have thought that this predicate is still SARGable since the ISNULL function is performed on the @Carrier variable, not the CarrierTrackerColumn column itself.

Wouldn't the query optimizer simply evaluate whether @Carrier is NULL at the outset, then apply a NC seek predicate for the CarrierTrackerNumber against the function result?

I understand that a applying a function against the column itself renders it non-SARGable and potentially forces a scalar calculation for every row in the table, but I might have been operating under a misconception that applying a function against the parameter would still be considered SARGable.

Thoughts?

Andre Ranieri

Mike Good SSCertifiable Points: 7415 More actions · Answer 8

Mike Good

SSCertifiable

Points: 7415

October 9, 2015 at 1:17 pm

#1832651

Good article! Thank you.

t.ovod-everett Default port Points: 1487 More actions · Answer 9

The whole point of that "CarrierTrackingNumber = ISNULL(@Carrier, CarrierTrackingNumber)" predicate is to provide a way of turning off the filter by passing @Carrier a NULL. It's not to look for NULL values, it's to allow a single stored procedure to support various combinations of search parameters.

Unfortunately, the stored procedure developer was lazy - the CarrierTrackingNumber that is the second parameter to the ISNULL will change from row to row, thus ensuring the equality always matches when @Carrier IS NULL (unless CarrierTrackingNumber happens to be NULL, in which case that row will be filtered out since NULL = NULL is Unknown). Any time you manipulate a field, left or right side of the operator, you pretty much guarantee that your predicate is no longer SARGable.

A better way to rewrite that predicate is "(CarrierTrackingNumber = @Carrier OR @Carrier IS NULL)". The problem is that then the query optimizer still can't rely on the predicate unless you use "OPTION (RECOMPILE)". If you use the latter, than the optimizer is allowed to look at the value of @Carrier and use that it designing the query plan. If @Carrier is NULL, it knows there is no need to check CarrierTrackingNumber = @Carrier and will design an appropriate plan (i.e. only using the first parameter in the Seek Predicate and using the third as a normal Predicate). If @Carrier is not NULL, it knows that it can safely use @Carrier in the Seek Predicate. But if you don't use "OPTION (RECOMPILE)", it has to assume either case is possible, and thus it can't use @Carrier in the Seek Predicate.

But "OPTION (RECOMPILE)" means every call gets recompiled - yuck! My personal experience is that if you want good behavior out of these sorts of stored procedures, provide explicit queries for the common use cases gated by testing the passed parameters for NULL. At the end, you can cover all the uncommon cases with a single query that tests all the parameters like above and uses "OPTION (RECOMPILE)". Recompiling isn't the best approach, but it still usually beats a clustered index scan!

Personally, I'd really like to see Microsoft implement Skip Scanning of indexes. See https://connect.microsoft.com/SQLServer/feedback/details/695044/implement-index-skip-scan .

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 10

Andre Ranieri (10/9/2015)
Can I get clarification on SARGability related to the predicate CarrierTrackingNumber = ISNULL(@Carrier, CarrierTrackingNumber)? I would have thought that this predicate is still SARGable since the ISNULL function is performed on the @Carrier variable, not the CarrierTrackerColumn column itself.
Wouldn't the query optimizer simply evaluate whether @Carrier is NULL at the outset, then apply a NC seek predicate for the CarrierTrackerNumber against the function result?
I understand that a applying a function against the column itself renders it non-SARGable and potentially forces a scalar calculation for every row in the table, but I might have been operating under a misconception that applying a function against the parameter would still be considered SARGable.
Thoughts?
Andre Ranieri

My thought is that one test is worth a thousand expert opinions. 😉 Test it! :w00t:

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

etheral1980 Old Hand Points: 303 More actions · Answer 11

Or use dynamic sql to build this query 🙂

No need to be afraid of them.

SimonLiew SSCrazy Eights Points: 8293 More actions · Answer 12

This is a contrived example, but it is a common one that I have seen.

What happen in the query is that the ordering of the index initially was ModifiedDate, CarrierTrackingNumber, ProductID. But the query was only able to use the first column ModifiedDate in the index to perform a seek due to the ISNULL function not allowing SQL to properly get the value in the second column CarrierTrackingNumber. If the query is unable to get to the second column in index, basically it can't get to the third column as well. However in the query plan, you'll still observe an Index Seek operator. Its only when you scroll your mouse over the Index Seek operator that you'll find the index is not as effective as it should.

It is not as effective because its only using the first column in the index seek, then underneath performs a filter which has to scan all rows where ModifiedDate = '1 Aug 2007' to just return the one record which you get in your output. This is kind of quite similar to having ModifiedDate as an index and both CarrierTrackingNumber and ProductID as included columns.

There are numerous ways to tune the query and it is very difficult to describe each and every solution. By understanding what the example has provided, ModifiedDate and ProductId is still SARGable. So, if you create an index in the ordering ModifiedDate, ProductId, CarrierTrackingNumber (or ProductId, ModifiedDate, CarrierTrackingNumber ) then you would have reduced the number of residual rows that needs to be filtered from the Index Seek.

In this article, I just wanted to point out that an Index Seek operator doesn't mean all columns in the index definition are used to "seek" and return the result. The predicate in an Index Seek operator indicate there are residual rows that needs to be filtered after a "seek" on an index.

Hope this clarifies and doesn't confuse further.

Simon Liew
Microsoft Certified Master: SQL Server 2008

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 13

akljfhnlaflkj

SSC Guru

Points: 76202

October 21, 2015 at 7:43 am

#1834669

Some are good, some not so good.