Starting with SQL Server 2005, Microsoft introduced a DMV, sys.dm_db_partition_stats, that provides you with the same information at a fraction of the cost. It requires a little more coding, but once you turn on STATISTICS IO, you will see the performance benefit.

Since we’re querying a DMV, we never touch the base table. We can see here we only need 16 logical reads to return the same row count of 1,133,242, and the tblERPInvoiceData table is nowhere in our execution plan.

By using the DMV, we have improved the query performance and reduced the total I/O count by nearly 100%. Another added benefit of using the DMV, is we won’t need locks on the base table and therefore will avoid the possibility of blocking other queries hitting that table.