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

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

avatar image By farushaik 0 asked Nov 29, 2016 at 12:21 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Jan 08 at 05:01 PM

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?

10|10000 characters needed characters left

1 answer: sort voted first

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

avatar image By Rob Farley 181 answered Jan 09 at 07:32 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x600
x32
x23
x19
x4

asked: Nov 29, 2016 at 12:21 PM

Seen: 753 times

Last Updated: Jan 09 at 07:32 AM