October 25, 2010 at 3:07 pm
Hi,
One interviewer asked me this following question.
A 200 lines stored procedure working fine till yesterday, but today morning it is taking long to run. How can you find where the problem is and what is the cause for this?
I said the there may be network problem. He did not agree. Can you help?
October 25, 2010 at 3:13 pm
First things first, look at the execution plan. See where it's hanging.
If you don't see anything there, check for blocks while it executes.
If that also looks fine, check CPU/IO usage and see if those are spiking for some reason on the box.
If not, make sure the proc hasn't actually changed (compare to source control versions).
If it hasn't, you can look into indexes/statistics/fragmentation on the tables it's pulling from. The only reason this isn't higher is because fragmentation usually doesn't happen out of the blue unless you have a huge data job on the tables..which I'm sure you would know about.
October 25, 2010 at 3:13 pm
If the stored procedure does not connect to another server then it's very unlikely network problems would affect the performance.
Assume the stored procedure only accesses databases on this server, what else could have changed?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 25, 2010 at 10:09 pm
Thank you all for your replies.
BMR
October 26, 2010 at 2:45 am
My guess would be statistics, but there are lots of possible causes and without more info it's impossible to say which.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2010 at 5:20 am
I was called in once where a procedure went from running in less than a second to many minutes. Since the procedure was called multiple times for each order picked and shipped it impacted getting products shipped.
The problem turned out to be a bad record added to a lookup table. I don't have my notes handy to explain the details, but no one would believe me until they removed the bad record and the problem went away.
I do agree with the previous comment that unless the procedure went across servers then network problems would not be a good answer.
October 27, 2010 at 5:44 am
Any DML operation held in the night which disturbed the indexes/statistics.this could be one cause
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 27, 2010 at 5:44 am
Removed
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 27, 2010 at 7:03 am
It's now using a bad query plan from the cache.
This exact scenario happened to us. As soon as the bad plan was removed; the sproc started running great again.
October 27, 2010 at 8:27 am
here would be my response, in order:
1) dropped index. would do a schema compare from known good config for db
2) new version of code rolled out - same as 1 to verify
3) blocking - sp_whoisactive
4) parameter sniffing issue
5) stupid user input (like asking for 5 years worth of data in report that should do a day or week) 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 19, 2013 at 8:01 am
1) check all process of corresponding database using sp_who.
2) check any process is not suspended ?
3) check query plan also
May 19, 2013 at 8:24 am
Please note: 3 year old thread
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply