Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
@Wilkins if you will create a registered account here, I'll merge this question with your account. –  jcolebrand Sep 1 '11 at 21:55
    
@jcolebrand, Wilkins - Is this not the same Wilkins –  Jack Douglas Sep 5 '11 at 7:10
    
@JackDouglas that is an unregistered account, I can't merge reasonably, so I won't at all. –  jcolebrand Sep 6 '11 at 1:30
add comment

migrated from serverfault.com Sep 1 '11 at 10:07

This question came from our site for professional system and network administrators.

3 Answers

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).

share|improve this answer
    
One addition that's worth adding in, a count at read committed is almost as inaccurate as read uncommitted. –  Mark Storey-Smith Sep 1 '11 at 12:59
    
@Mark true, I was just using NOLOCK as a common example of something folks do to "speed up" a query. –  Aaron Bertrand Sep 1 '11 at 13:24
add comment

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

share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.