Wednesday, March 7, 2012

NOLOCK hint causes process blocking?

We recently added NOLOCK hints to our less-important queries to cut down on
deadlocks. It does decrease the deadlocks but it seemed to increase other
process blocking... the type that nearly hangs the sql server. Is that
possible? Has anyone else noticed? I am starting to think NOLOCK is a
NO-NO, and that deadlocks are better.
-DanWhat's probably happeneing is that without NOLOCK the queries were being
slowed down. Now with NOLOCK they run full out and are hitting resource
shortages elsewhere. Deadlocks are NEVER better.
How is your server nearly hanging? If you're trying to resolve deadlocks by
adding NOLOCK then it may be that your queries/updates have problems.
Are you accessing resourcing in the same sequence?
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Dan English" <dan_english2@.cox.net> wrote in message
news:OC8PKDE0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> We recently added NOLOCK hints to our less-important queries to cut down
> on deadlocks. It does decrease the deadlocks but it seemed to increase
> other process blocking... the type that nearly hangs the sql server. Is
> that possible? Has anyone else noticed? I am starting to think NOLOCK is
> a NO-NO, and that deadlocks are better.
> -Dan
>|||Thanks for the response. I've posted the offending stored proc in a new
post.
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:PlJ3f.136806$vt2.119755@.fe08.news.easynews.com...
> What's probably happeneing is that without NOLOCK the queries were being
> slowed down. Now with NOLOCK they run full out and are hitting resource
> shortages elsewhere. Deadlocks are NEVER better.
> How is your server nearly hanging? If you're trying to resolve deadlocks
> by adding NOLOCK then it may be that your queries/updates have problems.
> Are you accessing resourcing in the same sequence?
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria

No comments:

Post a Comment