Query is slow when using parameters but fast when using literal

Question

I have a query that runs against a pretty large table, I need to do a count on it.

If I use a literal the query runs in a few seconds but when I put the values in as variables (which I need to do) the query takes forever and presumably does a full table scan.

I've done quite a lot of reading about this and I understand it most likely to do with parameter sniffing, which I can't pretend that I understand it, I just want to know how I can fix it, otherwise, I'm going to have to fall back on calling it in c# with generated query strings.

This query runs in a few seconds..

  SELECT Count(Id) FROM  dbo.BeaconScan WHERE State = 'Archived' AND  LastSeen < '29 February 2020';

This one takes forever

DECLARE @Date DATE = '31 March 2020'
DECLARE @Status NVARCHAR(256) = 'Archived'
SELECT Count(Id) FROM  dbo.BeaconScan WHERE State = @Status AND  LastSeen < @Date;

Answer 1

SQL Server optimizes queries based on rowcount estimates and heuristics. These estimates can differ with with literals, local variables, or parameters.

With a literal or parameter (parameter declared in the app code and passed with the command), SQL Server estimates counts based on the actual value provided and the statistics histogram (if an index or statistics exist on the column). This generally results in accurate estimates and an optimal plan when stats are up-to-date.

With a local variable (T-SQL DECLARE statement) or OPTIMIZE FOR UNKNOWN query hint, SQL Server estimates counts based on the overall average density of values and ignores the actual value and histogram. This generally results in a compromise plan that might be good enough overall but can be suboptimal for certain values. Adding an OPTION (RECOMPILE) query hint to a query with local variables will instead use the actual local variable values for optimization and yield the same plan as if literals were specified.

Note that parameterized query plans without the RECOMPILE hint are cached and reused. Current parameter values are ignored when the plan is reused so the reused query plan might be suboptimal for the current parameter values. This is another case where OPTION (RECOMPILE) might improve performance.

Use the OPTION (RECOMPILE) hint judiciously, considering query execution frequency. The compilation overhead can outweigh the runtime savings for queries that are executed frequently (e.g. many times per second).

Answer 2

You should examine your actual plans to be sure that the problem is in the parameter sniffing. Only actual plan shows you actual number of rows vs expected + the parameter for which the plan was built.

If you want your query to use your actual parameter every time, you can add recompile option at query level:

SELECT Count(Id) FROM  dbo.BeaconScan WHERE State = @Status AND  LastSeen < @Date
option(recompile);

Answer 3

If you are using the stored procedure, to eliminate parameter sniffing, do this:

DECLARE @DateLocal DATE = @Date
DECLARE @StatusLocal NVARCHAR(256) = @Status

SELECT Count(Id) FROM dbo.BeaconScan WHERE State = @StatusLocal AND LastSeen < @DateLocal

Answer 4

With a literal date, the optimizer can determine if a SEEK will out perform a SCAN. If the table has many years of data, but the query only asks for data after 29 Feb 2020, the optimizer can determine that it needs a small data set and will SEEK. The query will run relatively quickly.

The optimizer views a variable date as unknown. Therefore, the optimizer must build a plan that accounts for dates like 1 Jan 2001 or 12 Dec 2012. Large datasets do better with SCAN (index scan or table scan). Given the unknown value, the optimizer will often select SCAN. The query will run much longer because it is reading every row and not using the indexes.

To avoid the unknown, you can use the OPTIMIZE FOR query hint. But, depending on your use case, that may be no different than just using a literal.

Parameter sniffing usually refers to stored procedures. To avoid, assign procedure parameter to a variable within the first line or two of the procedure. Not necessary to know the full explanation for parameter sniffing in order to avoid it.