During my parameter sniffing classes, people get a little exasperated with the complexity of the problem. Parameter sniffing is totally hard. I get it. At some level, it’d be great to just hit a magic button and make the whole thing go away.
So inevitably, somebody will ask, “What about the database-level setting called Parameter Sniffing? Can’t I just right-click on the database, go into options, and turn this damn thing off?”
What they THINK is going to happen is that SQL Server will do an OPTION(RECOMPILE) on every incoming query, building fresh plans each time. That ain’t how this works at all, and instead, I wish this “feature”‘s name was “Parameter Blindfolding.” Here’s what it really does.
Equality searches often benefit when you disable parameter sniffing.
I’ll take the Stack Overflow database, create an index on Users.DisplayName, and then build a stored procedure that queries users by their DisplayName:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE INDEX DisplayName ON dbo.Users(DisplayName); GO CREATE OR ALTER PROC dbo.usp_SearchUsersByName @DisplayName NVARCHAR(40) AS SELECT TOP 1000 * FROM dbo.Users WHERE DisplayName = @DisplayName ORDER BY Reputation DESC; GO EXEC usp_SearchUsersByName @DisplayName = 'Brent Ozar'; EXEC usp_SearchUsersByName @DisplayName = 'Alex'; |
The classic problem with parameter sniffing is that if I call the proc for a relatively rare name like Brent Ozar, SQL Server builds a plan that’s great for a limited number of rows – but then the plan gets reused for more common display names like Alex. There are about 10K Alexes, and the index seek + key lookup may make less sense there, and we get a spill to disk because SQL Server didn’t grant enough memory for the sort:
Folks think, “Well, that’s bad – I want to avoid parameter sniffing. So let’s turn off parameter sniffing, and that’ll fix it, right?”
|
1 2 3 4 |
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = Off; GO EXEC usp_SearchUsersByName @DisplayName = 'Alex'; |
Then try running it for Alex. The good news is that SQL Server doesn’t bother sniffing the parameters:
The bad news is that we have a different problem: SQL Server is now ignoring the incoming parameters and optimizing for the average DisplayName rather than the one being passed in.
But you know what? In some cases…that’s totally okay. You might actually want to optimize for the average value. In normal data distributions, this means you’re going to be optimizing for index seeks rather than table scans, and who doesn’t like index seeks? I like index seeks. I mean, they’re not my fetish, but I like ’em. They’re alright.
But range searches suck.
You’re not likely to do a range search on a DisplayName, so let’s switch columns and look at the LastAccessDate. We’ll turn parameter sniffing back on:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On; GO CREATE INDEX LastAccessDate ON dbo.Users(LastAccessDate); GO CREATE OR ALTER PROC dbo.usp_SearchUsersByLastAccessDateRange @StartDate DATETIME, @EndDate DATETIME AS SELECT TOP 1000 * FROM dbo.Users WHERE LastAccessDate >= @StartDate AND LastAccessDate < @EndDate ORDER BY Reputation DESC; GO /* Small date range: */ EXEC usp_SearchUsersByLastAccessDateRange @StartDate = '2018-06-01 09:00', @EndDate = '2018-06-01 09:30'; GO /* Big date range: */ EXEC usp_SearchUsersByLastAccessDateRange @StartDate = '2018-06-01 09:00', @EndDate = '2018-06-01 17:00'; GO |
When parameter sniffing is ON, the small date range sets the stage, uses the index, and gets the small grant, and that backfires for the big date range:
Classic parameter sniffing problem. So when we “fix” it by disabling parameter sniffing, and then run the small date range:
|
1 2 3 4 5 6 |
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = Off; GO /* Small date range: */ EXEC usp_SearchUsersByLastAccessDateRange @StartDate = '2018-06-01 09:00', @EndDate = '2018-06-01 09:30'; GO |
This causes several interesting problems:
- The row estimates are completely insane: 16% of the table
- This is way over the tipping point, so SQL Server ignores indexes
- The query will always use parallelism, regardless of how tiny the date range is
- The memory grant is staggeringly large: 5GB of memory gets erased when this query runs
Parameter Blindfolding makes a lot of queries worse.
If you only have equality searches, not range searches, you could probably disable parameter sniffing at the database level and do okay. If you’ve got range searches, though, Parameter Blindfolding is going to backfire, and you’re still going to have a lot of query tuning to do.
9 Comments. Leave new
I see this problem a lot in my work as an ERP consultant. Microsoft Dynamics stores information from multiple ‘companies’ in a single table with only a company field to segregate the data. This works fine as long as all companies are in balance, but quite often there is a subsidiary in Luxemburg or Panama with much less data then the office in London or Cologne. Inevitably there will be query plans based on the small companies that have a devastating effect on database performance in the big ones.
So far the only remedy I have been able to come up with is frequently flushing the query cache. Not quite the fairest of them all.
If the query also runs fine optimized for the average value, you might want to try adding “option (optimize for unknown)” to the query. It turns off parameter sniffing for that single query.
Or you can use this OPTION (RECOMPILE, USE HINT (‘ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES’, ‘DISABLE_PARAMETER_SNIFFING’));
GO
I wouldn’t recommend that because it causes constantly high CPU usage. You’re recompiling the same queries over and over again, but using the exact same cardinality estimates. There’s no point of the continuous recompilations.
Thank you, Brent.
Francesco, dear old colleague 🙂
You can force DATAAREALITERALS in AX which ensures it creates a specific plan for each company, but it will not do you any good if the company setup is mixed with bad choices in regards to shared/non-shared tables, then it will be a huge mess. It will give a more consistent result in query responses.
Flushing the query cache does indeed help, until the “wrong” plan is being cached again and it is of course very difficult to explain that to the administrator and/or end-user.
We have to stop meeting this way. Rumors will spread.
This was presented as the solution from Microsoft, but unfortunately this option does not fix the problem. To not hijack this topic, I propose we’ll take this discussion elsewhere though.
Do you still drink?
[…] Brent Ozar explains why you should keep parameter sniffing on: […]
I created a job for this. ?ts a basic solution but is worked. A query is checking queries which are running more than 1 second, and remove their query plans from the cache. This solves problem immediately. And job inserts problematic statement in the sp to a table after removing query plan. Then I am adding recompile hint to that statement only in the sp.