September 23, 2010 at 3:32 am
Hello All.
I am running a fairly large query (by my standards anyway) in an SSIS package. Package has been running fine both when run through BIDS and when run from a SQL Server Agent job on my DEV server. Yesterday I deployed it to live and the process hung (appeared to hang) after returning about 1 million records (nothing special). For info the package is ETL based picking data from a SQL2000 database and loading it into a SQL2008 box. Until yesterday I had had no issues with the package.
After a bit of digging I found a number of CXPACKET WAITs causing the problem. Further investigation on this forum found a comment from Gail (and others, Gails name just sticks out) linking this to MAX Degree Of Parallelism. However before I start going along this path and making unnecessary changes, can anyone suggest how this may have started suddenly occurring? Last week the package ran fine in DEV, this week all I get is pain.
The server was rebooted last weekend for maintenance reasons. Does anybody know if this could cause me the problems I am seeing now?
Any thoughts would be greatly appreciated.
September 23, 2010 at 4:57 am
1) Just realised this is in the wrong group so before anybody says anything, I apologise, I obviously don't raise enough issues. 😀
2) Think I've found out the problem in the main. Credit is due to an old post from Eddie Weurch (Thanks Eddie!) whose one outstanding comment from my point of view was '...but the bottom line is to find the queries that are having high CXPACKET waits, and fix them.'
It turns out the parallelism (in my case at least) was being caused by an unecessary ORDER BY. Funny thing is, I don't remember it being a new change, I'm pretty damn sure it's been there a while. Anyway, I dropped the order by and not only did the Execution Plan show a distinct lack of parallelism, but I couldn't see a CXPACKET WAIT for dust.
While there have been no direct answers to my problem request, I would just like to say I am extremely grateful for all the time and effort people take to answer questions on here enabling the majority of people with problems to go search out the right answers.
Thanks! 🙂
September 23, 2010 at 9:08 am
Just FYI, CXPAcket_wait, isnt necssarily a bad thing. Here is a simple example:
You do a query which SQL SErver decides to divide in half- two processes. First process finishes, but must wait for the second process to complete before it can return your results. If you decrease Max Degree of Parrallelism, it will not divide it in half- only one process, so there will be no CXPACKET_WAIT, but it will take longer to complete.
Now your solution is the best one- if you dont need the order by, don't do it! but if you DID need the order by, then I do not think CXPACKET_WAIT is necessarily a bad thing and I bet you would find that decreasing max degree of parallelism just increases the total time to complete the query.
September 23, 2010 at 9:25 am
Gotta agree with your comments there. Each case is different and in mine, the ORDER BY was generating the CXPACKET WAITs. If I see them in future, I won't necessarily panic but I will have a better understanding.
Thanks for taking the time.
September 23, 2010 at 1:57 pm
SequelSurfer (9/23/2010)
It turns out the parallelism (in my case at least) was being caused by an unnecessary ORDER BY. Funny thing is, I don't remember it being a new change, I'm pretty damn sure it's been there a while.
The ORDER BY may have always been there. It takes a fairly small change in the data to change the way SQL will execute the query. In other posts you will see that a change of as little as 1%-2% in the data can change the way SQL uses indexes etc. It may just be that your database or some tables have got to a certain point where the old query plan wasn't efficient, so a new one was built.
Another thought is, when did the server restart did you skip a database optimization run? This may be causing a problem and it may dissapear after an index rebuild.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
September 24, 2010 at 1:16 am
Leo, you may have a point there. The optimisation runs are over a weekend and the reboot was definitely later. I think I might actually put the ORDER BY back in next Monday and run some tests. It would be good to know either way.
Thanks for the info.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply