Why does a parameterised query lead to an index scan but hardcoded values leads to index seek? [duplicate]

Question

I'm getting significantly different performance from a query that uses parameters vs the same query where the same values are hardcoded in the where clause.

It comes down to 2 child tasks in the execution plan. The hardcoded query can perform an index seek but the parameterised query does an index scan. Since the table contains a million rows the difference is massive.

The query is pretty ugly...

DECLARE @MethodOfDelivery nvarchar(3) = 'AAA'
DECLARE @OutwardPostcode nvarchar(4) = 'BBB'
DECLARE @InwardPostcode nvarchar(3) = 'CCC'

SELECT TOP 1
    slr.*
FROM
    [dbo].[SortLevelRecord](NOLOCK) slr
    INNER JOIN dbo.RoutingDataVersion(NOLOCK) v ON v.Version = slr.Version
    INNER JOIN dbo.Routing(NOLOCK) psr ON
        (
            slr.SortLevelKey = psr.Day1SortLevelKey OR
            slr.SortLevelKey = psr.Day2SortLevelkey OR
            slr.SortLevelKey = psr.Day3SortLevelkey OR
            slr.SortLevelKey = psr.Day4SortLevelkey OR
            slr.SortLevelKey = psr.Day5SortLevelkey OR
            slr.SortLevelKey = psr.Day6SortLevelkey OR
            slr.SortLevelKey = psr.Day7SortLevelkey OR
            slr.SortLevelKey = psr.Day8SortLevelkey OR
            slr.SortLevelKey = psr.Day9SortLevelkey OR
            slr.SortLevelKey = psr.Day10SortLevelkey OR
            slr.SortLevelKey = psr.Day11SortLevelkey OR
            slr.SortLevelKey = psr.Day12SortLevelkey OR
            slr.SortLevelKey = psr.Day13SortLevelkey OR
            slr.SortLevelKey = psr.Day14SortLevelkey
        ) AND
        psr.Version = v.Version AND
        psr.MethodOfDelivery = slr.MethodOfDelivery AND
        psr.OutwardPostcode = @OutwardPostcode AND
        (
            psr.InwardPostcode = @InwardPostcode OR
            psr.InwardPostcode = SUBSTRING(@InwardPostcode, 1, 1) OR
            psr.InwardPostcode = ''
        )
WHERE
    slr.MethodOfDelivery = @MethodOfDelivery  
ORDER BY
    LEN(psr.InwardPostcode) DESC

The index scan/seek is on the Routing table.

Something else that seems a bit strange is that the parameterised query uses Parallelism to gather streams but there are no parallel operations, only 1.

This is on Azure SQL server. I'm not sure what version that correlates to.

Answer 1

This is what is called parameter sniffing. What happens is when you execute a query, the underlying plan is stored and reused however that particular plan may not suit your variable. This is a normal and expected behavior in SQL Server. Because compiling queries is expensive and takes time, SQL server stores plan in the cache and as a DBA we want our SQL Server to re-use them as much as possible so that we can save our time and resources.

There is a very nice video from SQL Server SME Mr. Brent Ozar listed here which can give you full details on parameter sniffing, possible work-around and what should be done in order to avoid the same.

What you have asked in the question, almost same thing has been explained by Mr. Greg Larsen and you would get detailed info on variable and hard coding the values at this link.

You may also read details of parameter sniffing for azure on this link.

Few more interesting reads on the same topic:

1. https://www.sqlshack.com/query-optimization-techniques-in-sql-server-parameter-sniffing/

2. https://www.mssqltips.com/sqlservertip/3257/different-approaches-to-correct-sql-server-parameter-sniffing/

3. https://hackernoon.com/why-parameter-sniffing-hurts-your-sql-query-performance-d73c0da71fbc

I hope above helps.

Answer 2

Disclaimer: I don't know SQL-server so this is more of a general idea why this may happen.

When a query is sent to the DBMS for the first time it is compiled and then stored in a cache. When the same query is sent again the DBMS can reuse the compiled statement. A query like select x from y where z=3 is in this regard distinct from select x from y where z=4, i.e. the query must be compiled again, and it will occupy memory in the cache. If the cache is full one or more compiled queries has to be sacrificed and thrown away. This is one major reason to use a parameter marker in this query: select x from y where z=?

The down-side is that if there is a skew distribution:

---
|z|
---
|3|
|4|
|4|
|4|
--- 

there's no way to tell how many rows that will be returned by:

`select x from y where z=?`

so some calculated value has to be used. A reasonable approach is to take the number of rows on the table, divided by the number of distinct values in z. I.e. 4/2=2.

If we, on the other hand, have distribution statistics at hand and use a query like select x from y where z=4 we can use the distribution statistics to better predict the number of rows that will be returned from the predicate. Assume a table with 1000 rows and 4 distinct values for a column x and distribution statistics like:

x | cnt
-------
1 | 500
2 | 400 

If we ask a query like WHERE x=1 we immediately know that 500 rows match this predicate. What about WHERE x=3? We know that there are two values for x that is unknown and that there are 1000-500-400=100 possible rows. On average we can assume the result to be 100/2=50 rows. If we, on the other hand, ask a query like WHERE x=?, we would have to assume 1000/4=250 rows returned.

So, using literals over parameter markers may change the execution plan at the cost of more compilations, and more memory used in cache. In general, one may use literals for low cardinality (distinct values) columns and skew distributions, and parameter markers for high cardinality columns and uniform distributions.