We aren’t looking for accuracy, so this works fine for us. The NOLOCKs helped e.g. when indexes were in the process of creating.
Fast way to count large tables.
SELECT
SCHEMA_NAME([Tables].[schema_id]) AS [SchemaName],
[Tables].[name] AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM [sys].[tables] AS [Tables] WITH (NOLOCK)
JOIN [sys].[partitions] AS [Partitions] WITH (NOLOCK)
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].[index_id] IN (
0, 1
)
WHERE [Tables].name = N'TRANSACTIONS'
-- and [Partitions].[rows] > 0
GROUP BY SCHEMA_NAME([Tables].[schema_id]),
[Tables].[name];