February 18, 2009 at 11:57 pm
February 20, 2009 at 10:40 am
does the process affect objects with triggers on them ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 25, 2009 at 6:49 am
karthikeyan (2/18/2009)
Hi Gurus,I ran one process in of the sqlserver 2000 database that process id was 230 and same process was blocked itself.
why is it happend frequently.
Thanks in advance.
Sorry for the tardiness of this reply. Does the process include queries with correlated subqueries or multiple references to the same table? Is it running on a box with multiple processors? If so, the distribution of work to different processors may be causing the self-blocking. I have seen this happen in the product I work on.
If you can get to the specific query that is doing the blocking try adding the Maxdop query hint to see if that resolves the problem. Add OPTION (MAXDOP 1) to the end of the query.
CAUTION: This will definitely impact the speed of the query, although it can be argued that a failing query has no speed. 😀 You want to be very careful with this hint and use it only in the query that is blocking itself. In the situation I described above it resolved the problem and allowed the query to complete successfully.
If that resolves the problem you might want to see if it is possible to rewrite the query so as to eliminate the blocking section, thus eliminating the need for the hint.
February 25, 2009 at 9:02 am
It's perfectly normal with 2000 SP4. It happened in earlier versions, but wasn't reported.
If a query runs parallel and some of the threads are slower than others, the ones that finish first are 'blocked' but the ones still running. It's not a concern and it's not a problem.
If you're seeing a lot of it, you may want to lower the MAX DOP for the server. Set it to half the number of processors to start with/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply