Use temporary stored procedures for performance tuning

I recently found out that when tuning stored procedures (e.g. for parameter sniffing), you shouldn’t try to tune the queries separately using local variables, e.g.

DECLARE @InputParameter INT = 1;

/* Query taken from stored procedure. */
SELECT [ColumeName]
FROM [TableName]
WHERE [Id] = @InputParameter;

This is because using local variables will result in SQL Server optimising the query for the density vector rather than using statistics for “real” values.

Instead, you should use temporary stored procedures, e.g.

CREATE PROCEDURE #StoredProcedureName
    @InputParameter INT
AS
    SELECT [ColumeName]
    FROM [TableName]
    WHERE [Id] = @InputParameter;
GO

EXEC #StoredProcedureName @InputParameter = 1;

Kimberly Tripp has a (much) more detailed blog post about variables & the density vector at https://www.sqlskills.com/blogs/kimberly/stored-procedure-execution-with-parameters-variables-and-literals/