Curse of the Catch-All query

The catch-all query – a fairly common pattern that is often seen in SQL Server apps where there is a list of results that can be filtered by one or more criteria.

This usually takes the form of a stored procedure that is constructed as below. The example stored procedure is created in the Stack Overflow 2010 Database, provided under cc-by-sa 4.0 license from Stack Exchange Data Dump and running in compatibility level 160 on a SQL Server 2022 instance installed on a VM with 8 cores and 35GB RAM.

CREATE OR ALTER PROCEDURE dbo.spSearchUsers
(
	@UserId INT = NULL, 
	@DisplayName NVARCHAR(40) = NULL,
	@Location NVARCHAR(100) = NULL,
	@Reputation INT  = NULL
)
AS
SELECT	[Id],
		[DisplayName],
		[Location],
		[Reputation],
		[LastAccessDate]
FROM	dbo.[Users]
WHERE	([Id] = @UserId OR @UserId IS NULL) AND
		([DisplayName] = @DisplayName OR @DisplayName IS NULL) AND
		([Location] = @Location OR @Location IS NULL) AND
		([Reputation] = @Reputation OR @Reputation IS NULL);

We have a query that SELECTs from the Users table that takes multiple optional parameters and filters the output based on those.

Whilst fairly easy to read and write, queries written in this way often underperform.

Let’s create some suitable indexes for this query. We already have a clustered index on Id so I will create three non-clustered indexes to support the other three predicates:

CREATE INDEX IX_DisplayName ON dbo.Users
(
	[DisplayName]
)
INCLUDE
(
	[Location],
	[Reputation],
	[LastAccessDate]
);

CREATE INDEX IX_Location ON dbo.Users
(
	[Location]
)
INCLUDE
(
	[DisplayName],
	[Reputation],
	[LastAccessDate]
);

CREATE INDEX IX_Reputation ON dbo.Users
(
	Reputation
)
INCLUDE
(
	[Location],
	[DisplayName],
	[LastAccessDate]
);

Now let’s run the query and see how it performs:

EXEC dbo.spSearchUsers @UserId = 1;
EXEC dbo.spSearchUsers @DisplayName = 'Leon';
EXEC dbo.spSearchUsers @Location = 'UK';
EXEC dbo.spSearchUsers @Location = 'UK',@DisplayName = 'Leon';

The STATISTICS IO output is:

Table 'Users'. Scan count 1, logical reads 2034, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 2034, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 2034, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 2034, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Each call above has performed 2034 reads which seems a lot especially since UserId = 1 returned 1 row and @Location = ‘UK’ returned 1341 – why would they be the same?

As always, the answer is in the execution plan:

We can see from the plan that each query has performed an index scan of IX_DisplayName rather than an index seek of the relevant index. Why?

The Culprits

There are two things at play here – parameter sniffing and plan re-use.

Parameter sniffing is a much larger topic than this blog post but briefly, upon the first execution of the stored procedure, SQL Server compiles a plan that will be used for each subsequent execution of the stored procedure (plan re-use) until the plan is evicted from cache for one of a variety of reasons. Plan re-use (or plan caching) is a SQL Server feature that is designed to reduce the overhead of continually compiling execution plans for the same query (if a query runs millions of times, that overhead can add up). The plan is compiled using the parameters of the first stored procedure call and then each subsequent execution will use that same plan.

In our case, the first call was EXEC dbo.spSearchUsers @UserId = 1; so the plan for all of the calls after is the same as the one SQL Server compiled for this query, which used an index scan on IX_DisplayName.

But wait…why did SQL Server compile this plan for the query? Why would it not choose to seek the clustered index straight to the value in question, given that clustered index is on the UserId column?

As the stored procedure is parameterised, SQL Server needs to compile a plan that is safe for any combination of parameters that may be passed. If it compiled a clustered index seek plan for @UserId = 1, that plan would not be valid if we passed a @UserId = NULL and @Location = ‘UK’ because SQL Server wouldn’t be able to seek the clustered index on UserId for Location = ‘UK’.

The seek plan we were hoping for is below:

So the index scan plan SQL Server compiled is valid for all of the possible parameter combinations we may pass to this stored procedure – valid? yes, optimal? no.

There are a number of ways to fix this problem.

Option 1 – OPTION (RECOMPILE)

We can add this hint to the end of the query in the stored procedure. What this hint does is tell SQL Server to compile an execution plan for the query at the time of each execution rather than compiling once at the first execution. As the plan is compiled at execution time, SQL Server will use parameter embedding optimization (PEO) to use the values of the parameters in the execution plan as if they were literals. If we change our stored procedure:

CREATE OR ALTER PROCEDURE dbo.spSearchUsers
(
	@UserId INT = NULL, 
	@DisplayName NVARCHAR(40) = NULL,
	@Location NVARCHAR(100) = NULL,
	@Reputation INT  = NULL
)
AS
SELECT	[Id],
		[DisplayName],
		[Location],
		[Reputation],
		[LastAccessDate]
FROM	dbo.[Users]
WHERE	([Id] = @UserId OR @UserId IS NULL) AND
		([DisplayName] = @DisplayName OR @DisplayName IS NULL) AND
		([Location] = @Location OR @Location IS NULL) AND
		([Reputation] = @Reputation OR @Reputation IS NULL)
OPTION (RECOMPILE);

And execute again:

EXEC dbo.spSearchUsers @UserId = 1;
EXEC dbo.spSearchUsers @DisplayName = 'Leon';
EXEC dbo.spSearchUsers @Location = 'UK';
EXEC dbo.spSearchUsers @Location = 'UK',@DisplayName = 'Leon';

We can see that each stored procedure call gave us the coveted index seek on the respective index:

This is because when it came to compile the plan, SQL Server knew the values of the parameters we had passed so was able to use that to work out the most optimal access method.

As with everything in life, there is no free lunch and there are drawbacks to this approach. Firstly, the recompilation has a CPU cost. The whole point of plan caching is to reduce the CPU overhead of constant compilation, if this stored procedure is run frequently, this could increase the server CPU demand, however, if it is run infrequently, it will likely be OK. Secondly, the plan is not cached and therefore doesn’t appear in the plan cache meaning it will be invisible to tuning methods that use the plan cache so any issues with this plan could be extremely difficult to track down.

Option 2 – Dynamic SQL

The dynamic SQL solution involves dynamically building the WHERE clause based on the parameters that have been passed to the stored procedure and then executing via sp_executesql. By taking this approach, we remove the OR @Variable IS NULL predicate which is holding us back from doing the index seek.

My approach to this is below:

CREATE TYPE dbo.ParameterTable AS TABLE
(
    [Name]	  VARCHAR(255),
    [Type]    VARCHAR(255),
    [Value]   SQL_VARIANT
);

GO

CREATE OR ALTER PROCEDURE dbo.spSearchUsers
(
	@UserId INT = NULL, 
	@DisplayName NVARCHAR(40) = NULL,
	@Location NVARCHAR(100) = NULL,
	@Reputation INT  = NULL
)
AS

/* the base query */
DECLARE @sql NVARCHAR(MAX) = N'SELECT	[Id],
	[DisplayName],
	[Location],
	[Reputation],
	[LastAccessDate]
FROM	dbo.[Users]
WHERE	(1=1)';

DECLARE @params dbo.ParameterTable;

/* build up the SQL Query and the parameter definitions based on what was passed to the stored procedure */
IF @UserId IS NOT NULL 
BEGIN
	SET @sql += N' AND [Id] = @1';
	INSERT INTO @params VALUES ('@1','INT',@UserId);
END

IF @DisplayName IS NOT NULL 
BEGIN
	SET @sql += N' AND [DisplayName] = @2';
	INSERT INTO @params VALUES ('@2','NVARCHAR(40)',@DisplayName);
END

IF @Location IS NOT NULL 
BEGIN
	SET @sql += N' AND [Location] = @3';
	INSERT INTO @params VALUES ('@3','NVARCHAR(100)',@Location);
END

IF @Reputation IS NOT NULL 
BEGIN
	SET @sql += N' AND [Reputation] = @4';
	INSERT INTO @params VALUES ('@4','INT',@Reputation);
END

DECLARE @ParameterAssignment NVARCHAR(MAX) = '';
SELECT	@ParameterAssignment = ISNULL(@ParameterAssignment + ';','') + 'DECLARE ' + Name + ' ' + Type + ' = (SELECT CAST(Value AS ' + Type + ') FROM @p1 WHERE Name = ''' + Name + ''')'
FROM	@params;

SET @sql = @ParameterAssignment + ';' + @sql;

EXEC sp_executesql @sql,  N'@p1 dbo.ParameterTable READONLY', @params;

It has to be said – this is considerably more complex and less readable than the OPTION (RECOMPILE) solution, and harder to debug. This approach will also create multiple plans in the plan cache – one for each parameter combination (assuming it gets executed for each) though on any reasonably spec’ed server, this shouldn’t really cause too much plan cache bloat.

Walking through this alternative approach, first we had to create a user defined type. The reason for this is that we need to parameterise sp_executesql to remove the risk of SQL injection. To do this, we need to pass sp_executesql three parameters – firstly, the query text, secondly, a string with the definitions of the parameters, and finally, a list of the arguments for the query parameters we defined in the second sp_executesql parameter. The number of query parameters must be dynamic to match what was passed to the stored procedure and must be in the form @parameter1 = value, @parameter2 = value. We build this parameter list dynamically in the stored procedure using a table variable but a table variable cannot directly be passed to sp_executesql, as you will get the error below:

Msg 206, Level 16, State 2, Line 0
Operand type clash: table is incompatible with ParameterTable

With the reasoning for the table type now cleared up, let’s clarify what the new version of the stored procedure itself does. We can see that the string is built up in a variable called @sql and we add predicates to the string for each parameter that is passed, as well as adding the parameter value to @params.

If we call the procedure now…

EXEC dbo.spSearchUsers @UserId = 1;
EXEC dbo.spSearchUsers @DisplayName = 'Leon';
EXEC dbo.spSearchUsers @Location = 'UK';
EXEC dbo.spSearchUsers @Location = 'UK', @DisplayName = 'Leon';

..you will notice three things:

We get the seeks we were…erm…seeking
I have split this up into four separate screenshots and the eagle-eyed will notice the query numbers which show I have missed some queries out from my screenshots. This is because each call now includes three other queries – The table variable insert, the @ParameterAssignment variable assignment and the extracting of the values from the table variable by sp_executesql. These have been removed from the screenshot purely for the sake of brevity
The estimates are different between the OPTION (RECOMPILE) version and the sp_executesql version. This is because, due to the simplicity of the dynamically created queries in the new version of the stored procedure, the plans are considered trivial and therefore the estimation process is different. This is a topic outside of the scope of this post

Conclusion

We have seen that catch-all queries can be bad for performance but found two ways to fix this. As with most things SQL Server related, the “best” option differs depending on the query, workload, hardware etc and should be considered on a case by case basis.