This is something I was sure I had written about before, but it turns out I haven’t. I’m also aware that there’s a lot of information out there on the topic, but I wanted to post about Adaptive Query Joins and this is a significant precursor, which is why I’m putting this blog out first.
So… parameter sniffing… what is it?
Basically, it works like this… a piece of code is running fine and then suddenly performance falls through the floor and we’ve no idea why.
This is generally parameter sniffing in action… the “good” query plan (let’s say a Nested Loop) has been thrown out of cache and a “bad” plan appeared in its place (let’s say a Hash Join).
Here we have it in action:
select SalesPersonID, TotalDue
from sales.SalesOrderHeader
where orderDate >= '2007-01-01'
and SalesPersonID = 285
option (recompile)
select SalesPersonID, TotalDue
from sales.SalesOrderHeader
where orderDate >= '2007-01-01'
and SalesPersonID = 277
option (recompile)
As we can see, we have a major difference in plan (and therefore performance) if we use SalesPersonID 285 or 277.
If we were to put this into a proc (without option recompile), parameterise the SalesPersonID and call the proc for the first time using SalesPersonID 285 then we would have the Nested Loop in cache and this would give us (generally, based on my dataset) good performance throughout all options.
Then, the plan drops out of cache (there are MANY reasons this can happen – most common being a stats refresh) and the next call is SalesPersonID 277… suddenly all calls now get the plan with the table scan and performance drops drastically.
Let’s see this in action:
create or alter procedure testProc
@salesPersonID int
as
select SalesPersonID, TotalDue
from sales.SalesOrderHeader
where orderDate >= '2007-01-01'
and SalesPersonID = @salesPersonID
go
So now we can run this a good few times forcing the Nested Loop plan to be used (it’ll be fast, but if we work in milliseconds we can track performance easily enough):
declare @time datetime = current_timestamp
exec testProc 285
exec testProc 274
exec testProc 290
exec testProc 282
select datediff(ms, @time, current_timestamp)
So let’s see what happens when the first plan generated is the Hash:
dbcc freeproccache
dbcc dropcleanbuffers
go
declare @time datetime = current_timestamp
exec testProc 277
exec testProc 274
exec testProc 290
exec testProc 282
select datediff(ms, @time, current_timestamp)
So… that’s parameter sniffing in action. Basically when you first run code SQL Server “sniffs” the parameter and uses it to generate an execution plan. That plan is then associated with the proc until it drops out of cache. Therefore if you have parameter sniffing problems you can end up with good performance in general, but then suddenly it can drop if the wrong plan gets into cache.
There are ways to avoid this, such as adding “OPTION (recompile)” to each affected statement (accepting there is a cost to recompilation which will be incurred). These aren’t going to be covered so much here because this is a lead into a SQL Server “fix” which is Adaptive Query Joins. The main aim was to acquaint you with Parameter Sniffing as a whole.