Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2007-07-12 : 02:08:00
|
| Looking at the execution plan of a query that is performing to slow, I have noticed one key lookup costing 72%.Looking at it closer, I see I'm joining on a nullable column (that is mostly null) with an index on it. But it chooses instead the clustered index on one of the other columns in the table that is non-nullable and highly selective.What can I do about this? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-12 : 02:10:55
|
| Hard to tell without seeing your query.Maybe putting an extra WHERE clause of [pk-col] > 0 will force a index seek?Peter LarssonHelsingborg, Sweden |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-07-12 : 03:27:02
|
| I'm not sure in this case how seeing the query will help?Thanks for your suggestion, but my pk col is of guid type.Here are some stats:Estimated I/O cost: 0.003125Estimated CPU cost: 0.0001581Estimated Operator cost: 6.44866 (72%)Estimated Number of Rows: 1Estimated row size: 23BOrdered: TrueLong term, I think I will remove this nullable ParentCompany column and store the relationship in a link table. (Most companies don't have a parent company.)So SQL Server wont be able to reason that it should avoid the index because it contains mostly null values.But now I'd like to be able to test what the performance would be like if it chooses the non-clustered index that is built on the clustered index (instead of choosing the clustered index itself). That way, I can tell for sure if it is making the right decision. Can that be done? |
|
|
|
|
|
|
Query Hint?