read-only query waiting on lock in Sql Server: turbo-boost technique

[FerencMantfeld]

Sometimes you will execute a simple read-only query in Sql Server that you KNOW should return almost instantaneously but you find that the query goes into 'executing' and stays stuck there until another process has finished the DML. Very annoying to put it mildly.

Using a simple directive to the optimizer, you can tell Sql Server that you are fine with a dirty read and that you want the results back before the other process has completed its task.

You can simply change your query to include ' with (NOLOCK)' after the tables / views declaration from something like this:


select RefreshTYpe, RefreshDate, RefreshSource
, RefreshTargetProcessed, RecordsProcessed
from RefreshLog
where RefreshDate >=dateadd(day,-1,getdate())
orderby RefreshDate desc

to this:


select RefreshTYpe, RefreshDate, RefreshSource
, RefreshTargetProcessed, RecordsProcessed
from RefreshLog with(NOLOCK)
where RefreshDate >=dateadd(day,-1,getdate())
orderby RefreshDate desc

You just HAVE to love the simplicity of this, when you see the near-instant response time this yields.