3

I have a problem where the counts returned by the RowCounts column in a SP:StmtCompleted are different from the actual rows returned for execution of a select statement.

I have tried a select @@RowCount after the select query and it returns the same counts as count(*). I'm curious now what could be causing the profiler to return incorrect row counts.

I did read related threads suggesting fragmentation can yield wrong counts and a DBCC UPDATEUSAGE might solve the issue, but I want to understand what I'm doing before applying this. I would appreciate some elaboration. Also, do indexes play a role in the counts?

Related Q & A: SQL Server Row Count sp_spaceused rows discrepancy

I am using SQL Server 2014 Profiler.

This is how you can see row counts in Profiler, in case any one is wondering: Show Row Count in SQL Profiler

6

Generally, you can expect that the row count reported will correspond to the number of rows returned to the caller (and to the (x row(s) affected) message in SQL Server Management Studio in the case of a top-level query).

However, where the statement contains nested calls, such as to a scalar user-defined function, row counts generated in the (repeated) calls to the function's execution plan will be added to the total. This is an unfortunate side-effect of where the counting is done*, and the way non-inline functions are currently implemented. It has been considered not important enough to fix.

In the linked Connect item example (better formatted in the parent blog post by Guy Glanster), the total row count for the query that returns 100 rows is reported as 300, due to the single row affected by each of the two statements in the function, on each invocation.

If the function is reduced to a single statement:

RETURN CAST(@DateTimeValue AS date);

...the count is 200 (100 rows for the outer query, plus 1 row a hundred times for the function).

DBCC UPDATEUSAGE will not affect this. Indexes and fragmentation have no bearing either. It is simply an architectural consequence, and another reason to avoid non-inline functions.


* The details are rather esoteric. Broadly, for a SELECT query, it is the number of rows seen at the root node of the plan. There is a shared memory structure involved, and nothing you can see directly in an execution plan. When a scalar function executes, it does so in a subcontext that happens to share the structure. Perhaps this is by design, it is hard to know for sure without source code access. My feeling (as stated) is that it is an unintended side effect. For DML queries, a particular plan node is assigned the job of maintaining the rows-affected count. This can differ significantly depending on the shape of the plan (for example if an OUTPUT clause is used, or Split-Collapse update processing is employed).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.