Parameter Sniffing?
I often see clients using forced plans or plan guides (yes, even still, to this day) to deal with various SQL Server performance problems with plans changing.
There’s usually an execution plan or two floating around that seems to be a good general idea for a given query, and a couple weird high-end and low-end outliers for very specific populations of values.
This is especially common in third party vendor environments where code and/or index changes may not be allowed without the okay from the high priest of tech support who only answers questions when their celestial craft passes near Earth every 27 years.
Of course, forced plans and plan guides can both fail. You may also run into a “morally equivalent plan” in Query Store that looks quite morally ambiguous.
Recently while working with a client, we came across just such a scenario. And of course, of the many reasons why a forced plan might fail, this one was just a… general failure.
The fix we came up with was to track down the compile values for that nice middle ground plan, and use OPTIMIZE FOR to push that plan shape into reliably reality.
Territory
Here’s a close enough approximation to what we did, with a good-enough demo. Trying to get a more realistic one was hard without a much more complicated schema, which the Stack Overflow is not.
An index!
CREATE INDEX
p
ON dbo.Posts
(OwnerUserId)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
And a procedure!
CREATE OR ALTER PROCEDURE
dbo.OptimizeForStuff
(
@ParentId integer = NULL,
@PostTypeId integer = NULL,
@OwnerUserId integer = NULL
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
SELECT TOP (1000)
p.*
FROM dbo.Posts AS p
WHERE (p.ParentId = @ParentId OR @ParentId IS NULL)
AND (p.PostTypeId = @PostTypeId OR @PostTypeId IS NULL)
AND (p.OwnerUserId = @OwnerUserId OR @OwnerUserId IS NULL)
ORDER BY
p.Score DESC,
p.Id DESC;
END;
All good so far, even if it does have an air of laziness.
Darwin
The problem was that when the query executed something like this:
EXEC dbo.OptimizeForStuff
@OwnerUserId = 22656,
@ParentId = NULL,
@PostTypeId = 2;
It got a good-enough fast plan:
But when the query executed in almost any other way:
EXEC dbo.OptimizeForStuff
@OwnerUserId = 8,
@ParentId = 0,
@PostTypeId = 1;
EXEC dbo.OptimizeForStuff
@OwnerUserId = 1349,
@ParentId = 184618,
@PostTypeId = 2;
It got this sort of lousy plan.
Even Worse
When stranger executions came along, things got way worse!
EXEC dbo.OptimizeForStuff
@OwnerUserId = NULL,
@ParentId = 0,
@PostTypeId = 1;
EXEC dbo.OptimizeForStuff
@OwnerUserId = NULL,
@ParentId = 184618,
@PostTypeId = 2;
We need to avoid all of this.
Step Up
Here’s what we did (again, round about) to make sure we got the generally good plan across the board, without failures!
CREATE OR ALTER PROCEDURE
dbo.OptimizeForStuff
(
@ParentId integer = NULL,
@PostTypeId integer = NULL,
@OwnerUserId integer = NULL
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
SELECT TOP (1000)
p.*
FROM dbo.Posts AS p
WHERE (p.ParentId = @ParentId OR @ParentId IS NULL)
AND (p.PostTypeId = @PostTypeId OR @PostTypeId IS NULL)
AND (p.OwnerUserId = @OwnerUserId OR @OwnerUserId IS NULL)
ORDER BY
p.Score DESC,
p.Id DESC
OPTION
(
OPTIMIZE FOR
(
@OwnerUserId = 22656,
@ParentId = 0,
@PostTypeId = 2
)
);
END;
Which gets us the original fast plan that I showed you, plus faster plans for all the other executions.
For example:
EXEC dbo.OptimizeForStuff
@OwnerUserId = 8,
@ParentId = 0,
@PostTypeId = 1;
EXEC dbo.OptimizeForStuff
@OwnerUserId = 1349,
@ParentId = 184618,
@PostTypeId = 2;
Go from 1.5 seconds to ~300ms:
And the two outlier queries improve quite a bit as well (though neither one is exactly great, admittedly).
EXEC dbo.OptimizeForStuff
@OwnerUserId = NULL,
@ParentId = 0,
@PostTypeId = 1;
EXEC dbo.OptimizeForStuff
@OwnerUserId = NULL,
@ParentId = 184618,
@PostTypeId = 2;
In all cases, the plan is generally better and faster, and sharing the plan across (though imperfect for the outliers) tamped down the extreme performance issues that were there before with attempts at forced plans.
Posit Hell
While I’m no great fan of OPTIMIZE FOR UNKNOWN, using a specific value can act like a less faulty version of plan forcing.
You shouldn’t pull this out every time, because it is a bit of duct tape to keep a sinking ship above water, but in oddball cases, it can be a quick and rather painless fix.
At some point, better solutions should be explored and implemented, but emergencies don’t generally allow for the greatest care to be taken
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
