INSERT using EXEC problem
-
I have a stored procedure that runs fine by it self.
EXEC dbo.populateAging
This procedure returns 240k rows in 18 seconds. However, when I use this procedure for an INSERT, it hangs. Well it actually runs, but takes over four (4) hours to complete and does not populate the table. The table DOES MATCH the result set that is returned by the procedure 100%. What's more confusing, is that this procedure/process has been running weekly for almost a year with no issues until now. no changes to the server, procedure or table have been made.
TRUNCATE TABLE dbo.agingData
INSERT dbo.agingData
EXEC dbo.populateAging
Any ideas are welcome.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Tried recompiling the objects?
How many indexes on the inserted table?
Is there a trigger on that table [now].
-
A few more ideas came to mind... maybe that'll spark a light for you.
Does tempdb have to grow while doing that process?
Any other log or data file need to grow?
What's the recovery mode?
-
No indexes or triggers on target table.
Recovery model is Simple and I've tried with recovery model of bulk-logged with no change.
I'm not sure if tempDB or the DB running the proc need to grow or not but checking the actual file size while running the proc, they don't change.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Recompile did nothing?
-
Nope.
How would that make a difference? I can run the proc alone, then with insert, then alone and the "alone" executions always work fine. Doesn't recompile just for the proc to be recompiled. In this case the proc definition doesn't change and is fine on the first and third runs.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
I was thinking that proc a runs fines.
Proc b calls proc a.
Either plan b can be screwed, or plan b calls for an older/different version of plan a.
That's why recompile could have worked... however on a very long shot.
Can you spot any different between the 2 execution plans (with and without inserts) (sorry but I got nothing better at the moment)?
-
No noticable differences in the plans.
Here's an interesting note. I can run the procedure with "Results to File" then import the data into my table just fine. This is a solution for the immediate need, but definately not a long term solution.
*ARGH*
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
No idea what the problem is.... can't wait to see what everyone else can come up with!
-
OK, this is just too $%@$& weird!!!
Originally I was calling the procedure like below…..
TRUNCATE TABLE dbo.agingData
INSERT dbo.agingData
EXEC dbo.populateAging
I decided to try something new. I moved the TRUNCATE TABLE and INSERT statement inside the procedure and it works perfect.
Anyone that can possibly explain, I’d really like to know what is going on here and why it worked before and especially why it stopped working all of a sudden.
Thanks for your effort Ninja.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Changing the sp recompiled it, or more to the point changed the current execution plan. So that threw out any possible bad plan out the window and more importantly out of the cachedobjects table.
Can't say I have any better to offer at the moment
. -
I'm still not convinced the plan had anything to do with it.
In my humble understanding, the fact that I could run the procedure by itself fine tells me that the plan was fine? Isn't that a correct assumption?
But then again, who knows!!!!
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Most of the time.
But you can have more than 1 plan per procedure / object / query. That's why as a long shot I proposed the recompile. A more drastic solution would have been to wipe out the whole procedure cache. Really not a good idea on a busy server but it may have worked too.
-
I could/can try that. This is a server used mainly for report subscriptions that run in the early AM, and only limited use during the day.
So you're saying that a single proc that doesn't use any parameters can have more than one plan? That seems odd!
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
It's by design and for good reasons... search the articles on this forum for best practices + fully qualified object names + cached plans. You'll surely find the missing link in your knowledge.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply