Post reply

Query Performance Diagnostics with sp_PerfSQ

  • December 2, 2019 at 12:00 am

    #3700747

    Comments posted to this topic are about the item Query Performance Diagnostics with sp_PerfSQ

  • December 2, 2019 at 5:53 pm

    #3703548

    Can you explain how is the figure showing in cardinality is worked out?

    Thanks

    Carolyn

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • December 4, 2019 at 3:01 am

    #3704104

    The Cardinality column represents the number of rows SQL Server estimates will be returned by a query. SQL Server xquery is used to access the estimated query plan in cache and return the value of StatementEstRows:

    (SELECT CONVERT(float,MAX(s.value('@StatementEstRows', 'float')))

    FROM qp.query_plan.nodes('//StmtSimple') stmt(s))

    If the batch includes multiple queries the maximum value of StatementEstRows is returned, meaning Cardinality will identify the maximum estimated rows expected to be returned by any query in the batch making it less useful in these scenarios. Whether the batch includes one or multiple queries Cardinality must be treated as an estimate only. I have deliberated on whether to include this column on multiple occasions but I find myself still referring to it when examining why a query might exhibit a large memory grant (for example).

Viewing 3 posts - 1 through 2 (of 2 total)

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