Timeline for Unexpected index scan instead of seek on update
Current License: CC BY-SA 4.0
6 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 28, 2018 at 13:21 | comment | added | Luke | Thanks the links were helpful. Yeah the plan changes (eg estimated row count changes to 1) however the execution plan still does not suggest a missing index. I will create the index later on today though and see if the execution plan will make use of it | |
| May 28, 2018 at 12:40 | comment | added | ypercubeᵀᴹ |
Does timing (and the plan) change when you try the various options described in that article? (using a fixed value instead of a parameter, using OPTIMIZE FOR (@parameter UNKNOWN) or OPTION RECOMPILE, etc)
|
|
| May 28, 2018 at 12:39 | comment | added | ypercubeᵀᴹ |
Have a look at this answer: Why does SQL Server use a better execution plan when I inline the variable? and this superb blog post by Paul White: Parameter Sniffing, Embedding, and the RECOMPILE Options
|
|
| May 28, 2018 at 11:43 | comment | added | ypercubeᵀᴹ |
Not sure how you compared the 2 indexes (with INCLUDE and not). I don't think there would be much difference as you are updating the value tasklockedby anyway, so the UPDATE will have to update both the CI and this index. What key lookup is prevented?
|
|
| May 28, 2018 at 11:36 | comment | added | Luke |
Thanks. I created this table in a test environment and added some indexes. The index with the includes has better performance as it includes the other two fields, and prevents a key lookup which has the same performance cost at the index seek i.e. the index with the includes is twice as fast as the index without the includes. I had to force the use of the index though with a with (index [ix_FluxInbox_tasklockedby])
|
|
| May 28, 2018 at 11:09 | history | answered | ypercubeᵀᴹ | CC BY-SA 4.0 |