Query Performance Diagnostics with sp_PerfSQ

  • Edward Haynes

    SSC Veteran

    Points: 294

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

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8300

    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]

  • Edward Haynes

    SSC Veteran

    Points: 294

    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 3 (of 3 total)

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