Actual and Estimates That old conversation.

Question

Post reply

Actual and Estimates That old conversation.

Super Cat

SSCertifiable

Points: 7332
More actions
August 9, 2016 at 8:12 am

#329171

Hi All,
Apart from out of date stats, what can throw these 2 completely out of Sync.
Thanks
Tal.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Super Cat SSCertifiable Points: 7332 More actions · Answer 1

Super Cat

SSCertifiable

Points: 7332

August 9, 2016 at 8:13 am

#1894531

I'm talking about row estimates

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Overly complex queries where the optimizer doesn't have time to properly evaluate plans.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Data skew.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Talib123 (8/9/2016)
Hi All,
Apart from out of date stats, what can throw these 2 completely out of Sync.
Thanks
Tal.

Can you post the actual execution plan you are studying?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

A large number of things.

Lack of statistics

Conversions or functions on columns

Table variables (including multi-statement UDFs)

Variables in predicates

Plans being reused for executions with different parameters

Generalising queries (eg the Column = @Parameter OR @Parameter IS NULL)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Lynn Pettis (8/9/2016)
Overly complex queries where the optimizer doesn't have time to properly evaluate plans.

That'll get poor plans, not inaccurate row estimations.

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

Super Cat SSCertifiable Points: 7332 More actions · Answer 7

Thanks for taking the time all

Conversions or functions on columns; is the most likely with this piece of code.

This is what I thought but just needed to get a second opinion.

Again Thanks.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Talib123 (8/10/2016)
Thanks for taking the time all
Conversions or functions on columns; is the most likely with this piece of code.
This is what I thought but just needed to get a second opinion.
Again Thanks.

If you can post the actual execution plan (as opposed to the estimated plan), then you'd likely get a definitive answer, rather than guesses.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden