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
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?