every statement is parameterized

This is a common misconception that I hear from devs and DBAs alike… people seem to believe that SQL Server will parameterize every statement it runs and store the plan for re-use later.

However, this is not necessarily the case.

SQL Server has an inbuilt “safe” checker. Basically it will look at ad-hoc statements and determine whether they are “safe” or not. Those which are safe will be parameterized and placed in the plan cache. Those which aren’t are simply stored as a non-parameterized plan and these can cause a problem.

So what’s safe and what isn’t? Well surprisingly SQL Server has some very strict and incredibly restrictive rules around what is and isn’t safe.

These aren’t the complete list, but a subset of what makes a statement safe is as follows:

• FROM cannot have more than 1 table


• WHERE cannot have OR / in


• Sub queries are not allowed


• Consistent execution plans for all executions

As you can see, there are VERY few statements that could claim to be “safe” by SQL Server’s definition.

This means that your plan cache can become very bloated very quickly if you’re not careful. Even statements that look safe might surprise you:



So what’s wrong with this? Well nothing actually. If we look in the plan cache we’ll find that the statement is there and it is parameterized:

So let’s make use of that plan…



As you can see… it wasn’t re-used because SQL Server took a guess at the parameter type when it stored the plan in cache.

Therefore even “safe” plans can’t be trusted to be re-used at all times.

And to prove the point about the other plans, let’s take a look at a simple example:



So how does this show up in the plan cache?

As you can see, it’s a purely textual plan that can never be reused unless an exact textual match query is run again.

Therefore running lots of queries with different values could cause plan mayhem:

How can we get around this with ad-hoc statements? Well the best way is to use sp_executeSQL. Dynamic SQL using exec will simply produce the same result, but using sp_executeSQL and passing in a parameter will make SQL Server parameterize it in the plan cache.

Here’s a simple example:



Therefore, as you can see, if you want your code to produce re-usable plans, then you might have to reconsider the way in which you create and execute your ad-hoc statements.