During my testing, I discovered that estimated execution plans aren’t always generated. The SQL Server 2005 Books Online (BOL) topic “Transact-SQL Statements That Produce Showplans” has a complete list of query statements that don’t generate a plan, but what I found disappointing was the fact that statements using the sp_executesql stored procedure don’t generate an estimated plan. (I expect sp_executesql might show up in ad hoc reporting applications.) BOL also states that dynamic SQL statements don’t generate estimated plans. Just to be clear, in this case, dynamic SQL statements means SQL statements in the form EXEC @ SQL_Statement, where the variable @SQL_Statement contains the T-SQL query. You can certainly extract the query from the EXEC statement variable and pass it to SQL Server to generate an estimated plan. I imagine you could also develop code to parse statements that use sp_executesql to generate an estimated plan. However, the fact remains that you’re not always guaranteed an estimated plan. So if you decide to use estimated plans, make sure you incorporate error handling to account for situations where estimated plans are missing
