Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm completely stumped on this one. We're running a pretty usual intranet app here, classic ASP frontend and SQL Server 2005 backend. Occasionally, once/twice a week, a certain stored proc takes ~50x longer to run when I run it in the Management Studio. This slowness lasts the entire day and it inexcplicably goes away the next day.

So essentially what's happening is this: once/twice a week, when I fire up the intranet page that runs the stored procedure and displays the results, it takes roughly 115secs to run. I then fire up SQL Server Management Studio and run the same query, which this time around takes only 3-4secs to run.

The next day, the intranet page is taking as long as SSMS to run the very same stored procedure, with nothing having been changed in the interim (not to my knowledge anyway).

Other details:

  • the IIS connects to SQL Server 2005 using Provider=sqloledb as the driver
  • the slowdown lasts the entire day, regardless of the number of active users
  • I can replicate the slowness by running the same query from Excel, using the same driver/connection string
  • I've ran a trace on the SQL server and it turns out that on those particularly slow days, the duration of the stored proc is actually longer when executed via IIS than direct query

As such, I was wondering whether any of you has encountered any similar behaviour before, if you've fond a solution to it and what would your advice be in tackling/solving it?

Thanks for your help,

Please find Trace results in .csv format below:

EventClass,TextData,ApplicationName,CPU,Reads,Writes,Duration (s),ClientProcessID,SPID,StartTime,EndTime 12,exec spStubbornProc,Internet Information Services,"106,016","7,059,999",1,115.80,5476,85,18/04/2013 08:17:15,18/04/2013 08:19:11 12,exec dbo.spStubbornProc,Microsoft SQL Server Management Studio - Query,"3,141","146,051",0,3.40,5876,84,18/04/2013 08:20:45,18/04/2013 08:20:48 12,exec dbo.spStubbornProc,Microsoft SQL Server Management Studio - Query,"2,563","147,387",0,2.93,5876,84,18/04/2013 08:21:04,18/04/2013 08:21:07 12,exec spStubbornProc,Internet Information Services,"103,156","7,083,365",7,118.73,5476,80,18/04/2013 09:39:41,18/04/2013 09:41:40 12,exec dbo.spStubbornProc,Microsoft SQL Server Management Studio - Query,"2,406","175,029",1,2.57,5876,84,18/04/2013 10:08:58,18/04/2013 10:09:01 12,exec spStubbornProc,Internet Information Services,"112,218","7,103,267",7,114.72,5476,75,18/04/2013 14:06:26,18/04/2013 14:08:21 12,exec spStubbornProc,Internet Information Services,"93,515","7,101,229",3,93.60,6092,60,18/04/2013 19:37:02,18/04/2013 19:38:35 12,exec spStubbornProc,Internet Information Services,"2,500","148,775",1,3.42,5160,75,19/04/2013 09:16:01,19/04/2013 09:16:04

share|improve this question
    
Capture the post execution plans of both the queries run through IIS and through SSMS. Post them here, if possible. But look for discrepancies and performance degraders. –  Thomas Stringer Apr 19 '13 at 18:01
    
Please search & read –  Aaron Bertrand Apr 19 '13 at 18:07
    
    
Thanks Aaron, really useful link. I did search for that beforehand, the majority of the results were on se... A little bit of catch 22 there... –  user1158959 Apr 22 '13 at 7:24
    
How can I make Aaron's comment an answer? The linked article provides a thorough presentation of why the issue is happening and how to go about preventing this kind of behaviour in the future. –  user1158959 Apr 23 '13 at 9:28
add comment

1 Answer 1

up vote 0 down vote accepted

Usually this means that SQL is using cashed execution plan that just does not work any more. From SMSS it is recompiled and uses fresh execution plan. Try to force some index in queries, or rebuild indexes ? Definitely check execution plan for both queries.

share|improve this answer
    
It's unlikely SSMS is recompiling it; it is more likely that it is reusing a different plan because the app and SSMS have at least one SET option set differently. –  Aaron Bertrand May 20 '13 at 3:40
    
@AaronBertrand True. –  IvanP May 20 '13 at 6:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.