stored procedure runs in production in 50 minutes, fails after 2 hours 30 minutes in Dev with timedout

Hello Forum Members,

I have a stored procedure which does a Merge on destination table. It runs in production in 50 minutes, fails after 2 hours 30 minutes in Dev with timedout. Can you please let me know at higher level what to monitor.

Kind Regards,

Sqlquery9

July 23rd, 2015 5:49pm

Are you asking for why it takes much longer in development or why it timed out?  For timing out, what is your connection query time out set to?

For why it is taking longer in development, it could be a number of things.  Generally, you would check proper indexing first.  If not, then it may be environmental.  At that point, you would look at cpu, ram, and disk usage.  It could also be that the production disks are much faster than development.  Another possibility is that there are other processes in your development environment that is consuming a lot of resources as well.  Then again, maybe your admin set your development SQL Server instance to consume far less resources than production.

Free Windows Admin Tool Kit Click here and download it now
July 23rd, 2015 5:59pm

i think you might have the same copy as prod in dev. if not, then you should look in to performance side and indexing and fragmentation.

if your dev is same as prod this could be parameter sniffing. it happens in some environments. you can easily over come this by defining local variables with out changing your logic.

here is more info.

http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx

i have recently seen this kind of issue in organisation. and fixed it. i hope this helps you.

 
  • Proposed as answer by Milan Das 1 hour 27 minutes ago
July 23rd, 2015 8:10pm

How much data does it proceed? Does the table (dest) have a trigger?
Free Windows Admin Tool Kit Click here and download it now
July 24th, 2015 2:31am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics