When Row Counts in SQL Server Go Bad
When it comes to getting table row counts in SQL Server, there are a few different options available.1 Some are more accurate than others and it turns out that others can be even less accurate after an upgrade.
As part of a particular ETL process, I’ve got a row count check at the end of my data transfer that verifies that the Source and Target row counts are equal. Some of my tables have almost one billion rows, and running a SELECT COUNT(*) on every table was taking too long (not to mention the potential for locks). The databases I’m working with are not OLTP databases, so there are no writes going on other than my ETL. Microsoft does say that sys.partitions.rows provides an approximate number of rows, but up until now, the following query has gotten me accurate row counts when used at the end of my ETL process:
SELECT ta.name as TableName, SUM(Pa.rows) as RowCnt
FROM
sys.tables ta INNER JOIN
sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
WHERE
ta.is_ms_shipped = 0 AND
pa.index_id IN (1,0)
GROUP BY ta.name
This week, I upgraded to SQL 2016 and all of the sudden the same query on the same tables at the same time with the same indexes started giving me incorrect row counts in about 10% of my tables.
MSDN Documentation to the Rescue?
According to the MSDN page, DBCC UPDATEUSAGE “reports and corrects pages and row count inaccuracies in the catalog views”. So I ran DBCC UPDATEUSAGE. But still no match on my row counts.
After a bit more digging… I found the critical piece that I was missing. Apparently, though I don’t feel Microsoft does a great job of making this clear, DBCC UPDATEUSAGE only updates the page inaccuracies – ir doesn’t fix row count inaccuracies unless you specify the “WITH COUNT_ROWS” argument.
So I ran DBCC UPDATEUSAGE(myDBName) WITH COUNT_ROWS against my newly upgraded database and everything was right in the world. Well, Trump is still running for President, so I suppose it didn’t fix everything, but my row counts are accurate once again, so I’ll settle for that… for now.
1. Jes Borland (@grrl_geek) wrote a great article on those here, so I won’t go into those details. ↩