How SQL Server Profiler Calculates RowCounts

profilersql serversql server 2014

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?

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

Best Answer

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).

Related Solutions

Sql-server – SQL Server Table Size Discrepancy

When you delete from heaps, allocated space may not be unallocated unless you use a table lock. See "Deleting Rows from a Heap" from DELETE on MSDN

This is a separate problem to fragmentation (which happens of course)

Just in case, have you tried this to update usage info?

EXEC sp_spaceused 'MySchema.MyTable', 'true'

With no indexes or keys, you can't defragment it normally.
You can add/drop a clustered index, or do something like this

SELECT * INTO [MySchema].[MyTableNew] 
FROM [MySchema].[MyTable]
-- ORDER BY something

-- if you like
EXEC sp_spaceused 'MySchema.MyTableNew', 'true'

DROP TABLE [MySchema].[MyTable];

EXEC sp_rename 'MySchema.MyTableNew', 'MyTable';

oops: just seen updates, my Internets has been broken on and off...

SQL Server Row Count – sp_spaceused Rows Discrepancy

Yes, this can potentially affect plan choice. Does running DBCC UPDATEUSAGE make everything right again? e.g.

DBCC UPDATEUSAGE('yourdb', 'dbo.tblEMPLOYEES_MASTER');

Make everything right again? That doc says:

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

Best Answer

Related Solutions

Sql-server – SQL Server Table Size Discrepancy

SQL Server Row Count – sp_spaceused Rows Discrepancy

Related Question