SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


One interviewer asked me this following question


One interviewer asked me this following question

Author
Message
bmr270
bmr270
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 398
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?
Derrick Smith
Derrick Smith
SSC Eights!
SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)

Group: General Forum Members
Points: 948 Visits: 715
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.
Alvin Ramard
Alvin Ramard
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4287 Visits: 11639
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?



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

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
bmr270
bmr270
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 398
Thank you all for your replies.

BMR
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90323 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


steven.malone
steven.malone
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 227
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.
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5450 Visits: 4076
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;-)
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5450 Visits: 4076
Removed

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Steve-3_5_7_9
Steve-3_5_7_9
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1496 Visits: 1599
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.



TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12943 Visits: 8565
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) Smile

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search