Output 1 Record from 12 million records table ?

Question

Post reply

Output 1 Record from 12 million records table ?

curious_sqldba

SSC-Dedicated

Points: 36502
More actions
February 11, 2011 at 4:49 pm

#234349

i have a table which has 12 million records. In the execution plan , only one record is sent. There is an nc index which is doing seek. However i see that estimated I/O cost is 11 ? Is this an issue. This contributes to 72 % of the cost. Is there any room to tune this?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 1

Can you post the execution plan? Perhaps the index is not covering?

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 2

sqldba_icon (2/11/2011)
i have a table which has 12 million records. In the execution plan , only one record is sent. There is an nc index which is doing seek. However i see that estimated I/O cost is 11 ? Is this an issue. This contributes to 72 % of the cost. Is there any room to tune this?

The execution plan frequently lies. I've seen steps that say they going to take 12,000%, for example. Also, if the query runs in milliseconds, what does 72% of a handful of milliseconds end up being? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Is the query slow? IF not, why are you even worrying?

The costs are estimates, they can be wrong. Even if they're right, the total cost % of a plan has to add up to 100%. If it's a simple plan, there will be a single high % operator. There has to be.

Don't focus on costs and % unless you know (from testing for durations, reads and CPU) that there really is a problem.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass