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
withSeek Predicate: Revision > someRevision
-
If it isn't close, the server uses
Index Seek (NonClustered)
+Key Lookup (Clustered)
withSeek 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?
Best Answer
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:
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.