SQL Server cached an execution plan which is not optimal in some cases and uses it for all consequent queries

Our application uses SQL Server 2014 and we got an issue related to the plan cache.

We have a parametrized query and its execution plan depends on parameter values. The server caches an execution plan which is not optimal in some cases and then uses it for all consequent queries.

Details:

We have a table consists of the following columns:

(
 [Revision] [bigint] IDENTITY(1,1) NOT NULL,
 [UserId] [uniqueidentifier] NOT NULL,
 ...A WHOLE LOT OF OTHER COLUMNS...
)

The meaning of those two columns is pretty clear, UserId is an Id of the user that the record belongs to, Revision is an auto-incrementing index of the record. Other columns are not important, but they exist and affect execution plans.

The table contains ~40.000.000 rows and ~200.000 distinct UserId values, so each user has 200 records in average. Rows are never updated, we use only INSERT and DELETE to modify data.

Our application executes the following query against this table:

SELECT * FROM SampleTable WHERE Revision > {someRevision} AND UserId = {someId}

The table has two indexes:

• Clustered index: Revision asc

• Non-Clustered index: UserId asc, Revision asc

When I execute this query manually, I see that the execution plan depends on the value of someRevision.

-
If it's relatively close to the current max value of Revision, the server uses Clustered Index Seek with Seek Predicate: Revision > someRevision

-
If it isn't close, the server uses Index Seek (NonClustered) + Key Lookup (Clustered) with Seek Predicate: UserId = someId AND Revision > someRevision.

Our application uses Linq-To-Sql and generates parametrized queries, they look like this:

exec sp_executesql N'SELECT * FROM [SampleTable] AS [t0]
WHERE ([t0].[Revision] > @p0) AND ([t0].[UserId] = @p1)',N'@p0 bigint,@p1 
uniqueidentifier',@p0=1234,@p1='bc38dd12-238c-41a2-9dea-bb12ce105e6d'

I used dm_exec_cached_plans, dm_exec_sql_text, dm_exec_query_plan and understood that the ser

This is called parameter sniffing, and it's covered extensively in Erland Sommarskog's epic post, Slow in the App, Fast in SSMS.

I can't even begin to do justice to it here, but sample solutions include:

• OPTION (RECOMPILE) - which causes increased CPU use for the plan compilation, plus loses historical metrics of the query execution, but can build a unique plan for each set of parameters (although it can still be a suboptimal plan in cases of cardinality estimation issues)

• Optimizing for a specific value - if you know your data well, you can use an OPTIMIZE FOR hint so that a plan is always built for a specific parameter value, regardless of what the user passed in. This is like creating technical debt - if your data skew changes, you may have to revisit your code in order to get a better plan.

• Using index hints - which are generally worse than optimizing for a specific value because not only are you bossing the query optimizer around, but if that index disappears, your query simply fails. SQL Server doesn't try to use an alternate index for your query.

• Plan guide - but if anything whatsoever changes about your query, even a single letter, then the plan guide will no longer match.

• Combination of query and index tuning - get the developers to avoid selecting * (all the fields), and just get the fields they truly need. Then, build a covering index to match, and you'll get a single query plan that works well for all parameters.

Head on over and tackle Erland's excellent article - not only will it pay dividends today, but it will continue to pay off over your career as you solve this problem again and again. The solution that works well for your query today is likely to be very different than the solution you use for another query tomorrow.

StackExchange Database Administrators Q#178151, answer score: 7