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
