asked
CXPACKET performance issue?
I’m using sql server 2012 to bulk insert about 7 million records to a staging table that takes me about 1 hour 30 minutes. I’ve used performance monitor, profiler to diagnose the problem but couldn’t see any problem. Seems like I don’t have any issues with CPU, disk io or memory. The only major application on my server is the sql server. During my bulk insert process I’m not running anything else. My CXPACKET waits stats are very high. CXPACKET wait_time_ms is 79346890761 and max_wait_time_ms is 2164694. I have 8 cpu processor and my degree of parallelism is set to 0. Can high CXPACKET cause slow load performance? Will turning the CXPACKET off help or hurt the performance? Thanks.
If any of the answers below was helpful, please indicate that by clicking on the thumbs up next to it. If any solved your problem, show that by clicking on the check box next to that answer.
CXPACKET is an indication of parallel execution. It's not necessarily a problem. You need to understand what else is slowing things down, not just parallel execution. I would suggest looking at the execution plan for the process to understand what it's doing as well as looking at the other wait states. If you want to experiment with the possibilities of reducing parallelism, you should first try adjusting the Cost Threshold for Parallelism up from the default value of 5 to something like 30 to see if that affects things in a positive manner. By and large, parallelism, especially for data loads, is a good thing. And you'll see long cxpacket wait times if the parallel processes are waiting on other resources. So I don't recommend trying to set Max Degree of Parallelism to 1 to turn it off. At least not until you ensure that the issue does not lie elsewhere.
Further to Grant's suggestion, look particularly at points in the execution plan where the plan is changing from parallel execution to non-parallel operators (you'll see yellow blobs with arrows on for parallel operators). Dig into those steps, have a look at the data flowing into the non-parallel operators, see what the imbalances are caused by. Thoughts I have when seeing these issues: * What are the indexes on the table? * How up-to-date are the statistics on those indexes? * Are those indexes appropriate? * Is it quicker to [disable the index][1], run the data load, re-enable the index? [1]:
http://msdn.microsoft.com/en-us/library/ms177406(v=sql.105).aspx
15 People are following this question.
