Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Every Statement Is Parameterized
Published: Oct 09, 2015
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:

use AdventureWorks2012

select *
from Person.Person
where BusinessEntityID = 55

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…

use AdventureWorks2012

select *
from Person.Person
where BusinessEntityID = 999

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:

select h.SalesOrderID
from sales.SalesOrderHeader h
join sales.SalesOrderDetail d
on h.SalesOrderID = d.SalesOrderID
where h.SalesOrderID = 50000

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:

exec sp_executeSQL N'select SalesOrderID
from sales.SalesOrderHeader
where SalesOrderID = @salesOrderID'
N'@salesOrderID int', 50004

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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.