Solving Parameter Sniffing with Multiple Execution Plans

Dynamic SQL has many uses and one of these can help us fix Parameter Sniffing issues. Here we’ll look at how it can be used to generate multiple execution plans for the same query.

Parameter sniffing is a common issue. Even for simple queries we can run into suboptimal plans being produced. There are multiple ways we can use Dynamic SQL to solve this challenge. Here we’ll demonstrate one technique: Comment Injection.

Let’s start in the StackOverflow database with a procedure and index:

CREATE OR ALTER PROCEDURE dbo.GetPopularUsers (
	@MinimumViews INT
) AS
BEGIN

	SELECT Id, DisplayName
	FROM dbo.Users
	WHERE [Views] >= @MinimumViews;

END
GO

CREATE INDEX [Views]
ON dbo.Users ([Views]);

Sniff sniff

Let’s see the sniffing in action. We’ll try to make use of our index and look for some of the most popular users:

EXEC dbo.GetPopularUsers @MinimumViews = 100000;

This plan makes good use of our new index and the query only needs 81 reads to complete. What happens if we try to run the same for a much lower threshold though?

EXEC dbo.GetPopularUsers @MinimumViews = 10;

Well now we get the same plan but now have nearly 3.5mil reads. The procedure reused the same plan which worked well for our larger threshold. The lookup at this scale however is very inefficient.

Let’s clear the proc cache and run again for the same threshold to see what it would prefer:

DBCC FREEPROCCACHE;     /* For demonstration only */

EXEC dbo.GetPopularUsers @MinimumViews = 10;

With there being so many users to return the engine abandons the index and goes straight in for the table scan. This results in only needing 140k reads which is 25x less than the previous execution.

Both of these plans are fine for their individual parameters. Building the plan for our first parameter (sniffing) provides a plan which isn’t efficient for both however.

Dynamic SQL to the rescue

We’re going to change our procedure to resolve this. We’ll convert our query to use parameterised Dynamic SQL as demonstrated last week.

That doesn’t fix anything by itself though, it’ll still generate a plan on first execution and stick with it. What we want is to generate different query text which will produce different plans.

Note, different query text, not queries themselves, that part is fine.

When the engine executes a query it will build and cache different plans if we provide different text. That includes comments.

Our approach here is to inject a comment based on the parameter passed in which will allow separate plans to be created. Here’s our modified procedure:

CREATE OR ALTER PROCEDURE dbo.GetPopularUsers (
	@MinimumViews INT
) AS
BEGIN

	DECLARE @Sql NVARCHAR(MAX);
	
	SET @Sql = 'SELECT Id, DisplayName
		FROM dbo.Users
		WHERE [Views] >= @Views;';

	IF (@MinimumViews < 1000)
		SET @Sql = @Sql + ' /* Large Dataset */';

	EXEC sp_executesql
		@stmt = @Sql,
		@params = N'@Views INT',
		@Views = @MinimumViews;

END
GO

The threshold should be defined based on analysis of how the query performs with different volumes of data. In this case I settled at 1000. Any parameters below this will have one plan, and above will have a different one – due to having the comment injected into the query.

How do our queries run now?

EXEC dbo.GetPopularUsers @MinimumViews = 100000;

EXEC dbo.GetPopularUsers @MinimumViews = 10;

That’s two queries which look identical but are executing with different plans. Very powerful.

This resolves the sniffing in our procedure. We now have each parameter running better regardless of which of them gets executed first. This will provide more tailed execution plans and result in more effective execution.

We can check the cache for these plans too:

SELECT cp.plan_handle, t.*, qp.*
FROM sys.dm_exec_cached_plans cp
	CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
	CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
WHERE t.text LIKE '(@Views INT)%'		/* Find our parameterised query */

We can see different plan handles and the two separate queries that has been produced from our Dynamic SQL in the new procedure.

Wrap up

Here we’ve looked at how we can use Dynamic SQL to help us generate multiple plans for a single stored procedure. This can allow us more flexibility when we have parameters which could vary results wildly.

We’ve demonstrated this by producing two different plans. This could be extended to introduce more, for example if you needed 3 to handle small, medium and large sets of results.

There are many solutions for parameter sniffing and this is just one of the potentials. As data distribution changes this method could falter and the threshold of 1000 may need to be adjusted for instance.

There are plenty of uses for Dynamic SQL and there’s a few more scenarios I’d like to demonstrate in the coming weeks, stay tuned!