Timeline for Is it a parameter sniffing problem?
Current License: CC BY-SA 4.0
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 11, 2018 at 3:50 | vote | accept | Jay Chen | ||
| Jun 27, 2018 at 14:09 | comment | added | pacreely | @JayChen don't change it, your variable and column should be the same type to prevent implicit conversions. | |
| Jun 27, 2018 at 13:16 | comment | added | Jay Chen | @pacreely yes, it is a NVARCHAR type, should I change it to varchar? (unicode is not required for this column) Hi Aaron, Thanks, I will try and see how | |
| Jun 27, 2018 at 12:33 | comment | added | Aaron Bertrand | @JayChen Don’t put a lot of weight into the estimated cost % shown in an estimated plan. They can occasionally be useful guide posts but if you’re just looking at a single plan what is the value? Every query is going to have costs that add up to 100% of the cost. Run the query and see if it is faster. | |
| Jun 27, 2018 at 12:26 | comment | added | pacreely | @JayChen a 'clustered index seek' is not a bad thing, it's way better than a 'table scan'. The only additional overhead is on the update, it's worth testing the query to get the actual IO and TIME. A half-way option would be to keep the table as a Heap and create a Non-Clustered index on staffid. You'll be able to perform RID lookups and have less update overhead. Also, is the staffid column an NVARCHAR? | |
| Jun 27, 2018 at 11:48 | comment | added | Jay Chen | Hi pacreely, yes, my table does not have an index on StaffID. I have tried to index StaffID column and checked the execution plan, it seems the costing is worse than before, as it needs to do the 'clustered index update' (304%) and 'clustered index seek' (100%) AIK, indexing will slow down the update statement, is it a correct way to solve this problem? | |
| Jun 27, 2018 at 10:30 | comment | added | Tibor Karaszi | Good point about the index. Definitely check if there is an index on StaffID! | |
| Jun 27, 2018 at 9:15 | history | answered | pacreely | CC BY-SA 4.0 |