Timeline for Unexpected index scan instead of seek on update
Current License: CC BY-SA 4.0
14 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 7, 2020 at 22:23 | history | edited | CommunityBot |
added [query-performance] to 2412 questions - Shog9 (Id=1924)
|
|
| May 28, 2018 at 13:28 | comment | added | Erik Reasonable Rates Darling | Be careful testing with local variables. | |
| May 28, 2018 at 12:11 | answer | added | Tibor Karaszi | timeline score: 1 | |
| May 28, 2018 at 12:04 | comment | added | Luke | @KumarHarsh thanks. The estimated number of rows is actually off by 12million. I manually updated stats on this table and the row estimate is now 13167.3. I suspect this is root of the issue. Why does the row count not reflect as expected on the index scan statistics? | |
| May 28, 2018 at 11:17 | comment | added | KumarHarsh | reason may be that : each taskLockedBy have many rows like in above example 28637.so even if you have index on taskLockedBy it won't be utilise .Because optimizer think that it is easier to scan than seek.Optmizer find the best plan "quickly".If each taskLockedBy has fewer rows then it will prompt for missing index message. | |
| May 28, 2018 at 11:09 | answer | added | ypercubeᵀᴹ | timeline score: 1 | |
| May 28, 2018 at 11:05 | comment | added | Luke | @vonPryz thanks. The DDL has been provided in the body of the question. There are 9 other indexes on this table, but non cover even one of the required fields. | |
| May 28, 2018 at 11:04 | comment | added | Luke | @ypercubeᵀᴹ thanks. I expected the optimiser to realise that there is a missing index and suggest a missing one. Instead, it seems content with performing a scan. | |
| May 28, 2018 at 11:03 | history | edited | Luke | CC BY-SA 4.0 |
added 1914 characters in body
|
| May 28, 2018 at 10:08 | comment | added | ypercubeᵀᴹ |
Do you have an index on (tasklockedby) (with or without INCLUDE)? If there isn't one, what else is left for the optimizer than to do a table scan?
|
|
| May 28, 2018 at 10:04 | comment | added | vonPryz | What's the DDL for the table? Any other indexes? | |
| May 28, 2018 at 10:00 | history | edited | Luke | CC BY-SA 4.0 |
added 122 characters in body
|
| May 28, 2018 at 9:54 | review | First posts | |||
| May 28, 2018 at 10:04 | |||||
| May 28, 2018 at 9:51 | history | asked | Luke | CC BY-SA 4.0 |