SQL Server Performance Forum – Threads Archive
Should I turn off Parallelism?
I’m running a update statement. I see the following in Process Info: Process ID Context ID Status Wait Type 52 2 Sleeping CXPACKET52 1 Sleeping CXPACKET
52 0 Sleeping PAGEIOLATCH_SH I wonder whether I should turn off Parallelism or not. SQL Server 2000 has SP3.
Thank you for any help.
Can you show update statement?
Luis Martin
Moderator
SQL-Server-Performance.com
Hi Luis, It is another simple T-SQL statement: update #temp set column2 = table1.column2
from table1, #temp
where table1.column3 = #temp.column3 #temp has 5 million records and table1 has 5000 records. I have added a nonclustered index (column3, column4) for #temp. I have used ITW for this and it suggest me to add another nonclustered index (column3) for #temp but it says that will only increase performance by 5% so I have not added it.
I would suggest creating non clustered index on just column3 and create a non clustered index on column 2 and 3 in table1. Turning off parallleism will help only if you determine that it is the problem. You can look at the execution plan for verifying the same. Else you can run the query with MAXDOP=1 option to see if the performance improves. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
In my experience parallel query execution often causes more trouble than it’s worth. It can cause self-deadlocks, can degrade performance, and cause a number of other problems. Microsoft themselves do not guarantee that it will always work. I recommend liberal use of the OPTION (MAXDOP 1) optimser hint, or for a more fuller and permanent solution, set it off server-wide with sp_configure. I have found one thing that performs much better with parallelism than without, and that is dbcc checkdb(). On my main OLTP server I have parallelism off all the time but explicitly re-enable it during dbcc checks. Tom Pullen
DBA, Oxfam GB
Thomas this is a very strong statement. I agree that parallelism sometime hurts performance but I think those issues are more because of outdated statistics ot bad execution plan and less depenedent on the parllelism. If parallelism was so bad, multi proc machines would not have been running SQL Server. SQL Server has bencmarks of running 64 processors. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
I agree with gaurav on this. The ONLY time you should disable paralellism is when you have thoroughly tested and have a really good reason for doing so. We have queries that run in half the time with parallelism enabled. You do want to be careful you don’t get some batch process choking off the whole server, but other than that parallelism has a lot of benefits on a multi-processor system. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Yes, it’s a strong statement, but I have had some severe problems with it. Also, you can still have seperate queries running in parallel, so there is still some element of parallel execution. Tom Pullen
DBA, Oxfam GB
No there isn’t. That’s not parallel execution. That’s multi-process execution. There’s a huge difference. Have you tried this? UPDATE t2
SET t2.column2 = t1.column2
FROM
table1 t1
INNER JOIN #temp t2 ON t1.column3 = t2.column3 It shouldn’t make a difference, but it is the right way to write the statement. This will also theoretically limit your recordset quicker. You will need the following indexes: table1(column3)
#temp(column3) MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
I would have to agree with thomas in some extent. We’ve had major issues with parallelism as well. Might be that I’m burnt from the SQL 7.0 days but we’ve had issues on SQL 2000 as well. In shared hosting environments or other environments with a lot of databases on the server we leave it default off. This to avoid the hassle of narrowing down what’s performing bad and not. It’s a pain when you have a lot of databases and a lot of different types of queries on the same server. Often some will perfom bad with parallelism. In the case with servers where you have fewer databases or databases used for one specific and maybe larger application we start with parallelism on. But with many databases on the same server it’s in my experience not worth the hassle you often get with parallelism turned on.
The blanket statement part is what’s dangerous Argyle. On big applications that are mission critical, it’s just not a good idea to turn it off. It’s well worth it to tune for it and gain the benefits of it. It sounds like you agree with this though. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
There is a very good article in this website about parallelism: http://www.sql-server-performance.com/jc_parallel_execution_plans.asp
I have read it but it is too technical for me at this moment. I will reread it after I understand more about the execution plan.
]]>