Getting accurate row count for a table

Question

Getting accurate row count for a table

md23

Old Hand

Points: 352
More actions
September 8, 2011 at 9:08 am

#247525

I've found a lot of conflicting information about the accuracy of different methods for obtaining a table's row count, specifically sys.dm_db_partition_stats. I need to get a count for the size of all tables within a database that contains MANY tables, and I'd like to make that process as quick as possible. I'm on SQL 2008. We did upgrade from SQL 2000, but I ran a DBCC UPDATEUSAGE during the upgrade process.
My understanding is that a SELECT COUNT(*) FROM MyTable will perform a table scan to obtain the current count of committed rows in the table, but that method is very slow when I have to iterate over all the tables in the DB. A set-based approach makes way more sense here.
For instance:
SELECT object_id,
SUM(ps.row_count)
FROM sys.dm_db_partition_stats
WHERE index_id = 0
OR index_id = 1
GROUP BY object_id
How does sys.dm_db_partition_stats compare? Are there any instances where the DMV would not be accurate for all committed transactions?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

I believe it's accurate, but not transactionally consistent, so you could potentially see counts that were later rolled back.

In reality, I don't see many cases where that would present a problem. What are you using the counts for that requires them to be so accurate to a point in time?

md23 Old Hand Points: 352 More actions · Answer 2

HowardW (9/9/2011)
What are you using the counts for that requires them to be so accurate to a point in time?

It's record counts for customer data imports. Truthfully it's probably OK if they aren't accurate, but it's the type of thing that inevitably leads to support requests when the record count doesn't match the customer's expectation.

If the DMV is correct other than current transactions, that's probably "good enough", and any errors would likely be corrected on the next run of the query.

AluburaJ Old Hand Points: 366 More actions · Answer 3

USE Database_name

GO

SP_SPACEUSED 'yourschema.yourtablename'

GO

The above gives the number of rows and other information regarding the table. This is the faster way to see the number of commited rows in a table.

LutzM SSC Guru Points: 107049 More actions · Answer 4

mysearchresult (9/9/2011)
USE Database_name
GO
SP_SPACEUSED 'yourschema.yourtablename'
GO
The above gives the number of rows and other information regarding the table. This is the faster way to see the number of commited rows in a table.

Did you check what SP_SPACEUSED is using to calculate the number of rows? (sys.dm_db_partition_stats, as above)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

md23 Old Hand Points: 352 More actions · Answer 5

mysearchresult (9/9/2011)
USE Database_name
GO
SP_SPACEUSED 'yourschema.yourtablename'
GO
The above gives the number of rows and other information regarding the table. This is the faster way to see the number of commited rows in a table.

In my case I'm looking to get the rowcount for many tables at once, so the DMV is actually more efficient since I don't have to call the stored proc over and over again.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

md23 (9/9/2011)
mysearchresult (9/9/2011)
USE Database_name
GO
SP_SPACEUSED 'yourschema.yourtablename'
GO
The above gives the number of rows and other information regarding the table. This is the faster way to see the number of commited rows in a table.
In my case I'm looking to get the rowcount for many tables at once, so the DMV is actually more efficient since I don't have to call the stored proc over and over again.

Ok, Only 1 call to a sp (not the best way to go about it, but still fun to know) =>

SET STATISTICS IO, TIME OFF

SET NOCOUNT ON

SET IMPLICIT_TRANSACTIONS ON

GO

USE [master]

GO

IF OBJECT_ID('dbo.spaceused', 'U') IS NULL

BEGIN

CREATE TABLE dbo.spaceused (

DbName sysname DEFAULT(''),

tblName sysname,

Row_count INT ,

Reserved VARCHAR(50),

data VARCHAR(50) ,

index_size VARCHAR(50),

unused VARCHAR(50),

PRIMARY KEY CLUSTERED (DbName, tblName)

);

END

ELSE

BEGIN

--DROP TABLE dbo.spaceused

TRUNCATE TABLE dbo.spaceused

END

COMMIT

GO

DECLARE @Cmd VARCHAR(8000)

SET @Cmd = 'USE [?];

IF ''?'' NOT IN (''tempdb''

--, ''master'', ''model'', ''msdb''

)

BEGIN

--PRINT ''?''

DECLARE @InnerCmd VARCHAR(8000)

SET @InnerCmd = ''

EXEC sp_spaceused '''''' + CHAR(63) + ''''''''

INSERT INTO master.dbo.spaceused(tblName, Row_count,Reserved,data,index_size,unused)

EXEC sp_MSforeachtable @InnerCmd

UPDATE master.dbo.spaceused SET DbName = ''?'' WHERE DbName = ''''

END

'

--PRINT @Cmd

EXEC sp_MSforeachdb @Cmd

DELETE FROM dbo.spaceused WHERE Row_count = 0

SELECT

DbName

, tblName

, Row_count

, CONVERT(BIGINT, REPLACE(Reserved, ' KB', '')) / 1024 AS MB_Reserved

, CONVERT(BIGINT, REPLACE(data, ' KB', '')) / 1024 AS MB_data

, CONVERT(BIGINT, REPLACE(index_size, ' KB', '')) / 1024 AS MB_index_size

, CONVERT(BIGINT, REPLACE(unused, ' KB', '')) / 1024 AS MB_unused

FROM

dbo.spaceused

ORDER BY

DbName

, MB_Reserved DESC

, Row_count DESC

COMMIT