1

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)?

  • 1
    Did you try OPTION(RECOMPILE)? – Erik Darling Jun 20 '17 at 18:29
  • @sp_BlitzErik I believe OPTION(RECOMPILE) would work as well, but this is a query that gets called often...I would need to check our CPU resources before opting for that option (no pun intended). – NeedleInAFullStack Jun 20 '17 at 18:39
  • Kitchen sink. – Aaron Bertrand Jun 21 '17 at 0:01
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.