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.
| Author |
Topic |
|
MuadDBA
628 Posts |
 Posted - 2006-06-30 : 11:12:51
|
| Who knew parallellism could be a bad thing?One of our SPs has generated this message, and I am trying to figure out why. The problem is that MS KB Articel doesn't really give me a good idea how a query can block itself due to parallellism, or how I could rewrite it to reduce the chance this will happen.Anyone here know of some good advice for this? Forcing merge or loop joins as opposed to parallell queries doesn't seem like a great idea, and neither does removing the ability of the query to do parallell execution. I want a good performing query that can maximize the resources my server has to offer, not one that I artificially throttle back. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-06-30 : 11:23:23
|
| Are you getting blocking or deadlocking?blocking is just do do with the way the processes are presented after sp4.The main thread sets off other threads and waits for the results - while it is waiting it says it is being blocked (by it's own spid). That's nothing to worry about and it will carry on when they complete.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-06-30 : 12:24:09
|
| Do you have the latest hotfix as well - (2187). (I assume you are on sp4)http://www.microsoft.com/downloads/details.aspx?FamilyID=9C9AB140-BDEE-44DF-B7A3-E6849297754A&displaylang=en#AdditionalInfoThat solved some blocking problems we had when running DBCC INDEXDEFRAG for instance.Do you have hyperthreading?, if so set the maximum number of processors to be used for parallell execution to the #physical processors (-1).Seeing processes blocking itself can be confusing at best when you first see it, I was a bit shocked myself the first time.It does not necessarily mean something bad though, and you can ignore those locks.rockmoose |
|
|
|
MuadDBA
628 Posts |
Posted - 2006-06-30 : 13:15:39
|
| No, I actually got the error that intra-query parallellism caused a deadlock. Yes, we're on SP4 and I know about blocking showing up because of latch waits.Our version is 8.00.2162. IS there something specific in that SP build that would help resolve this?Yes, I beleive we do have hyperthreaded processors. Thanks for all the input so far. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-06-30 : 14:04:02
|
| What is your "max degree of parallelism"You can find out via looking at the properties of the sql server or sp_configure.And how many physical procs do you have?.hotfix 2162, was actually replaced by 2187, and withdrawn from ms website.rockmoose |
|
|
|
MuadDBA
628 Posts |
Posted - 2006-06-30 : 15:54:02
|
| our maxdop is 8, we have 8 processors. |
|
|
|
MuadDBA
628 Posts |
Posted - 2006-06-30 : 16:06:07
|
| I just read up on all of the hotfixes included in 2187, and I don't see anything relating to parallellism. I am hesitant to apply patches ahead of our normal schedule if they don't purport to correct any known issues with parallell queries. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-06-30 : 16:18:24
|
| You can try the maxdop hint in your query, to see if it makes things better.Edit. setting maximum degree of parallellism is a very easy operation.With 4 physical = 8 hyperthreaded procs.I would definitely set max degree of parallellism to 4 or 3 (we have 3).At least now you know there is apatch, you should apply it in the next patch cycle.rockmoose |
|
|
|
MuadDBA
628 Posts |
Posted - 2006-06-30 : 16:30:18
|
| The problem is that it's very difficult to tell if this would make things better. This was the first time we saw this error, and the proc runs once per day. Any changes I make might fix it, or we might deal with reduced performance for 3 weeks and still have this happen again. It's aggravating. |
|
|
|
|
|
|
|
|
Intra-Query Parallellism deadlocking.