[dba-SQLServer] Does count(*) lock the table?

jwcolby jwcolby at colbyconsulting.com
Wed Dec 7 10:00:52 CST 2011

I have a half dozen tables which I need to get counts of valid records for.  Additionally I need to 
get counts where the two tables join on a common field - HashPerson, HashFamily and HashAddress.

We have written a process in C# which dynamically generates these count SQL statements, passes it 
off to SQL Server, gets the results back and populates a spreadsheet with the answers.  As it 
happens there is an entire "table" or square area of the spreadsheet for the intersection (as I call 
it) counts for each of those hashes, IOW an entire section for HashPerson counts, another for 
HashFamily and another for hashAddress.

Since I am working in C# and each Hash area is a class instance, and since I have threads available 
in C#, and since it was taking about 5 hours to run all these counts, I decided to thread it and run 
each Hash on it's own thread, IOW try and get the counts for the HashPerson, HashFamily and 
HashAddress "simultaneously".  I have 12 cores and about 50 gigs of memory assigned to SQL Server so 
I thought *maybe* it would work.

Well... it appears that at some point very early on, something locks the process and only one of the 
hash classes proceeds.  When that one finishes then the other two process together.

We are checking whether we are having deadlock issues in C# but I need to know whether SQL Server 
itself would lock the tables that the count was processing.

So the question is, does SQL Server apply locks to a table when doing a count of the records in the 
table.  Likewise if I join two tables and do a count on that, does that cause a lock to be applied 
to either or both of the tables.

If so is there a syntax I can use to prevent this lock?  I am doing a join potentially on two tables 
with as many as 50 or 100 million records in each table.  These counts take a long time so having 
table locks applied is not a good idea in general.

John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

More information about the dba-SQLServer mailing list