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

  • 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. Commented Apr 19, 2013 at 18:01
  • Please search & read Commented Apr 19, 2013 at 18:07
  • Related: dba.stackexchange.com/questions/22020/… Commented Apr 19, 2013 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... Commented Apr 22, 2013 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. Commented Apr 23, 2013 at 9:28

1 Answer 1


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.

  • 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. Commented May 20, 2013 at 3:40
  • @AaronBertrand True.
    – IvanP
    Commented May 20, 2013 at 6:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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