I do not have indexed views or an index on a computed column in this situation.
I had quoted identifiers and ansi nulls off and tried them on. I have already
run a trace and there was only one recompile but not on the stored procedure
that seems to be the problem. Parameters are passed to a cursor in a stored
proc that seems to be the cause. The stored procedure now takes 10x as long as
it took before. Most of the events returned are SP:statementcompleted.
I also dropped and recreated the tables involved and it got worse.
Any other suggestions?
Erland Sommarskog wrote:
> There are plentyful of possible explanations, and with your vague
> description it is very difficult to say.
> First thing to check is run this:
> SELECT objectproperty(object_id('proc_name'), 'IsQuotedIdentOn'),
> objectproperty(object_id('proc_name'), 'IsAnsiNullsOn')
> Both these should return 1. If they do not, reload the stored procedure
> and make sure that you have SET QUOTED_IDENTIFIER ON and SET ANSI_NULLS ON
> when you do this. This may have the effect of a miracle - or no effect at
> all. This test covers the case that there is a indexed view or an index
> on a computed column involved. These two features requires these two
> settings to be on - together with four more settings. What is special
> with these two settings is that they are saved with the stored procedure.
> A more trivial explanation is that the stored procedure includes a
> query which balances between a good and a bad query plan. SQL Server
> uses a cost-based optimizer which uses statistics about the data.
> Most of the time it does a good job, but every now and they it grabs
> the wrong plan.
> Use the Profiler to narrow down exactly which statement is cause the
> problem. You can also use the Profiler to catch the query plan.
> When you run a Profiler trace, include the SP:Recomile event. If you
> have plenty of these, it may be the recompiles that kills you.