Optimizing Execution Plans for Parameterized T-SQL

Question

EDIT: I've updated the example code and provided complete table and view implementations for reference, but the essential question remains unchanged.

I have a fairly complex view in a database that I am attempting to query. When I attempt to retrieve a set of rows from the view by hard-coding the WHERE clause to specific foreign key values, the view executes very quickly with an optimal execution plan (indexes are used properly, etc.)

SELECT * 
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = 20

However, when I attempt to add parameters to the query, all of a sudden my execution plan falls apart. When I run the query below, I'm getting index scans instead of seeks all over the place and the query performance is very poor.

DECLARE @ForeignKeyCol int = 20

SELECT * 
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = @ForeignKeyCol 

I'm using SQL Server 2008 R2. What gives here? What is it about using parameters that is causing a sub-optimal plan? Any help would be greatly appreciated.

For reference, here are the object definitions for which I'm getting the error.

CREATE TABLE [dbo].[BaseTable]
(
    [PrimaryKeyCol] [uniqueidentifier] PRIMARY KEY,
    [ForeignKeyCol] [int] NULL,
    [DataCol] [binary](1000) NOT NULL
)

CREATE NONCLUSTERED INDEX [IX_BaseTable_ForeignKeyCol] ON [dbo].[BaseTable]
(
    [ForeignKeyCol] ASC
)

CREATE VIEW [dbo].[ViewOnBaseTable]
AS
SELECT
    PrimaryKeyCol,
    ForeignKeyCol,
    DENSE_RANK() OVER (PARTITION BY ForeignKeyCol ORDER BY PrimaryKeyCol) AS ForeignKeyRank,
    DataCol
FROM
    dbo.BaseTable

I am certain that the window function is the problem, but I am filtering my query by a single value that the window function is partitioning by, so I would expect the optimizer to filter first and then run the window function. It does this in the hard-coded example but not the parameterized example. Below are the two query plans. The top plan is good and the bottom plan is bad.

Answer 1

When using OPTION (RECOMPILE) be sure to look at the post-execution ('actual') plan rather than the pre-execution ('estimated') one. Some optimizations are only applied when execution occurs:

DECLARE @ForeignKeyCol int = 20;

SELECT ForeignKeyCol, ForeignKeyRank
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = @ForeignKeyCol
OPTION (RECOMPILE);

Pre-execution plan:

Post-execution plan:

Tested on SQL Server 2012 build 11.0.3339 and SQL Server 2008 R2 build 10.50.4270

Background & limitations

When windowing functions were added in SQL Server 2005, the optimizer had no way to push selections past these new sequence projections. To address some common scenarios where this caused performance problems, SQL Server 2008 added a new simplification rule, SelOnSeqPrj, which allows suitable selections to be pushed where the value is a constant. This constant may be a literal in the query text, or the sniffed value of a parameter obtained via OPTION (RECOMPILE). There is no particular problem with NULLs though the query may need to have ANSI_NULLS OFF to see this. As far as I know, applying the simplification to constant values only is an implementation limitation; there is no particular reason it could not be extended to work with variables. My recollection is that the SelOnSeqPrj rule addresssed the most commonly seen performance problems.

Parameterization

The SelOnSeqPrj rule is not applied when a query is successfully auto-parameterized. There is no reliable way to determine if a query was auto-parameterized in SSMS, it only indicates that auto-param was attempted. To be clear, the presence of place-holders like [@0] only shows that auto-parameterization was attempted. A reliable way to tell if a prepared plan was cached for reuse is to inspect the plan cache, where the 'parameterized plan handle' provides the link between ad-hoc and prepared plans.

For example, the following query appears to be auto-parameterized in SSMS:

SELECT *
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = 20;

But the plan cache shows otherwise:

WITH XMLNAMESPACES
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT
    parameterized_plan_handle =
        deqp.query_plan.value('(//StmtSimple)[1]/@ParameterizedPlanHandle', 'nvarchar(64)'), 
    parameterized_text =
        deqp.query_plan.value('(//StmtSimple)[1]/@ParameterizedText', 'nvarchar(max)'),
    decp.cacheobjtype,
    decp.objtype,
    decp.plan_handle
FROM sys.dm_exec_cached_plans AS decp
CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp
WHERE
    dest.[text] LIKE N'%ViewOnBaseTable%'
    AND dest.[text] NOT LIKE N'%dm_exec_cached_plans%';

If the database option for forced parameterization is enabled, we get a parameterized result, where the optimization is not applied:

ALTER DATABASE Sandpit SET PARAMETERIZATION FORCED;
DBCC FREEPROCCACHE;

SELECT *
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = 20;

The plan cache query now shows a parameterized cached plan, linked by the parameterized plan handle:

Workaround

Where possible, my preference is to rewrite the view as an in-line table-valued function, where the intended position of the selection can be made more explicit (if necessary):

CREATE FUNCTION dbo.ParameterizedViewOnBaseTable
    (@ForeignKeyCol integer)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT
        bt.PrimaryKeyCol,
        bt.ForeignKeyCol,
        ForeignKeyRank = DENSE_RANK() OVER (
            PARTITION BY bt.ForeignKeyCol 
            ORDER BY bt.PrimaryKeyCol),
        bt.DataCol
    FROM dbo.BaseTable AS bt
    WHERE
        bt.ForeignKeyCol = @ForeignKeyCol;

The query becomes:

DECLARE @ForeignKeyCol integer = 20;
SELECT pvobt.*
FROM dbo.ParameterizedViewOnBaseTable(@ForeignKeyCol) AS pvobt;

With the execution plan:

Answer 2

You could always go the CROSS APPLY way.

ALTER VIEW [dbo].[ViewOnBaseTable]
AS
SELECT
    PrimaryKeyCol,
    ForeignKeyCol,
    ForeignKeyRank,
    DataCol
FROM (
    SELECT DISTINCT
        ForeignKeyCol
    FROM dbo.BaseTable
) AS Src
CROSS APPLY (
    SELECT
        PrimaryKeyCol,
        DENSE_RANK() OVER (ORDER BY PrimaryKeyCol) AS ForeignKeyRank,
        DataCol
    FROM dbo.BaseTable AS B
    WHERE B.ForeignKeyCol = Src.ForeignKeyCol
) AS X

Answer 3

I think in this particular case it may be because the data types between your parameters and your table do not match exactly so SQL Server has to do an implicit conversion which is not a sargable operation.

Check your table data types and make your parameters the same type. Or do the cast yourself outside the query.