T-SQL: Saved Transaction
Techies--
This stored proc has been running like a charm for nearly a year, every day, every 15 minutes. Last night the sql server agent job that kicks off the batch showed this sproc in a running state for 10 hours. I think it is due to how I am using saved transactions. Here is the objective of the attached source:
A batch of orders comes into staging that can be in any state of having been already exported or not. For example, the new run can have new orders, it can have the same group of orders (someone ran the export twice), it can have older orders (someone ran an old batch), it can have some new orders and some old, etc. I can have a single order per batch or hundreds.
I want to be able to issue a delete statement under certain conditions, or inserts under other conditions (I've tried MERGE, it does not execute nearly as efficiently as separating the delete statements from the inserts). I think I am using the saved transaction feature to collect all the actions I need to do for all the conditions in order to execute them at the end.
I need another pair of eyes to help me spot what the "drop-through" is in this code.
Please advise.
MetroTransactionProblem.sql
This stored proc has been running like a charm for nearly a year, every day, every 15 minutes. Last night the sql server agent job that kicks off the batch showed this sproc in a running state for 10 hours. I think it is due to how I am using saved transactions. Here is the objective of the attached source:
A batch of orders comes into staging that can be in any state of having been already exported or not. For example, the new run can have new orders, it can have the same group of orders (someone ran the export twice), it can have older orders (someone ran an old batch), it can have some new orders and some old, etc. I can have a single order per batch or hundreds.
I want to be able to issue a delete statement under certain conditions, or inserts under other conditions (I've tried MERGE, it does not execute nearly as efficiently as separating the delete statements from the inserts). I think I am using the saved transaction feature to collect all the actions I need to do for all the conditions in order to execute them at the end.
I need another pair of eyes to help me spot what the "drop-through" is in this code.
Please advise.
MetroTransactionProblem.sql
<This stored proc has been running like a charm for nearly a year, every day, every 15 minutes. >
did you have any changes (sql\windows) after successful "15 min" execution?
----
for start
check if there are any sql error\eventviewer\sqlagent
also try to run
exec sp_recompile 'yourproc'
and check 2nd execution proc duration
--- sp_recompile http://msdn.microsoft.com/en-us/library/ms181647(v=sql.105).aspx
--
check if you are running DB main tasks: reindex\checkdb\update stats
--if you do not - run at least update stats
--when this box was restarted last time?
check your server CPU\Memory is not chocking during your proc execution ( may need to add some extra horse powers and\or see what else is running (e.g. Antivirus ))
=-----
--
it is for start
did you have any changes (sql\windows) after successful "15 min" execution?
----
for start
check if there are any sql error\eventviewer\sqlagent
also try to run
exec sp_recompile 'yourproc'
and check 2nd execution proc duration
--- sp_recompile http://msdn.microsoft.com/en-us/library/ms181647(v=sql.105).aspx
--
check if you are running DB main tasks: reindex\checkdb\update stats
--if you do not - run at least update stats
--when this box was restarted last time?
check your server CPU\Memory is not chocking during your proc execution ( may need to add some extra horse powers and\or see what else is running (e.g. Antivirus ))
=-----
--
it is for start
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
acperkins--LOL! Were you having a bad day?
ASKER
@dtodd,
The sproc is issued from an SSIS package ... but could be executed outside of it. The sproc normally runs in under 1 minute. Contrary to what some of the responses here indicate, it isn't performing in an inefficient manner. As an aside, I didn't expect anyone to cycle through 600 lines--especially when over 80% of the 600 lines are simple insert/update/delete statements--I thought that someone (as you did) would filter that out and hone in on the named vs. non-named transactions with a plan of action for determining why on a likely error, the rollback left the transaction in limbo. In the future, I'll edit out the noise to keep the conversation focused.
@acperkins,
You're bright and I know you know it, but investing your time in posting snark for the purpose of posting snark isn't the career builder you may think it is--particularly when the assessment you offer is based on speculation and assumption rather than an honest review. You were so focused on communicating your dismissive message, that you disabled yourself from focusing on the problem domain long enough to provide true professional advice. Watch that tendency. It's a career assassin.
The sproc is issued from an SSIS package ... but could be executed outside of it. The sproc normally runs in under 1 minute. Contrary to what some of the responses here indicate, it isn't performing in an inefficient manner. As an aside, I didn't expect anyone to cycle through 600 lines--especially when over 80% of the 600 lines are simple insert/update/delete statements--I thought that someone (as you did) would filter that out and hone in on the named vs. non-named transactions with a plan of action for determining why on a likely error, the rollback left the transaction in limbo. In the future, I'll edit out the noise to keep the conversation focused.
@acperkins,
You're bright and I know you know it, but investing your time in posting snark for the purpose of posting snark isn't the career builder you may think it is--particularly when the assessment you offer is based on speculation and assumption rather than an honest review. You were so focused on communicating your dismissive message, that you disabled yourself from focusing on the problem domain long enough to provide true professional advice. Watch that tendency. It's a career assassin.
I'll edit out the noise to keep the conversation focused.
Exactly my point.
You're bright and I know you know it
Fair enough and I am glad you got it solved.
the career builder you may think it is
Why on earth would you assume that this site is a career builder for me (or for that matter anyone here that spends an inordinate amount of time helping others) ? Surely you don't believe that?
P.S. Most people here call me by my name: Anthony
Exactly my point.
You're bright and I know you know it
Fair enough and I am glad you got it solved.
the career builder you may think it is
Why on earth would you assume that this site is a career builder for me (or for that matter anyone here that spends an inordinate amount of time helping others) ? Surely you don't believe that?
P.S. Most people here call me by my name: Anthony
ASKER
Anthony,
What I believe is that spending time on help boards like this travel parallel to one's career. How one passes on professional knowledge is an attribute. If the quality of any of your responses is less than it could be, it's likely that the root cause driving that decision intersects elsewhere.
Kind regards,
Paula
What I believe is that spending time on help boards like this travel parallel to one's career. How one passes on professional knowledge is an attribute. If the quality of any of your responses is less than it could be, it's likely that the root cause driving that decision intersects elsewhere.
Kind regards,
Paula
But in broad terms, I suspect your Stored Procedure for whatever reason (but if I were to guess the Stored Procedure is in urgent need of optimization) is taking longer than the 15 minute interval allowed for it.