SQL Server Execution plans

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gary Mazzone - 30 Sep 2008 13:46 GMT

OK I have a weird problem happening.....

I have a stored proc that will not run in 13 hrs. If I pull the select
statement out (that is all that is in the proc) and run it as an Ad Hoc query
it completes in 4 secs.

The execution plans for the proc and the query are completly different. Any
one have any ideas as to what could be causing this type of condition?

SQL Server is 2005 Enterprise Edition SP2

Reply to this Message

Tibor Karaszi - 30 Sep 2008 14:18 GMT

Google for "Parameter Sniffing". Also check out
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> OK I have a weird problem happening.....
>
[quoted text clipped - 6 lines]
>
> SQL Server is 2005 Enterprise Edition SP2

Reply to this Message

TheSQLGuru - 30 Sep 2008 19:03 GMT

Try executing the sproc using the WITH RECOMPILE option. (you will see this
in your investigations into parameter sniffing as Tibor recommended you do.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

> OK I have a weird problem happening.....
>
[quoted text clipped - 8 lines]
>
> SQL Server is 2005 Enterprise Edition SP2

Reply to this Message