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

Use: WITH (NOLOCK) like this:

SELECT COUNT(SomeField) FROM SomeTable WITH (NOLOCK)



On Wed, Dec 7, 2011 at 8:00 AM, jwcolby <jwcolby at colbyconsulting.com> wrote:

> 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
>
> ______________________________**_________________
> dba-SQLServer mailing list
> dba-SQLServer@**databaseadvisors.com <dba-SQLServer at databaseadvisors.com>
> http://databaseadvisors.com/**mailman/listinfo/dba-sqlserver<http://databaseadvisors.com/mailman/listinfo/dba-sqlserver>
> http://www.databaseadvisors.**com <http://www.databaseadvisors.com>
>
>