| Author |
Topic |
|
ahmeds08
Aged Yak Warrior
737 Posts |
 Posted - 2013-09-20 : 13:08:57
|
| Hi All,I have a stored procedure which we execute through sql agent job and the average execution time was 6 mins.But a week back we dropped and re created the procedure(no code changes) and since then it is taking around 40 mins to finish execution from job.Please help on this.mohammad.javeed.ahmed@gmail.com |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-09-20 : 13:13:04
|
| Check the execution plan. You may want to implement a plan guide/recompile/optimize if the issue is due to a bad plan. You can try recompiling it with sp_recompile to see if that'll resolve the issue. Also look for out-of-date statistics.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-09-20 : 14:33:20
|
| I have rebuilt all the indexes,updated statistics,recompiled the procedure(sp_recompile).but still no luckExecution plan doesn't show any missing indexes.mohammad.javeed.ahmed@gmail.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-09-20 : 14:51:16
|
| Have you checked blocking and wait stats? How about hardware bottlenecks, such as high CPU and I/O latency?Please post the execution plan so that we can help.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-09-20 : 16:56:26
|
| Now i see the wait_type is 'OLEDB'.mohammad.javeed.ahmed@gmail.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-09-20 : 17:23:05
|
| If it's OLEDB, then the issue is outside of SQL Server. Is it using a linked server?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-09-22 : 22:34:44
|
| Yes Tara,It is using a linked server.mohammad.javeed.ahmed@gmail.com |
|
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2013-09-22 : 23:11:13
|
| read this toohttps://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/ |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-09-23 : 07:53:34
|
| i don't have any parameters for the proceduremohammad.javeed.ahmed@gmail.com |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-23 : 08:30:48
|
| If it is using a linked server, there are 3 things you need to be concerned about. First is the performance of the remote server, second the performance of the local server, and third, the network connection between the two.If the only thing you did was drop and recreate the stored procedure, it might run a little slow when you run it the first time, but subsequently, you shouldn't see any noticeable difference. So something else might be at play here. It could be that the stored procedure is returning a lot more data, or it could be that the load on the server has increased, or it could be any number of other things.I would start out by running the stored procedure locally on the remote server and observing its performance and comparing that to what you see when you run it as a linked query. |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-09-23 : 09:18:49
|
| Thank you James.And today the execution time has decreased,previously it was taking around 40 mins and today it was 30 mins.The query in the procedure which uses the linked server is a simple query using two joins and i have separately executed that part of the query on the remote server and the execution plan looks good.I guess it is the network speed causing the issue.mohammad.javeed.ahmed@gmail.com |
|
|
|
|
stored procedure running slow after recreating