You have posted two execution plans.
The "good plan" uses an Index Seek on the Company table (to find all companies with the specified headCompanyId), then an Index Seek into the T-Subject table to find the rows for that company, plus a Key Lookup to find the rest of the row. (Since your query returns s.*, key lookups are unavoidable; please talk to your developers and get them to specify only the columns they need). For just a few hundred rows, this execution plan should perform very fast.
The "bad" plan uses the same Index Seek on the Company table, but then feeds that into a Hash Match join with a full Clustered Index Scan of the T_Subject table as its other input. The T_Subject table has almost 45 million rows and they are all read. That will indeed be slower.
However, I also see differences in number of rows. Not only in the estimates, but also in the actuals. This means that either the plans were captured on different systems (dev vs prod??), or at different times (perhaps the "good" plan was captured a few weeks ago?).
First things first. Since the plans are from different machines or from different times I need to ask this. On the machine where you get the "bad" plan, does the index used in the "good" plan even exist? The good plan indicates that it's using an index called "idx9" on the T_Subject table, with companyId as the leading index column. The query does not need an index with this specific name, but it does need an index on CompanyID. (Either by itself or as the first of the collection of indexed columns).
If such an index does not exist, create it and rerun the query. It will probably be used now. If such an index does exist, check if it's enabled. If it isn't, enable it and rerun the query. It will probably use the index now.
If you do have an index on T-Subject.CompanyId, AND it is enabled, AND the index is still not used, then we are getting in the "ouff this is hard" area. I will need more information before I can troubleshoot that. Please do the following.
By Hugo Kornelis 271 answered Jan 08 at 10:21 AM