Sometimes rapid code development doesn't always produce the most efficient code. Take the age old line of code SELECT COUNT(*) FROM MyTable. Obviously this will give you the row count for a table, but at what cost? Doing any SELECT * from a table will ultimately result in a table or clustered index scan.
USE AdventureWorksDW2012;
SELECT COUNT(*) FROM dbo.FactProductInventory;
GO
Turning on STATISTICS IO on reveals 5753 logical reads just to return the row count of 776286.
Table 'FactProductInventory'. Scan count 1, logical reads 5753, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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.
USE AdventureWorksDW2012;
SELECT
s.name AS 'SchemaName'
,o.name AS 'TableName'
,SUM(p.row_count) AS 'RowCount'
FROM sys.dm_db_partition_statsp
JOIN sys.objects o ON o.object_id = p.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE p.index_id <2 AND o.type = 'U'
AND s.name = 'dbo'
AND o.name = 'FactProductInventory'
GROUP BY s.name,o.name
ORDER BY s.name,o.name;
GO
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 776286, and the FactProductInventory table is nowhere in our execution plan.
Table 'sysidxstats'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 0, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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.
This is just one simple example of how you can easily improve the performance of an application.
