SQL Server Performance Forum – Threads Archive

Same Query / Different Execution Plan

Does anyone know why a query would produce a different execution plan when
running in QA or a Stored procedure? I have a pretty simple query that,
when I run it in QA returns in less than a
second. When I put it in a stored procedure and execute it in QA with command exec
it takes over a minute. When I look at the
execution plans, they are different. What would cause this? I’m using SQL
Server 2000 in a windows environment. Thanks for the consideration,
Viktor. viksa

Zdravo Viktore (Hi Viktor), Does your stored procedure have any parameter? I guess when you execute code from qa you put constant in query instead of parameter. When you run proc, cached execution plan may be used. You can try to recompile sp and see what happens.

I have experienced this before aswell. Stored procedures are stored with their execution plan as it was at the time of the procedure creation, which can be affected later if any of the table structures or indexes were changed since that creation date. Try: EXEC &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />rocedure name> WITH RECOMPILE and see if performance is any better. If performance is still slow, Re-index the affected tables and see if that helps at all.

]]>