Row count in big tables with DMV DM_DB_Partition_Stats and SP_SpaceUsed

Friday, February 19, 2010 |

I have seen people counting row by using COUNT or may be COUNT_BIG, if it is very big table, aggregate function. When you go for COUNT function, you will scan full table and it may take very big amount of time, especially when you are having VERY BIG table with few millions of rows. So .NET developer also uses same COUNT aggregate function in .NET front end application which is really very time consuming.

I just see one of my .NET developers was doing this for the table, having 150 million rows so I corrected him with following two ways and thought to share it with my blog readers.

Rather than scanning full table I always prefer to use either DMV or SP_SpaceUsed stored procedure. In my early days few years back, I was using SP_MSTABLESPACE but in SQL Server 2005+, SP_MSTABLESPACE is just for backward compatibility so I do not recommend using SP_MSTABLESPACE and promoting dm_db_partition_stats and SP_Spaceused.

Let us see  TSQL for each.

--Method1
SELECT
SUM(row_count) as TotRows
FROM
sys.dm_db_partition_stats
WHERE
object_name(object_id) = 'YourTableName'
AND (Index_Id = 1)
GO

--Method2
SP_Spaceused 'YourTableName'
GO

--Method3
SP_MSTABLESPACE 'YourTableName'
GO


Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

0 comments: