Post reply

Large differences between estimated and actual row counts but stats are OK

  • November 27, 2015 at 10:21 am

    #302550

    I'm having a problem with a Function which is taking 10 times longer to run than it used to and high CPU usage. And I'm puzzled by what I'm seeing in the execution plan with some index seek operators showing large discrepancies between actual and estimated row counts. For example an actual row count of 61 and an estimated row count of 269,445.

    When I looked at the stats for the index associated with the highest-cost operator in the plan, its sampled rows count was about 10th of the actual rows so I ran a Full Scan. However that's made no difference. Now sampled rows and actual rows are the same but I'm still seeing large discrepancies between actual and estimated row counts for the same index. I was wondering if the high estimate was causing the plan to request a much higher memory grant than was needed however I can't understand where this high estimate is coming from so I was hoping someone might point me in the right direction.

    Gordon.

  • November 30, 2015 at 10:17 am

    #1842944

    If we don't have schema information or indexing, a query or proc or something that will give us a hint as to what type of function you are using and why it is a problem, makes it hard for many of us enthusiastic helpers to help.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • November 30, 2015 at 10:24 am

    #1842946

    Check this article for better (or actual) help:

    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • December 2, 2015 at 10:03 am

    #1843571

    Apologies for the delay in responding.

    This is a reasonably complex Scalar-valued function returning XML and generates a fairly large Execution Plan. However there's definitely something screwy about the Estimated Row Count returned by SQLSentry Plan Explorer since those huge values don't occur when looking at it in SSMS. However I was probably missing the wood for the trees anyway since the operator that had been 'red-flagged' had both a Seek Predicate and Predicate. However I'm not understanding the latter since the Predicate was associated with an equality comparison of a column that is a foreign key (and a member of at least 6 indexes) and a parameter value:

    Predicate:

    [DB].[dbo].[SurveyItem].[SurveyItemId]=[@SurveyId]

    So I can't see why this isn't a seek predicate.

  • December 2, 2015 at 10:24 am

    #1843581

    Scalar UDFs have problems with plan/row estimates. I'll try to avoid them at all costs. Maybe this can give you an idea on how to improve it: http://www.sqlservercentral.com/articles/T-SQL/91724/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • December 2, 2015 at 10:27 am

    #1843583

    Thanks Luis, I'll check out the article.

Viewing 6 posts - 1 through 5 (of 5 total)

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