Query Performance Diagnostics with sp_PerfSQ

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

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



    Facts are stubborn things, but statistics are more pliable - Mark Twain

  • 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