Timeline for SQL Server indexes Work Sometimes
Current License: CC BY-SA 4.0
12 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Feb 1, 2019 at 9:58 | comment | added | BainAnator | Sorry, seems there is a misunderstanding, I meant not having it in was the issue. | |
| Jan 31, 2019 at 12:34 | comment | added | sepupic | >>>its the OPTION(RECOMPILE) that was the issue<<< Your queries were WITHOUT this option, on contrary, it could HELP to find the optimal plan, as in this case your parameters and even variables are sniffed on every execution. You instead used (OPTIMIZE FOR UNKNOWN), that excludes use of statistics, and where there can be only one row returned for some certain values of variables, server estimates much more rows as it has no idea about the values passed | |
| Jan 31, 2019 at 11:07 | vote | accept | BainAnator | ||
| Jan 31, 2019 at 11:07 | comment | added | BainAnator | I Appreciate all the help guys, its the OPTION(RECOMPILE) that was the issue. i shall request with the systems developers to change the queries. | |
| Jan 31, 2019 at 10:39 | comment | added | Denis Rubashkin |
The only option to make query optimizer use the index is to replace OPTION(OPTIMIZE FOR UNKNOWN) with OPTION(RECOMPILE). Then, depending on statistics, the index could be used if it doesn't cause a huge number of key lookup operations.
|
|
| Jan 31, 2019 at 10:06 | comment | added | sepupic | If you want to include a field of image type in your index you should pass from deprecated image type to varbinary(max), the best way to do it is to make select into new table with the cast of image as varbinary. I don't think it will help you anyway, BLOB fields are always slow to be retrieved, I think your problem is not lack of indexes, your problem is BLOBs | |
| Jan 31, 2019 at 9:48 | comment | added | BainAnator | How would i use the include since there is an image field? i tried it with all the other fields in the include with no success? | |
| Jan 31, 2019 at 9:17 | comment | added | sepupic | >>>I also read that using include just wastes space<<< Creating many useless indexes is not only waste of space, it's also a const of mantenance of those indexes on every DML operation | |
| Jan 31, 2019 at 9:14 | comment | added | sepupic | >>>Its not optimal because the datatype is of varbinary. so its a long key in many cases<<< Your clustered index key is ALWAYS presented in all your nonclustered indexes too | |
| Jan 31, 2019 at 9:06 | comment | added | BainAnator | Hi sepupic, Thanks for the explanation, Its not optimal because the datatype is of varbinary. so its a long key in many cases, but not much I can do about that. I also read that using include just wastes space and they you should only use the fields in the where clause, this has work until now. It also works on all other queries. I can't use the include in the one case as there is an image field "Note". | |
| Jan 31, 2019 at 8:54 | comment | added | Denis Rubashkin |
There is no order operation in the second query plan so the clustered index must be on [Link ID] I think.
|
|
| Jan 31, 2019 at 8:44 | history | answered | sepupic | CC BY-SA 4.0 |