SQLTeam.com | Weblogs | Forums

Force a query to use a certain plan


Or more specifically, to not use a certain plan.

Currently, we have a query that historically has used two different but similar plans. For some reason, SQL has chosen to use the plan that performs far worse on one of our databases. Since we use EF, I can't use hints to force it to use a certain plan. Can I delete the bad plan in SSMS? Will it just come back. What are my options as a DBA to get it to use the better plan (that the other 39 databases are using)?



hope migration went well.



Can you post the query that EF is generating?


Hi Jeff,

The query itself is very simple. See below. The problem is that this is an EF object in the code and the object is created from hundreds of locations throughout the codebase. So I don't see an easy way to substitute in a stored proc.

SELECT [GroupBy1].[A1] AS [C1]
FROM [dbo].[StorageLocations] AS [Extent1]
WHERE ([Extent1].[ClientId] = @p__linq__0)
AND ([Extent1].[IsDeleted] <> 1)
AND ([Extent1].[IsDeleted] <> 1)
AND ([Extent1].[StorageLocationType] = @p__linq__1)
AND (([Extent1].[DisplayPath] = @p__linq__2)
OR (([Extent1].[DisplayPath] IS NULL)
AND (@p__linq__2 IS NULL)))
) AS [GroupBy1]

One idea we had was to delete the index that the one query plan was using (a seek) but I queried the plan cache and it is being used by other queries so i would solve one problem and create many others I think.



A better idea would be to convert that to a stored procedure and have the front end call the sproc. Consider that there are two identical ANDs for IsDeleted and that you have a bit of "catch all" query thing going on with the @p__linq__2 thing and understand that it could be seriously optimized by conditionally getting rid of the "OR". That "OR" is likely the reason why it occasionally elects to not use the correct index (bad form of parameter sniffing).

Here's a very old but still very appropriate link to help solve such problems without the risk of SQL Injection.


Seems like I may be able to force a plan through the Query Store. I've never used it before so I will have to play around with it on QA...