totaly wrong plan
I've updated statistics but it does not help.I 've tried option recompile. it is very strange
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.
- In Plan Explorer, enter the original query as the Command Text, verify that "With Live Query Profile" is selected under the "Get Actual Plan" button, then click the Get Actual Plan button and continue through the dialog.
- Next, go back to the Command Text and change this line: "from dw..t_subject s" to read "from dw..t_subject s with (INDEX = [name of index], FORCESEEK)". (Replace "name of index" with the name of the index on T_Subject that has CompanyId as the leading column.
- You now should have two execution plans in the Plan Explorer history pane (right hand side of the screen by default). Save this as a .pesession file and post it here.
- Do NOT use the hints I gave above in your production code. Even if the query does run faster with these hints, they should not be applied unless you know 100% sure that this is the only way to get a good plan. For now we do not even know yet why you do not get the good plan without the hint, so it's far too early to start putting hints in production code. Using them as part of troubleshooting has a lower threshold.