May 26, 2011 at 12:33 pm
I will look into both options. It seems, though, at this point, the problem is bigger than a single sproc. This job is hanging at every step, even though I've increased the database sizes. Do you have any thoughts on what the problem could be?
May 26, 2011 at 12:37 pm
My first thought was locking/blocking do you see anything in the Activity Monitor?
Having increased the DB sizes tends to take growth waits out of the equation.
CEWII
May 26, 2011 at 1:00 pm
Elliott Whitlow (5/26/2011)
My first thought was locking/blocking do you see anything in the Activity Monitor?Having increased the DB sizes tends to take growth waits out of the equation.
CEWII
Oh my God, it seems so obvious now!!!! There was a job that had been running since 10pm last night and never finished. I never even looked at that (long story, but it's a big coincidence that we had problems with the same job two nights ago and I thought it was all related...it never occurred to me that it could be something completely different). Thanks for mentioning it!
May 26, 2011 at 1:57 pm
You are welcome.
CEWII
May 26, 2011 at 2:26 pm
Vicki Peters (5/26/2011)
Ninja's_RGR'us (5/26/2011)
Vicki Peters (5/26/2011)
If it's a UNION of several inserts, then wouldn't it all have to be inserted at once?Union or union all? That could make a hell of a difference as well.
Union.
In case you don't know the difference... union is the equivalent of select DISTINCT * FROM alll 4 datasets. So the server has to resort the data, group by all column to filter out the duplicates.
If you don't need to do that, then union all just concatenate the 4 datasets without anything else so that runs much faster (but with possibly different output).
May 26, 2011 at 2:29 pm
Ninja's_RGR'us (5/26/2011)
Vicki Peters (5/26/2011)
Ninja's_RGR'us (5/26/2011)
Vicki Peters (5/26/2011)
If it's a UNION of several inserts, then wouldn't it all have to be inserted at once?Union or union all? That could make a hell of a difference as well.
Union.
In case you don't know the difference... union is the equivalent of select DISTINCT * FROM alll 4 datasets. So the server has to resort the data, group by all column to filter out the duplicates.
If you don't need to do that, then union all just concatenate the 4 datasets without anything else so that runs much faster (but with possibly different output).
Yes, I did know, thanks. Unfortunately it is necessary. But this problem is all okay now. There was a process slowing everything down. However, it was also a good idea to enlarge the databases (which I did), and I also am working on BCPing as many steps of this job as possible.
Thanks, everyone, for the input!
May 26, 2011 at 2:36 pm
Awesome, I had to talk about it... you'd be surprised by the # of people who don't know the difference.!!
May 26, 2011 at 8:18 pm
Ninja's_RGR'us (5/26/2011)
Awesome, I had to talk about it... you'd be surprised by the # of people who don't know the difference.!!
hehe I just had to clarify that exact topic to my wife yesterday (yes we are the uber geek family :-P)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 27, 2011 at 5:25 am
So in relation to your wife, what's the difference between union and union all? plz keep it PG! ;-):-P
May 27, 2011 at 6:57 am
Ninja's_RGR'us (5/27/2011)
So in relation to your wife, what's the difference between union and union all? plz keep it PG! ;-):-P
The level of commitment 😉
Far away is close at hand in the images of elsewhere.
Anon.
May 27, 2011 at 7:38 am
David Burrows (5/27/2011)
Ninja's_RGR'us (5/27/2011)
So in relation to your wife, what's the difference between union and union all? plz keep it PG! ;-):-PThe level of commitment 😉
LOL! Like the commitment level difference between a pig and a chicken for breakfast.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply