Help with query optimization

Aircan 2013-10-10 23:42:10

Is there anything I can do with this. It's a pretty big query. I'm concerned about the 21.6% Index Seek (Table23.Index54)

Dan Holmes 2013-10-11 12:34:13
It runs in under a second. How much faster do you need it to be?

The index seek looks bad because the estimates are way off on that node. It was expecting 26k rows and actually had 349. The costs on the node are the estimates, even on an actual plan.

There are places where table2 is being scanned multiple times in the same branch but on different indexes. Perhaps on index that combines index20 and index46 by adding column45 would help. You find be able to do the same with table7's 53 and 43 indexes. Without the DDL i can't say how much that would help the read count, which is already low in my opinion.

Autochartist 2013-10-11 13:27:39
Just a quick observation:

You have a whole bunch of clustered index scans on various tables, indicating that you have no useful index available on those tables that the query can make use of.

Regarding your concern, the index seek cost of 4.288 is likely much higher than its real cost (as it's pulling 349 rows, vs the 26k estimated). As Dan pointed out, it's an estimate only, it's not the actual cost.
Perhaps updating statistics on this table might help with bringing the cost estimation closer to reality?