Why does the Clustered Index Seek take 45% of plan?

farushaik 2016-11-29 12:21:26

I'm trying to understand why this clustered index seek at Object15.Index7 is consuimg 45% of execution plan. I've another Non clustered index with objects but the execution plan is not using that, instead going for this seek. Could someone please explain?

Thanks

/storage/attachments/1800-Plan_Anonymized.pesession
Aaron Bertrand 2017-01-08 17:01:22
You've anonymized the statement away, which makes it harder to determine why the clustered index seek has been chosen (or whether a different index might have been more appropriate; another index might have been more expensive depending on the keys, the query, and whether lookups would be required). You've also opened a plan that was generated in Management Studio. Can you please upload a new actual plan generated from within the latest version of Plan Explorer?
Rob Farley 2017-01-09 07:32:26
Hi Farushaik,

Sorry it's taken so long to get an answer back to you…

The Clustered Index Seek is searching for rows according to Column4, which must be the Clustered Index value. This is actually pretty efficient. You could make a nonclustered index to achieve the same, by indexing Column4 (making it a unique index) and making sure that Column93 and Column94 were both 'included columns', but there's very little to gain from this.

The reason why it's estimated (remember that the costs in a sqlplan are only estimates based on IO) to take 45% of the plan is because it's having to do that Seek over half a million times! It's probably an estimated 45% of the logical IO, but probably not anywhere close to 45% of the CPU or Time effort in this query.

Doing half a million seeks on a table is not necessarily that bad. It's really not. I've seen plenty of examples where despite the Estimated Cost of that operator being high, it's actually a remarkably efficient query. I once tuned a query that took over a minute with 300,000 reads down to a query that took just a few seconds with nearly 10,000,000 reads – because the data in the table with the Seeks was quickly loaded into RAM and then the same data was read over and over again.

For tuning this query, I would actually be more concerned about the two Hash Matches on Object14 and Object10, which are large – 604MB and 796MB respectively.

Rob

November 29, 2016
No Comments