Can We Get Row Counts After Execution?

The general idea for this question came from dba.stackexchange.com: could we, and if we can, how, get row counts after execution.

I was intrigued with the idea, so I ran some tests and did a little digging. I boiled it all down in the answer at the link, but I figured I could share a little here as well.

Properly Retrieve Row Counts After Execution

The right way to do this is obvious and simple. Before you need it, set up an Extended Events session. Done. The only question is what goes into the Session. First blush, sql_batch_completed and/or rpc_completed. Both will return a rows affected value. Although, interestingly, the row_count value is documented as rows returned. However, it’s both.

But, if you really want to get picky, batches and procedures frequently have multiple statements. This means that the row count you’re getting isn’t for a particular table. If you want to get that, then you need to capture the statement level stuff, sp_statement_completed or sql_statement_completed, depending on if you’re looking at procedures or batches.

However, this is one of those “be careful what you wish for” moments. Because, if you capture all statements in a system, you’re going to have a very large amount of data to deal with. This is where filtering is your friend.

Other Ways To Get Row Counts After Execution

Another way to get a precise count of rows affected after execution is if you can capture the query plan plus runtime metrics using sys.dm_exec_query_plan_stats. I’ve already talked about this cool new DMV here. The key to the use of it is of course, you have to immediately retrieve it, or the counts you’re looking at may not be the ones you want. So, this is after the fact, but only immediately after the fact.

Worth mentioning is using the Live Execution Plans, or more accurately, live query metrics. I’ve written about that before too. The easy way, enable Trace Flag 7412. The harder, but not hard, way, capture the query_thread_profile Extended Event. Either way, you can see live row counts for a given query.

You can also query the DMVs to see the row counts immediately after execution in last_rows. However, just like the other two methods mentioned here, it has to be immediate. You can’t stroll along later, when the query has executed more times and see the same row counts.

Estimated Row Counts After Execution

Beyond those mechanisms listed above, there’s now way to get accurate row counts after execution. Now we’re down to guesses recorded in execution plans. As I outlined in the question on StackExchange, you can get the plan from cache and it’ll show you an estimate. You can also get the plan from query store, same thing. I’m not saying these aren’t useful. They are. However, it’s just limited in what it can provide.

Further, you have to remember, what you’re looking at are the estimated row counts at the time of compile. That may not be accurate to either the real row counts, or, to the row counts at execution.

Conclusion

Short answer, if you really want to track row counts, set it up ahead of time with Extended Events. Just be sure to filter them appropriately. After the fact, getting the count of rows requires either immediacy, or the willingness to accept estimates.