Does running a COUNT() statement using NOCOUNT ON; consume much

Question

On millions of rows.. Is it worth caching the total amount or turning NOCOUNT OFF; instead of requesting it each time on that much data?

Sql Server 2008 R2

Also, is running a COUNT() statement on the Primary_Key only, increase performance?

Answer 1

NOCOUNT does not mean "don't count" - it just means "don't report the count." Reporting can result in a lot of unnecessary chatter between server and client (which is usually ignored), and can incorrectly be interpreted as a result set by some APIs. At my previous jobs one of our coding policies was that SET NOCOUNT ON was required in every procedure, and I still promote it as a best practice. There are some APIs that rely on this message, however, and in fact your own .NET code may be relying on .RecordsAffected today.

As for the count, you can use sys.dm_db_partition_stats but be aware that this does not guarantee 100% accuracy - like taking a direct count with NOLOCK, it will not account for transactions in progress.

I also suggest a couple of improvements on @garik's answer:

SELECT 
    sch = OBJECT_SCHEMA_NAME(i),
    obj = OBJECT_NAME(i),
    [RowCount] = rc
FROM 
(
    SELECT 
        i = [object_id],
        rc = SUM(row_count)
    FROM sys.dm_db_partition_stats
    WHERE index_id IN (0,1)
    GROUP BY [object_id]
) AS x
ORDER BY [RowCount] DESC;

This will take partitioning into account, includes the schema as well as object name, and identifies heaps and clustered indexes explicitly (who knows when Microsoft will start giving hypothetical indexes or other system-defined indexes negative index_id values).

EDIT:

As for the question about counting on a primary key, you will see no difference in performance between COUNT(*) and COUNT(key_column) - if you inspect the plans, they should be identical. Be careful, though, about using COUNT(nullable_column) - personally I think COUNT(*) is safer. That said, for millions of rows, it is quite rarely a realistic requirement to get a 100% accurate count. Retrieving the row counts from the DMV above should be more than adequate and doesn't have a chance of impacting the table itself (or being slowed down by operations on the table).

Answer 2

SET NOCOUNT ON is only for the xx rows affected that can affect client-server performance and breaks a surprising amount of code. See this on SO: http://stackoverflow.com/questions/1483732/set-nocount-on-usage

COUNT(*) vs COUNT(PK) was discussed here: What is the difference between select count(*) and select count(any_non_null_col)? Summary: COUNT(*) is different to COUNT(pk) as per the ANSI standard. In practice they are the same but COUNT(PK) is a myth

Answer 3

Returns row count for every table without table scan:

SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC