Run-time Execution Plan Options

re: Run-time Execution Plan Options

Alexander Kuznetsov — Sun, 22 Feb 2009 02:32:17 GMT

Joe,

It's a great post! Can you change the third tag from Query Hinys to Query Hings?

re: Run-time Execution Plan Options

noeldr — Mon, 23 Feb 2009 15:45:23 GMT

"SQL Server already has statement level recompile for multi-statement stored procedures." Yeah, which is BUGGY! Watch out for that: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=386810

re: Run-time Execution Plan Options

jchang — Thu, 26 Feb 2009 15:22:57 GMT

ok, this just jogged my memory, last year I saw an application, where the stored procedures, instead of writing a simple query joining several tables, had one table valued function that joined to the remaining tables.

I think this over came the problem of the stored procedure picking up inconsistent parameters on compile. For their particular case, a simple WITH RECOMPILE on the stored procedure seemed to be the best solution as the compile cost was low in relation to what could be afforded for the stored procedure, even when few rows are affected. I assume MS will eventually debug this, but always verify and consider alternatives.