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.
