August 15, 2013 at 10:01 pm
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).
August 17, 2013 at 5:20 am
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
October 8, 2015 at 11:39 pm
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 9, 2015 at 12:52 am
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
October 9, 2015 at 7:44 am
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?
October 9, 2015 at 8:18 am
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.
October 9, 2015 at 11:11 am
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
October 9, 2015 at 1:17 pm
Good article! Thank you.
October 9, 2015 at 4:35 pm
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 .
October 9, 2015 at 6:05 pm
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
Change is inevitable... Change for the better is not.
October 10, 2015 at 12:37 am
Or use dynamic sql to build this query 🙂
No need to be afraid of them.
October 10, 2015 at 7:06 am
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
October 21, 2015 at 7:43 am
Some are good, some not so good.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply