You'll have to forgive my naivety as I'm not a DBA but my understanding is that over time the statistics of a database change and a stored procedure must be recompiled to keep the query plan up to date with the latest statistics.
Assuming I have a stored procedure in my database that is recompiled against the latest statistics at some regular interval, what are the implications of in-lining the stored procedure in code and wrapping it in an
sp_executesql statement? Do I lose the refreshing of the query plan that used to happen as part of the recompilation of the procedure?
If there's anything else (other than permissions) that I need to consider before I make this change then I'd appreciate your insights.
I read this on MSDN:
The ability of the SQL Server query optimizer to match the new Transact-SQL string with an existing execution plan is hampered by the constantly changing parameter values in the text of the string, especially in complex Transact-SQL statements.
So assuming the stored procedure I'm attempting to in-line and wrap in
sp_executesql does indeed contain some parameters, is this saying that although my execution plan is cached, I'm making it more difficult for SQL Server to find and reuse it?