question

GPO avatar image
GPO asked

Query hitting unexpected index

I would have thought the two following batches were equivalent. SELECT last_modified_datetime ,list_number FROM dbo.mytable WHERE last_modified_datetime >= (select dateadd(mi,-30,max(last_modified_datetime)) from dbo.another_table) and another_col = '10' and list_number is not null GO DECLARE @last_modified_datetime as datetime; SET @last_modified_datetime = (select dateadd(mi,-30,max(last_modified_datetime)) from dbo.another_table); SELECT last_modified_datetime ,list_number FROM dbo.mytable WHERE last_modified_datetime >= @last_modified_datetime and another_col = '10' and list_number is not null GO My execution plan begs to differ. The first does a seek on `last_modified_datetime` in the expected index. There will only ever be about 100 rows modified in the last 30 minutes. So it's nice and quick. In the second, it's as if the execution plan is saying "Geez buddy, I have no idea what `@last_modified_datetime` is. How about we try something else? There's a good lad." Why would this be?
indexesexecution-planvariable
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What's the _estimated_ execution plan saying about number of rows, compared to the real outcome? Do you have updated statistics on the involved columns and indexes?
1 Like 1 ·
Furthermore when I use a `with (index(ix_blah))` hint to make it use the index I want it to, it is doing 13 logical reads. Without the hint (using the unexpected index) it's doing half a MILLION logical reads!
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Without seeing the execution plans, I'm speculating, but... the local variable results in a a sample against the statistics of the column while the sub-query is probably converted to a join and either the stats are not sampled or a different mechanism is employed. Those are the behaviors I'd expect, but again, I'm going off what I can see, not all the evidence. Just as an experiment, try putting the query with the variable into a procedure and substitute the variable for a parameter and see what parameter sniffing can do for you.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sacred Jewel avatar image
Sacred Jewel answered
It is not recommended to use the local variables. This is where the problem occurs. When you use local variable, the optiizer somehow is trying to work on another_col = '10' and list_number IS NOT NULL before the datetime predicate. But in the sub-query mode, the datetime filter is applied first which results in an index seek. So as per recommendations, if this is a stored procedure, populate the variables before passing it to the procedure. This will hopefully give an optimal execution plan.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wait, you lost me, don't use local variables? Why not? In this case they might be causing a problem, but they don't in all cases. In fact, in lots of cases they solve problems. I recommend using local variables all the time and so do most other people involved in tuning and code writing. You'll need to qualify that quite a bit.
2 Likes 2 ·
@Grant Fritchey I am lost as well :( This is indeed recommended by Microsoft. And with all due respect, "using local variables all the time" may not prove good. This scenario tells itself. AFAIK, In case you use input parameters or literals as filters in your queries, the SQL Server can know what the values are (parameter sniffing), but in case of using local variables,the SQL Server does not know the actual values and builds the execution plan by supposing that 30% of the table`s rows will be returned by the query. (A hard coded guess) According to this, the SQL Server optimizer can choose to use Hash Match instead of Nested Loop or Table/Index scan instead of Index seek. This can lead to inefficient execution plan. I know the point, that parameter sniffing may prove to be a problem as well. But I would use local variables only when I know that paramter sniffing may hinder the performance.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
In this case I agree with @Sacred Jewel and @rafat. The local variable makes the optimizer to guess the selectivity, on the other hand parameters may cause parameter sniffing. You could take a look at this presentation: http://www.microsoftpdc.com/2009/SVR31 If you suffer from parameter sniffing, you could try to use the "optimize for unknown" query hint.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@rafat and @Sacred Jewel are the same i.e. me :) I do not know why the answer shows the real name and comment reflects the username.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.