locks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

locks

Author

Topic

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2012-07-27 : 11:31:43

Hi,

I have locking issue on one table.
When I issue select count(*) from tablename with (nolock) it works. But when I issue same query without nolock it never finish. I run sp_who2 and activity monitor there are some process that create locks. How to overcome this issue?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 11:46:46

one alternative is to use snapshot isolation mode

http://visakhm.blogspot.com/2010/02/avoiding-deadlocks-using-new.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-27 : 12:26:54

If you are just looking for row count in the table, you could do this:

SELECT  row_count
FROM    sys.dm_db_partition_stats
WHERE   OBJECT_NAME(OBJECT_ID) = 'YourTableName';

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources