Whilst I would say the actual execution plan is the most useful, estimated execution plans have their place – sometimes you just need to see estimates or a quick verification that a change you have made has had some effect on plan shape. I find them helpful to quickly see if a change made to the code, an index, database setting or similar has had an effect on the execution plan without having to wait for the query to finish (especially if I am performance tuning a query that has a long run time).
sp_executesql is also useful – it helps us to execute dynamically created queries and supports parameterisation. It is also used by the .NET SqlCommand class to issue queries to the database engine in a parameterised form, assisting with SQL injection prevention along the way.
I recently found myself troubleshooting some code coming from an application. It was a Slow in the app, fast in SSMS problem so I was using sp_executesql to replicate the application behaviour. I also wanted to get the estimated execution plan. Here is an example query and what I was greeted with when I asked for the estimated plan:
Read on for one challenge you might find when trying to tune operations that you’ve built using sp_executesql, as well as what you can do about it.