Many of you are troubleshooting Parameter Sniffing issues the wrong way in production. In this three-part series, I’ll show you how to troubleshoot it the right way:
- Getting the Compiled Parameter Values
- Getting the App’s SET Options
- Avoiding the Density Vector
This is part 1 in the series.
Parameter Sniffing 101
When a query suddenly degrades in performance, my first thought is always a bad execution plan. If it’s a query that gets executed frequently, there’s usually higher CPU utilization when there’s a bad plan in the plan cache.
You’ll know if that’s the case if the production emergency goes away after you’ve removed the bad plan from the plan cache. But wait! Before you take corrective action on the problematic query, save the plan. You are going to need that plan to continue troubleshooting after you have removed it from the plan cache.
There are various ways to get the plan for a query. I’m going to use sp_BlitzCache.
We’ve got two rows here as sp_BlitzCache has info about the stored procedure and the one statement inside it. Click on the “Query Plan” value for either row. Save it as a sqlplan file.
Now that you’ve saved the plan, you can fix the production emergency by removing the plan from the plan cache. If it’s a stored procedure that I’m dealing with, then I’ll use sp_recompile as I’m old school like that. Otherwise, I’ll use DBCC FREEPROCCACHE(<sqlhandle>) or DBCC FREEPROCACHE(<planhandle>).
EXEC sp_recompile 'GetPostsByUser'
If the production emergency stops, you know you’ve found a bad execution plan and now need to see what can be done to avoid this in the future.
You can quickly get the stored procedure code by right-clicking on the plan anywhere that’s white (not an operator) and selecting “Edit Query Text…”
CREATE PROCEDURE GetPostsByUser
SET NOCOUNT ON;
SELECT Users.Id, Users.DisplayName, Posts.Id, Posts.CreationDate, Posts.Title, Posts.Score, PostTypes.Type
JOIN Users ON Posts.OwnerUserId = Users.Id
JOIN PostTypes ON Posts.PostTypeId = PostTypes.Id
WHERE Posts.OwnerUserId = @UserId;
You may see that the query text is truncated if your query is obnoxiously long. Mine is short, so we get the whole thing.
Getting the Compiled Parameter Values
The next step is getting the parameter values that were used when the query was compiled.
Right-click in the white area of the plan and select “Show Execution Plan XML…”
Scroll to the bottom and locate the value for ParameterCompiledValue.
When this query was compiled, the stored procedure was executed with @UserId = 26615. The execution plan was optimized for @UserId = 26615.
When the execution plan is optimized for @UserId = 26615, does it perform well for other @UserId values? If the answer is no, then this one is considered a bad execution plan. It was good for @UserId = 26615 though.
You can also use sp_BlitzCache to get the compiled values.
Click on the “Cached Execution Parameters” value for either row.
You aren’t quite ready to start testing yet. Next up: