Both XEvents and SQL Trace include events that fire at the completion of a statement and at the completion of a batch. For XEvents, they are sqlserver.sql_statement_completed and sqlserver.sql_batch_completed, and for SQL Trace they are SQL:StmtCompleted and SQL:BatchCompleted. When troubleshooting a DML query recently, I noticed that the row count reported in the sql_batch_completed event was greater than the row count reported in the sql_statement_completed event by one or two rows. Now, in general, this is not necessarily surprising. If a batch includes multiple statements, the row counts for all statements are added up and the sum is reported at the batch level. Sometimes, the fact that the batch contains multiple statements may be less than obvious. For example, if a DML statement targets a table with an AFTER trigger, the row counts from statements in the trigger will be added to the batch level row count. In my case, however, the batch contained a single UPDATE statement, and the target table did not have any triggers.
I began to suspect a bug in SQL Server, when my colleague Nacho Alonso Portillo (blog) pointed out that there are actually additional statements in the batch. These additional statements are SELECT queries executed internally by SQL Server when during query optimization, it finds that statistics are missing or out of date. These autostats queries look similar to the one below, and can be seen in either XEvents and SQL Trace sessions if the sqlserver.sp_statement_completed or SP:StmtCompleted events are included.
SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [ColumnName] AS [SC0] FROM [dbo].[TableName] WITH (READUNCOMMITTED) ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL
The number of rows produced by these queries will be added to the batch level row count.
While this behavior is by design, the fact that the batch level row count includes row counts from internal SQL Server queries may be unexpected. This does not seem to be documented or mentioned anywhere (please add a comment below otherwise). Hopefully this blog will help others who may be puzzled by the discrepancy.
© 2019 Microsoft. All rights reserved.