I had a query passing from Entity Framework which is a very expensive query with a terrible performance. while optimizing this query I found the problem in this part:

SELECT TOP 20 *
FROM       [dbo].[FilingCartable] AS [Extent1]  
INNER JOIN [dbo].[EstateFiles] AS [Extent7] 
   ON  [Extent1].[EstateFileId] = [Extent7].[IdEstateFile] 
   AND [Extent7].[Status] = 6   

Here the execution plan shows Index Scan for FilingCartable table. As soon as I comment the part AND [Extent7].[Status] = 6 , execution plan shows Index Seek for table FilingCartable and query runs so fast!

I want to know the reason why?

closed as too broad by sp_BlitzErik, mustaccio, McNets, RDFozz, Joe Obbish Dec 5 at 0:27

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • This would be easier to answer if we had the full execution plan (actual if possible) (you can provide that by posting it on www.pastetheplan.com; remember to edit the link into your question), and the details on your indexes (copies of the CREATE INDEX statements). CREATE TABLE commands might help, as well. – RDFozz Dec 4 at 22:56

I searched and the solutions I found were :

1- Force plan to seek index ( WITH (FORCESEEK) ) that I cant use this in Entity Framework
2- I used AND ([Extent7].[Status]%6 = 0)

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