SQL Server Select Count READ_COMMITTED_SNAPSHOT QUESTION

I seem to be getting a lot of deadlocks when doing select count(*) on a particular table.
I have already changed all the required parameters and made them into row only locking.

I've also changed the database to use READ_COMMITTED_SNAPSHOT isolation,

However, it seems that using a select count(*) where column = ? on the table causes deadlocks or locks on the table.

Am I correct that the select count(*) should only be accessing intermediate rows? It doesn't seem that way and I'm still encountering deadlocks. Proper indexing would probably help,

The question is: Does SQL Server 2008 R2 place shared lock on a table during select count(*) even when read_committed_snapshot is set to on?

Be careful with READ_COMMITTED_SNAPSHOT: if you set it on, it can cause lots of subtle bugs.

Also READ_COMMITTED_SNAPSHOT is the default isolation level, which may be overridden by something. Run DBCC USEROPTIONS to determine the actual isolation level your select runs under.

I would explicitly SET TRANSACTION ISOLATION LEVEL SNAPSHOT right before your select. That way you will be sure your select never embraces in deadlocks, and you do not break any other code, like READ_COMMITTED_SNAPSHOT might.

StackExchange Database Administrators Q#31302, answer score: 2