Confused execution plan

Question

Confused execution plan

Viewing 8 posts - 16 through 22 (of 22 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Edward-445599 (5/11/2011)
--table stats
IX_TableA_idMay 11 2011 12:29AM314984882425471930.801755111.44844YES

The entire statistics output please. Dump it into a spreadsheet and attach it if it's too large.

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

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 2

Edward-445599

Hall of Fame

Points: 3147

May 11, 2011 at 4:27 am

#1322843

sorry here you go

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

Thanks, I'll take a look later.

There's something wrong with the cardinality estimation, on SP4 the estimated rows are in the hundreds of thousands, while actual rows is 1. This mis-estimate causes the optimiser to think that scanning the primary key index and doing a join is better than doing key lookups.

Are you interested in root cause or a fix?

If a fix, add the primary key column as an include in the index on ID. It's not included automatically, as it's not the clustered index. That will fix this completely.

If I may ask, why is a numeric ID being stored as a varchar(15)? That may well be exasperating the problem. If it's always numeric, the column should be int or bigint.

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

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 4

wow thanks so much very interesting, I will look into that more deeply, I was just lucky to pick that query up my fear is that other queries are being affected.

This is a fairly old but huge DB (2TB). A total rewrite is planed but for now we are only doing basic maintenance

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 5

GilaMonster (5/11/2011)
Thanks, I'll take a look later.
There's something wrong with the cardinality estimation, on SP4 the estimated rows are in the hundreds of thousands, while actual rows is 1. This mis-estimate causes the optimiser to think that scanning the primary key index and doing a join is better than doing key lookups.

Hi just been trying to get some literature on this, I am unable to find stuff do you have any links for further reading?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

There used to be a free download version of this but I can't find it atm...

http://www.red-gate.com/our-company/about/book-store/sql-server-execution-plans

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 7

Ninja's_RGR'us (5/11/2011)
There used to be a free download version of this but I can't find it atm...
http://www.red-gate.com/our-company/about/book-store/sql-server-execution-plans

thanks looks like a good book I might get it , but I meant more information specificly to the problem with SP4

"There's something wrong with the cardinality estimation, on SP4 the estimated rows are in the hundreds of thousands, while actual rows is 1. This mis-estimate causes the optimiser to think that scanning the primary key index and doing a join is better than doing key lookups."

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

I don't know that any book has ever been written to adress only or such a specific issue.

The book I had in mind with grant was just solid info about anything optimisation and plans. That should get you enough knowledge to fill in the gaps and google the rest.