SP not using optimal exec plan

Question

SP not using optimal exec plan

sqlSushi

Mr or Mrs. 500

Points: 516
More actions
January 21, 2004 at 9:03 am

#107711

HI,

We have code that runs quickly in QA, but the same code in a stored procedure runs terribly. I have also noticed that the execution plan for the code in the proc when run is not optimal, yet the code when run in QA is executed optimally.
I have sp_recompiled the proc, DBREINDEX'ed the table.
Looking for any insight.

Thanks
Mark

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

That sounds like "parameter sniffing" with atypical values. Does your stored procedure have defaults (like null) for its parameters?

--Jonathan

ThomasH SSCertifiable Points: 7437 More actions · Answer 2

Also, does the SP produce #Temp tables with data you are "massively" processing or have many rows in them.

Once you understand the BITs, all the pieces come together

John Chiu SSCrazy Points: 2619 More actions · Answer 3

We had same problem, too. Are you on SQL 7 ? If do, you need go to SQL2K. Most of this problem disppear on SQL2K.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

Alter your stored procedure and place constant values in your queries, or assigning values to your variables in the stored procedure to test whether or not the procedure compiles with a better plan.

sqlSushi Mr or Mrs. 500 Points: 516 More actions · Answer 5

Thanks for all the posts,

_____________________________________________

That sounds like "parameter sniffing" with atypical values. Does your stored procedure have defaults (like null) for its parameters?

______________________________________________

Yes we are assigning default values to a few of the params, but are always passing in a proper value. There are two params that do have a default of null.

Can this effect a query? Come to think of it was this param that seemed funny in the plan. Does anyone have more info on this?

Thank you!

sqlSushi Mr or Mrs. 500 Points: 516 More actions · Answer 6

We are also using SQL 2K AS, and this SP in question used no temp tables.

Also, good idea on testing with constants.

Thank you