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.

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.

@JoelCoehoorn not at all. Neither does updating statistics, rebuilding indexes or things like that. The optimizer is convinced that it has the best plan and I need to convince it otherwise.
–
John BledsoeMar 8 '13 at 18:12

My only other suggestion is to look into index hints (blog.sqlauthority.com/2009/02/07/…). I don't think an index hint will work directly, but if you can get Sql Server to complain to you why it was unable to use a hint, that could point you in the direction you need to find a solution.
–
Joel CoehoornMar 8 '13 at 18:20

@JoelCoehoorn that's a good suggestion. I've been trying to use those to force the plan that I want and SQL Server does complain that it can't create a plan with that hint, but it doesn't give any indication as to why. I'm going to try to reduce the problem to the smallest possible instance and work from there.
–
John BledsoeMar 8 '13 at 18:36

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:

So the specific optimisation rule seems to be SelOnSeqPrj? Any thoughts on why the auto parameterised plan doesn't need an explicit OPTION (RECOMPILE) but the manually parameterised one does? Initially I was speculating that it might be something to do with potential NULL values but I see the auto parameterised tree contains ScaOp_Convert int,Null,ML=4 -> ScaOp_Identifier COL: @1
–
Martin SmithMar 9 '13 at 13:16

Actually I see in the auto parameterised case the initial memo structure has ScaOp_Comp (ScaOp_Identifier,ScaOp_Const) and the manual parametised case ScaOp_Comp (ScaOp_Identifier,ScaOp_Identifier) despite apparently both having (ScaOp_Identifier,ScaOp_Identifier) in the input tree
–
Martin SmithMar 9 '13 at 13:45

@MartinSmith I thought I had written about SelOnSeqPrj somewhere but I can't find it right now. The response to your comments is too long for this box, so I'll edit some extra details into my answer.
–
Paul WhiteMar 9 '13 at 23:29

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.

Actually, in this example KeyCol1 is of type uniqueidentifier, so I would think that if anything the first query (the one with a good plan) would have this problem and not the second (the one with the bad plan). However, the query run by the application is actually using sp_executesql, so I will double-check the data types of the parameters.
–
John BledsoeNov 29 '12 at 22:46

Running into this problem again and parameter data types are definitely not the issue.
–
John BledsoeMar 7 '13 at 15:05