Posted by Erland Sommarskog on 01/04/07 22:44

kmounkhaty@yahoo.com (smounkhaty@bremer.com) writes:
> When I ran my adhoc script below it generated only 45000 reads or 4
> seconds but when I wrapped it into procedure it took about two minutes
> or millions of reads. The parameters calling both adhoc and proc are
> indeed the same. I'm pretty 99.9% sure that the proc does not recompile
> because I don't mix up between DDL and DML, no temp tables or any thing
> to cause proc to recompile. The big difference is adhoc used index scan
> for 45% but proc used bookmark lookup for 75%. Why it's so difference
> since they both returned the same results?

Run this:

select objectproperty(object_id('yoursp'), 'ExecIsAnsiNullsOn'),
objectproperty(object_id('yoursp'), 'ExecIsQuotedIdentOn')

If any of these return 0, recreate the procedure and make sure that
the settings ANSI_NULLS and QUOTED_IDENTIFIER are ON. This matters if
there is an indexed view or an index on a computed column. They can
only be used if these two settings are active, and these two are saved
with the stored procedure.

It could also depend how you pass the parameters, as Hugo discussed,
but we don't that. If the script is your actual ad-hoc script, it
may be that it works better if you copy the parameters to local variables.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]