5

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:

  1. Clustered index: Revision asc
  2. 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 server put a single plan for this query into the cache. So, if the query with the corresponding value of Revision came first, the plan using Clustered Index Seek would be stored in the plan cache and then would be used for all the consequent queries.

It leads to an excessive number of logical reads (x10000) and unacceptable execution time for queries which should be executed using the second plan (Index Seek (NonClustered) + Key Lookup (Clustered)).

Also I noticed that the threshold where the server switches between plans (the tipping point) depends on statistics, if it's stale, the plan can be sub-optimal even regardless the cache, because the server incorrectly estimates the number of rows with Revision greater than the given one.

In addition, we have a large set of similar tables with similar use cases and all of them have the same issue.

What can I do to solve this issue?

I could try to use OPTION (RECOMPILE), which is not easy with Linq-To-Sql, but it also doesn't look really optimal in performance terms.

Also I could use sp_create_plan_guide or hack Linq-To-Sql even more and try to WITH (INDEX(...)) clause to force using the second plan, but as I said there is a lot of tables with the same core structure, so this way looks like a lot of manual work.

Generally, my questions:

Can SQL Server understand that the plan stored in the cache is not optimal for given parameters and don't use it?

Are there some best practices of handling parametrized queries if their optimal execution plans depend on parameters?

  • 1
    Consider implementing the clustered (perhaps unique) index/primary key on UserID and Revision. I suspect that will avoid your parameter sniffing problem and jumping through hoops to avoid it. It's unclear if an index on Revision alone will be useful to other queries.\ – Dan Guzman Jul 6 '17 at 11:46
  • @DanGuzman We use this clustered key, because it allows all INSERT's to be stored at the end of the clustered index. I suppose that the clustered key starting with UserId leads to inserts to random physical places (I haven't tested it yet though) – RomanG Jul 6 '17 at 12:01
  • You are correct that a random GUID will slow inserts, especially a concern with spinning media. This can be mitigated with incremental GUID values assigned via a NEWSEQUENTIALID() default constraint or UuidCreateSequential in app code along with some byte swapping (dbdelta.com/improving-uniqueidentifier-performance).. – Dan Guzman Jul 6 '17 at 12:07
  • @DanGuzman That's an interesting function, haven't seen it yet. I guess it isn't applicable in this case, since UserId is not a record Id, but Id of an owner of the record, but If I have used this function, would it have worked correctly if I moved my database to another server? As I see the sequence is correct only on a specified computer. – RomanG Jul 6 '17 at 12:20
  • A nonclustered index that covers the query would be an equivalent solution to organising the table by (UserID, Revision), but would cost a bit more to keep updated. While inserts at random points in the data file aren't ideal, SQL Server is designed to store and retrieve data that way, and I think it will avoid you being hurt when there are out-of-date statistics, which also are part of SQL Server's design. – Robert Carnegie Jul 6 '17 at 12:25
7

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.