Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

One interviewer asked me this following question Expand / Collapse
Author
Message
Posted Monday, October 25, 2010 3:07 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 11:38 AM
Points: 80, Visits: 357
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?
Post #1010429
Posted Monday, October 25, 2010 3:13 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 12:57 PM
Points: 576, Visits: 692
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.
Post #1010437
Posted Monday, October 25, 2010 3:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 3,103, Visits: 7,811
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.
Post #1010438
Posted Monday, October 25, 2010 10:09 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 11:38 AM
Points: 80, Visits: 357
Thank you all for your replies.

BMR
Post #1010517
Posted Tuesday, October 26, 2010 2:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
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 2008, MVP
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

Post #1010605
Posted Wednesday, October 27, 2010 5:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 7:19 AM
Points: 98, Visits: 226
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.
Post #1011408
Posted Wednesday, October 27, 2010 5:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1011426
Posted Wednesday, October 27, 2010 5:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
Removed

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1011427
Posted Wednesday, October 27, 2010 7:03 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 11, 2014 5:37 AM
Points: 904, Visits: 1,412
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.




Post #1011498
Posted Wednesday, October 27, 2010 8:27 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 4,319, Visits: 6,112
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 at GMail
Post #1011583
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse