July 31, 2017 at 8:41 am
I would swear that at some point, I read here on SSC that the row_count column in sys.dm_db_partition_stats is 100% accurate now. I was so confident of that I've also been posting that as the truth on various posts I've made.
I remember the "old days" of SQL2005 and previous, where syspartitions.rows needed to have DBCC UPDATEUSAGE(database-name) in order to make sure the row counts in that sys view matched actual row counts.
As a result, I changed all my scripts that check row counts to use the new dynamic management view.
today, I identified that some tables actual row counts do not match, regardless of whether I run DBCC UPDATEUSAGE on the database, or on the individual table.
my differences run anywhere from 5K to 12K rows for a handful of tables(7) out of 737 total tables
I do not have any open transactions; so nothing could be inserting rows, but awaiting committing transactions.
The server I'm using is SQL2016 SP1.
SQLVersion ProductVersion EditionID EngineEdition MachineName ProductLevel
SQL 2016 13.0.4199.0 1872460670 3 SUNPRDCL01 SP1
my code snippet for comparison is straight forward: a cursor to RBAR some row counts, which is then compared to partition stats.
here's my code, do you see the same behaviour on an active database?
IF OBJECT_ID('tempdb.[dbo].[#Results]') IS NOT NULL
DROP TABLE [dbo].[#Results];
CREATE TABLE [dbo].[#Results] (
[SchemaName] VARCHAR(128) NOT NULL,
[ObjectName] VARCHAR(128) NOT NULL,
[TotRowCount] BIGINT NULL);
DECLARE @isql VARCHAR(8000);
DECLARE c1 CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
--###############################################################################################
--cursor definition
--###############################################################################################
SELECT 'INSERT INTO [#Results]([SchemaName],[ObjectName],[TotRowCount])
SELECT ''' + SCHEMA_NAME(SCHEMA_ID) +''' AS SchemaName, ''' + name + ''' AS ObjectName, count(*) As TotRowCount
FROM ' + QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) + '.' + QUOTENAME(name) + ';'
FROM sys.tables;
--###############################################################################################
OPEN c1;
FETCH NEXT FROM c1 INTO @isql;
WHILE @@fetch_status <> -1
BEGIN
--PRINT @isql;
EXECUTE(@isql);
FETCH NEXT FROM c1 INTO @isql;
END;
CLOSE c1;
DEALLOCATE c1;
;WITH IndexCounts
AS
(
SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) +'.' + QUOTENAME(o.name) AS QualifiedObjectName,
SCHEMA_NAME(o.schema_id) AS SchemaName,
o.name AS ObjectName,
SUM(ps.row_count) AS [TotRowCount],
'SELECT * FROM '
+ QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.'
+ QUOTENAME(o.name) AS cmd
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ps
ON i.OBJECT_ID = ps.OBJECT_ID
AND i.index_id = ps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
GROUP BY
SCHEMA_NAME(o.schema_id),
o.name
)
SELECT T1.[TotRowCount] - T2.[TotRowCount] AS PartitionCount_Minus_RowCount,*
FROM IndexCounts T1
INNER JOIN [#Results] T2
ON T1.[SchemaName]=T2.[SchemaName]
AND T1.[ObjectName] = T2.[ObjectName]
WHERE T1.[TotRowCount] <> T2.[TotRowCount]
Lowell
July 31, 2017 at 10:05 am
OK answering my own question, my assumption was wrong. the count is an approximation. it is not 100% accurate.
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-partition-stats-transact-sql
row_count bigint The approximate number of rows in the partition.
and another post by Erland Sommarskog emphasizes that running dbcc updateusage may work. With emphasis on may. no guarantees.
Well, now I know!
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8ed9cbad-2745-457e-ab95-3fc5fdd423f2/row-count-problem-with-sysdmdbpartitionstats?forum=transactsql
Lowell
August 1, 2017 at 6:20 am
Something additional I'm noticing: for the seven tables that have inaccurate row counts when compared to actual counts,
Those are tables that are populated with a TSQL MERGE statement from a staging table.
That leads me to believe that the MERGE statement changes don't affect sys.partition_stats the same way a plain old set of INSERT/UPDATE/DELETE statements would.
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply