Should you OPTIMIZE FOR default values in a dynamic search query?

Question

I have a dynamic search query that looks like this:

@CustID             INTEGER         = -2, 
@Name               NVARCHAR        = ''

--more parameters with default values...

Select x, y, z FROM MyTable 
WHERE 
(@custid = -2  OR CustomerID = @CustID) AND
(Name = ''     OR Name = @Name) AND
--more dynamic search conditions....

We've recently been having a lot of trouble with parameter sniffing and it causes major slowdowns, and even timeouts.

1) I first tried setting all my parameters to local variables, at that fixed the parameters sniffing, but the default query (the query with all default parameter values) was taking 5 seconds when before it would take 1 second. No more timeout level executions though.

2) Next I tried using OPTION (OPTIMIZE FOR (@CustID UNKNOWN, @Name UNKNOWN, ...)), but that essentially does the same thing as using local variables.

3) Last I tried still using OPTION (OPTIMIZE..., but instead of UNKNOWN, I used the default value of each parameter: OPTION (OPTIMIZE FOR (@CustID = -2, @Name = '', ...)). This kept the default search fast (still about 1 second, and it seems all other searches with other values still execute fast.

Is it a good or known practice to to this (#3)?

Answer 1

I would dynamically construct the query such that each combination of search values results in a different plan.

If you are using a stored procedure to do this, it might look something like this.

Do this in the tempdb:

USE tempdb;

Create a demo table:

CREATE TABLE dbo.DatabaseList
(
    ServerName sysname NOT NULL,
    DatabaseName sysname NOT NULL,
    CreateDate datetime NOT NULL,
    CONSTRAINT PK_DatabaseList PRIMARY KEY CLUSTERED 
    (
        ServerName ASC,
        DatabaseName ASC,
        CreateDate ASC
    )
) ON [PRIMARY];

Create a stored proc:

IF OBJECT_ID(N'dbo.SearchDatabaseList', N'P') IS NOT NULL
DROP PROCEDURE dbo.SearchDatabaseList;
GO
CREATE PROCEDURE dbo.SearchDatabaseList
(
    @ServerName sysname = NULL
    , @DatabaseName sysname = NULL
    , @CreateDate datetime = NULL
    , @Debug bit = 0
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @where nvarchar(max);
    DECLARE @connector nvarchar(max);
    DECLARE @statement nvarchar(max);
    DECLARE @vars nvarchar(max);

    SET @vars = N'
    @ServerName sysname
    , @DatabaseName sysname
    , @CreateDate datetime
    ';

    SET @statement = N'SELECT *
FROM dbo.DatabaseList dl
';

    SET @connector = '';
    IF @ServerName IS NOT NULL
        OR @DatabaseName IS NOT NULL
        OR @CreateDate IS NOT NULL
    BEGIN
        SET @where = N'WHERE ';
    END
    ELSE
    BEGIN
        SET @where = N'';
    END
    IF @ServerName IS NOT NULL 
    BEGIN
        SET @where = @where + @connector + N'ServerName LIKE @ServerName';
        SET @connector = N'
    AND '
    END
    IF @DatabaseName IS NOT NULL
    BEGIN
        SET @where = @where + @connector + N'DatabaseName LIKE @DatabaseName';
        SET @connector = N'
    AND '
    END
    IF @CreateDate IS NOT NULL 
    BEGIN
        SET @where = @where + @connector + N'CreateDate >= @CreateDate';
        SET @connector = N'
    AND '
    END
    SET @statement = @statement + @where;
    IF @Debug = 1 
    BEGIN
        PRINT @statement
        PRINT N'';
    END
    ELSE
    BEGIN
        EXEC sys.sp_executesql @statement, @vars
            , @DatabaseName = @DatabaseName
            , @ServerName = @ServerName
            , @CreateDate = @CreateDate;
    END
END
GO

Demo searches using the stored proc:

EXEC dbo.SearchDatabaseList @Debug = 1, @ServerName = N'SomeServer';

Running the above generates this query (running with @Debug = 0 will actually execute the query):

SELECT *
FROM dbo.DatabaseList dl
WHERE ServerName LIKE @ServerName

more examples...

EXEC dbo.SearchDatabaseList @Debug = 1, @DatabaseName = N'SomeDatabase';

output:

SELECT *
FROM dbo.DatabaseList dl
WHERE DatabaseName LIKE @DatabaseName

Just by create date:

EXEC dbo.SearchDatabaseList @Debug = 1, @CreateDate = N'2017-06-19T00:00:00';

output:

SELECT *
FROM dbo.DatabaseList dl
WHERE CreateDate >= @CreateDate

Search server and database:

EXEC dbo.SearchDatabaseList @Debug = 1, @ServerName = N'SomeServer'
    , @DatabaseName = N'SomeDatabase';

output:

SELECT *
FROM dbo.DatabaseList dl
WHERE ServerName LIKE @ServerName
    AND DatabaseName LIKE @DatabaseName

Search server, database, and create-date:

EXEC dbo.SearchDatabaseList @Debug = 1, @ServerName = N'SomeServer'
    , @DatabaseName = N'SomeDatabase'
    , @CreateDate = N'2017-06-19T00:00:00';

output:

SELECT *
FROM dbo.DatabaseList dl
WHERE ServerName LIKE @ServerName
    AND DatabaseName LIKE @DatabaseName
    AND CreateDate >= @CreateDate

This results in clean target queries that only contain the relevant where clauses, thereby allowing the SQL Server Query Optimizer to pick a plan that is relevant to the query, and generate multiple plans that can also be cached.

This approach could easily be replicated in C#, or whatever client you're using to submit the query.