Why does a parameterised query lead to an index scan but hardcoded values leads to index seek? 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.
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:


https://www.sqlshack.com/query-optimization-techniques-in-sql-server-parameter-sniffing/
https://www.mssqltips.com/sqlservertip/3257/different-approaches-to-correct-sql-server-parameter-sniffing/
https://hackernoon.com/why-parameter-sniffing-hurts-your-sql-query-performance-d73c0da71fbc


I hope above helps.