In addition to my recent post Running SP with Access pass-through query. set nocount on did the trick, but only for one day. My next try was to add with recompile option to store procedure declaration, and after 2 weeks I must admit this was a solution!
CREATE PROCEDURE [dbo].[MySP] @FilterExpr varchar(100)
with recompile
as
…
As your database has grown over time, the original query plan is no longer optimal. Thus the decrease in the first place.
ReplyDeleteSee also this TechNet articel about Recompile:
http://technet.microsoft.com/en-us/library/ms190439.aspx
But the basic problem is that the optimizer can not always choose the optimal plan due to dynamic SQL.
See Adam's article about it: http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/controlling-stored-procedure-caching-with-dyanmic-sql.aspx
Yes, this is what i found. Strange, that it worked more than 2 years without problem...
ReplyDeletehmm jepp quite strange.. any ideas why?
ReplyDelete