April 7, 2015 at 9:45 am
Hello All,
I have one stored procedure being called by the job that runs everyday. It generally takes 2-3 minutes to run. But sometime, for some reason it hangs at one point(its always same place when it occurs) it runs forever. We had to kill the process after running several hours. The problem is we can not recreate the issues. It runs fine in all other environments(dev, UAT). It runs fine on production as well if you take out the code from proc and run manually. I tried running sp_recomplile and worked for few days and again it started having the same problem. Since I can generate the issue in any other environment I am not sure I do start to resolve this issue.
April 7, 2015 at 9:56 am
When I have had this kind of problem it often has to do with the indexes or statistics. A few things to check:
Make sure that
* your statistics are up-to-date in production.
* have all the same indexes in prod as your other environments
* Production indexes are online and are not too heavily fragmented (this will cause the optimizer not to sometimes not use them)
You may also want to look at locking, blocking and look for deadlocks. Production is obviously more active and is more prone to deadlocks, lock escalation, etc - stuff that you may not encounter in an environment with less traffic.
Edit: added comments about locking, etc...
-- Itzik Ben-Gan 2001
April 7, 2015 at 12:23 pm
Deadlocks will present themselves as errors, not just a long running proc.
I agree it could be blocking. Check to see if there are blocking issues. Does your production server have the same amount of data as all the other machines you're testing it against? It could be differences in statistics caused by differences in data lead to different execution plans. Have you looked at the execution plans for when it's bad and for when it's good and compared the two? If you do this, focus on the compile values for your parameters. You might be looking at bad parameter sniffing. Check the ANSI connection settings to be sure that the production machine is the same as the others. Are there differences in the Cost Threshold for Parallelism between production and the other machines. How about the MAXDOP setting?
Those are all the guesses I can come up with based on the information at hand.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 7, 2015 at 12:25 pm
Deadlocks will present themselves as errors, not just a long running proc.
Yep. Duh! Good catch Grant.
-- Itzik Ben-Gan 2001
April 7, 2015 at 2:52 pm
Its a same data and there is not blocking issues. When I take our the code and run manually (Out side the stored proc) on the same server(prod), it has no issues...it only occurs when it is with in the proc and is being called by the jobs. I am pretty sure its not statistics. Because we tried restoring the database from the same night we had issues on DEV and tried to run the process....the proc/job run with no issues.
April 7, 2015 at 4:14 pm
So a restore of the database on dev and the proc runs good, but in production the proc runs bad?
Sounds like a server setting and/or hardware then. Without that, I'd still be focused on the possibilities of bad parameter sniffing. But now, check the max memory setting, max degree of parallelism, cost threshold for parallelism, oh, all that stuff. If they'are all identical between dev and prod, then I would assume hardware.
I would also capture the wait statistics for the query running in production and dev, just so you can compare to understand what it's waiting on. Have you captured the execution plan in each environment?
Editing this, I still think it might be bad parameter sniffing. After the restore, it would have to recompile and depending on the parameter value you passed, you could be getting a different plan. You haven't talked about plans yet. Get a plan when it's running good, and one when it's running bad. Preferable would be an actual plan for both, but not necessary.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 7, 2015 at 6:15 pm
Walton (4/7/2015)
Hello All,I have one stored procedure being called by the job that runs everyday. It generally takes 2-3 minutes to run. But sometime, for some reason it hangs at one point(its always same place when it occurs) it runs forever. We had to kill the process after running several hours. The problem is we can not recreate the issues. It runs fine in all other environments(dev, UAT). It runs fine on production as well if you take out the code from proc and run manually. I tried running sp_recomplile and worked for few days and again it started having the same problem. Since I can generate the issue in any other environment I am not sure I do start to resolve this issue.
You say the SP is being called "by the job that runs everyday." Please characterize this job in a little more detail. Is it SQL Agent running the SP?
I'm thinking that you might try adding OPTION RECOMPILE to the SP, since you said forcing a recompile tends to give you temporary relief for a few days. It depends on how resource intensive compiling that SP is, whether this will help or not.
Also, does the SP get called with parameters?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 7, 2015 at 6:56 pm
dwain.c (4/7/2015)
Walton (4/7/2015)
Hello All,I have one stored procedure being called by the job that runs everyday. It generally takes 2-3 minutes to run. But sometime, for some reason it hangs at one point(its always same place when it occurs) it runs forever. We had to kill the process after running several hours. The problem is we can not recreate the issues. It runs fine in all other environments(dev, UAT). It runs fine on production as well if you take out the code from proc and run manually. I tried running sp_recomplile and worked for few days and again it started having the same problem. Since I can generate the issue in any other environment I am not sure I do start to resolve this issue.
You say the SP is being called "by the job that runs everyday." Please characterize this job in a little more detail. Is it SQL Agent running the SP?
I'm thinking that you might try adding OPTION RECOMPILE to the SP, since you said forcing a recompile tends to give you temporary relief for a few days. It depends on how resource intensive compiling that SP is, whether this will help or not.
Also, does the SP get called with parameters?
Yes it SSIS packing calling the stored proc. It takes parameter too which is always same name of package of package calling the stored proc for audit purpose. And also it always stuck (whenever it does) on same place and that part call the function with parameters.
What odd thing I have on the code is something like below:
where not (fun1(@para1, @para2) and fun2(@para1, @para2)
when I googled it it is equivalent to
Where fun1(@para1, @para2) or fun2(@para1, @para2)
I am wondering if this code has anything to do with the issue.
April 7, 2015 at 8:09 pm
Walton (4/7/2015)
dwain.c (4/7/2015)
Walton (4/7/2015)
Hello All,I have one stored procedure being called by the job that runs everyday. It generally takes 2-3 minutes to run. But sometime, for some reason it hangs at one point(its always same place when it occurs) it runs forever. We had to kill the process after running several hours. The problem is we can not recreate the issues. It runs fine in all other environments(dev, UAT). It runs fine on production as well if you take out the code from proc and run manually. I tried running sp_recomplile and worked for few days and again it started having the same problem. Since I can generate the issue in any other environment I am not sure I do start to resolve this issue.
You say the SP is being called "by the job that runs everyday." Please characterize this job in a little more detail. Is it SQL Agent running the SP?
I'm thinking that you might try adding OPTION RECOMPILE to the SP, since you said forcing a recompile tends to give you temporary relief for a few days. It depends on how resource intensive compiling that SP is, whether this will help or not.
Also, does the SP get called with parameters?
Yes it SSIS packing calling the stored proc. It takes parameter too which is always same name of package of package calling the stored proc for audit purpose. And also it always stuck (whenever it does) on same place and that part call the function with parameters.
What odd thing I have on the code is something like below:
where not (fun1(@para1, @para2) and fun2(@para1, @para2)
when I googled it it is equivalent to
Where fun1(@para1, @para2) or fun2(@para1, @para2)
I am wondering if this code has anything to do with the issue.
If you are using scalar user defined functions in WHERE clause of your procedure that's tragic and something that should be re-designed. I digress.
Grant asked if you have compared query plans but you have not answered that. I would look at the plans including the estimated and actual number of rows... You say you don't think it's statistics but how, again, do you know for sure?
Grant also asked about your memory and parallelism settings... Getting a serial plan in prod while getting a parallel plan elsewhere would certainly explain your troubles. Having, say a much, much higher cost threshold for parallelism in prod than elsewhere would help explain this further.
What I'm trying to say is there have been a number of questions raised which would likely help us get to the bottom of this that you have not answered.
Review some of the unanswered questions, get and post the plans, gather some of your settings, post them here... that would get this solved quicker.
Edit: typos, grammar
-- Itzik Ben-Gan 2001
April 7, 2015 at 8:41 pm
Exactly.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply