SQL Server 2008 R2
I've got a stored procedure that was taking 30s to run. When I ran it in SSMS it would take under 100ms. The 1st thing I thought was parameter sniffing so I created local variables in the SP and assigned my params to them. That brought the time down to around 5s. I then realized that my SET options were different between SSMS and the ADO call. I set the default on SQL Server to SET ARITHABORT ON and that seemed to fix everything... temporarily.
Now it seems like everything works fine for some time but after the usecount on the cached plan gets into the several thousand range it slows down again. If I use FREEPROCCACHE to clear out the single plan, it speeds up only to slow down again after a few thousand executions. I tried the OPTION(RECOMPILE) hint on my main select in the SP but that only made things worse.
Any suggestions?
--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson
I've got a stored procedure that was taking 30s to run. When I ran it in SSMS it would take under 100ms. The 1st thing I thought was parameter sniffing so I created local variables in the SP and assigned my params to them. That brought the time down to around 5s. I then realized that my SET options were different between SSMS and the ADO call. I set the default on SQL Server to SET ARITHABORT ON and that seemed to fix everything... temporarily.
Now it seems like everything works fine for some time but after the usecount on the cached plan gets into the several thousand range it slows down again. If I use FREEPROCCACHE to clear out the single plan, it speeds up only to slow down again after a few thousand executions. I tried the OPTION(RECOMPILE) hint on my main select in the SP but that only made things worse.
Any suggestions?
--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson