September 18, 2002 at 1:44 am
If your SELECT statement is non-critical, for example a web page that refreshes regularly so bad data won't matter, use the WITH (NOLOCK) hint after your table object name:
SELECT name FROM db.dbo.foo WITH (NOLOCK) WHERE state='CA'
This tells SQL to ignore locks placed on the table or rows, but you might get dirty data which is about to be updated, hence my first comment. If the SELECT is critical for accuracy, then there is little you can do, except make sure it is as specific as possible, i.e. consider a covering index.
Simon
September 18, 2002 at 1:46 am
R u using transactions?
It happens because of transactions sometimes, You need to commit such transactions to avoid locks.
if you don't mind selecting non committed data then you can use
select * from table with (nolock)
clause to avoid locks.
One more thing, sequence of accessing tables in the transactions should be same.
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply