cfradenburg (7/7/2011)
OPTION (RECOMPILE) is to be only used as a temporary measure while a process for converting adhoc T-SQL code to Stored Procedures is underway.
I'm not sure I agree with this. I've seen cases where there's a statement that doesn't take a lot of resources to compile and depending on the parameters passed in different execution plans can be generated. Statements like that are good candidates for recompiles. Granted, it may be more efficient to compile it with a specific parameter but that depends on the situation. Using option recompile certainly isn't the first thing I would look at but there are times it may end up being the best tool.
Yes, e.g. in SAP environments all selects to the DB from the business application are going to the DB interface via parameterized SPs. But e.g. for the iDOCs the status will be changed after each processing of it and the status flag has a very high inequality in the distribution of values per status. And also the autoupdate of statistics don't runs often enough by the 20% thrashould value. We so run daily an update statistics manually for the status field and recompile all executions with an hint in the ABAP-code which going to the IDOC status table with very good performance results per execution. In this case, the distribution of date is unequally, the auto update statistics runs not often enough; the parameters are very different and the selects runs seldom in relation to other selects. Here the recompile is a very good option.
Suresh Kumar Maganti (7/7/2011)
Dear reader,What I meant by thae statement is the following:
Usually developers tend to use TSQL from their application instead of SP's. Then their plan cache fills up faster that usual. Then they use OPTION (recompile) as an answer to reduce plan cache contention. And continue to use adhoc TSQL code from their application. This kills the many benefits of SP's completely.
Thats absolutly understandable.
Harveysburger (7/7/2011)
...On an OLTP system in the past I personally never found WITH RECOMPILE useful.
Claude
Never say never 😉