Cursor Hell
-
I have a job that runs very well until it hits a section that uses a cursor. I inherited the job and most of the business knowledge behind it is gone. The only things I know for certain at this point is that it's spawning up to 16 additional tasks and most of them sit with a LATCH_EX wait for the others; the context 0 is waiting with CXPACKET. Sometimes this job runs in a couple of hours, sometimes it reaches a point where it stops, almost like its run out of resources to allocate.
Several questions: What is a LATCH_EX wait?
What is a CXPACKET wait?
The box has 8 CPUs, would reducing the number allowed for use by SQL to 6 improve throughput?
The box has 8G of memory, SQL is allowed a maximum of 4G, would increasing this have a positive impact?
All available processors are available for parallelism, should I cut this back to reduce the number of spawned tasks?
Tim
-
Have you analyzed the code to see if the cursor can be replaced with something set-based? It sounds tp me like the cursor is starting some tasks that do not finish and end up fighting each other for resources.
It would be helpful if you could post some example code that we could check out.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Tim,
This isn't going to help you a bit... I just want to make a point to any others that may read this because it's been an ongoing battle on this forum, on other forums, and at work... thanks for letting me "hi-jack" your thread for just a minute.
Folks, here is a classic example of what happens when you don't have the business rules for the code documented IN THE CODE! And, for all you managers out there... if you don't give folks the time to write good, high performance, scalable, set-based code that is FULLY documented in the code, then you're going to run into the same problems that poor ol' Tim is having right now. I don't care what the "schedule" stipulates... get better at bidding the time necessary to write, document, and unit test good code.
The company I work for recently made the shift from the normal "panic" coding that schedule dictates to "planned coding" including commenting of the code... at first, things looked grim... it took 2 to 6 times longer to get the code into production... but guess what? We've almost reached the Nirvana of the coveted "ZERO DEFECTS" which saved much more time than all the rework we were doing and, get this, because of the embedded documentation, even someone who never saw the code before could easily modify the code correctly! In the past, a code modification that took 2 days to research suddenly only took an hour to research! As a sidebar, having another person look at and analyze your code against the requirements will also save on mistakes (known as "peer reviews") and is a form of "extreme programming" that saves hundreds of hours per month in rework.
Time spent up front will save dozens more time than what it'll take afterwards. Do it right the first time... make reasonable schedules that allows that attention to detail.
And, remember, "If you want it real bad, you'll probably get it that way"
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Tim,
Thanks for letting my "rant" in... I don't know if we can help you with that nasty cursor, but if it's not hundreds of lines long, maybe you should post it... we might be able to help...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Nice rant Jeff!! :-))
As for a cursor that also uses parallelism . . . YUCKO!!! If the threshhold for parallelism is still set at the default of 5, I would consider raising it higher, even on an 8 cpu box (ESPECIALLY if it is really a 4 cpu hypterthreaded box!! - actually, if the latter, try disabling HT!!) As always, test before doing production stuff.
Rewriting the cursor logic as set-based logic is obviously a laudable goal here too.
If that can't be done, is the cursor at least defined as FAST_FORWARD??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Hello, Tim?
You still there? 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Yup, still here. Jeff, THANK YOU for the rant, however in this case the time was available to document.
What was also present was an extremely insecure person who used NO documentation as 'job security'.
However, when he made the statement, "You never use indexes in relational databases, in fact they're not allowed",
he was shown the door and told to not let it hit him in the ass on the way out. This supposed "DBA" who throught he knew more than everyone else combined is the bane of my existance.However, there is a somewhat happy ending for me; my company has hired another DBA and this one 'likes' writing T-SQL
. He also thinks the twit from before us should have been shot. We shall now proceed to fix what others have broken.A quick question for anyone reading this; why are there so few women in this field?
-
Wow... been there and done that... Glad to see you got a got DBA for a change.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I agree Tim! Consider yourself to be VERY fortunate to have a DBA at all for SQL Server, much less one that enjoys writing TSQL. I do hope for your sake that he/she is GOOD at writing TSQL as well! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply