I have a query with several joins that is not performing well. After a lot of triage, I decided to take the query apart and look at just the driving table. Below is an example of the query based on just the driving table.
DECLARE @VAR AS INT SET @VAR = 11652862 SELECT Field1, Field2 FROM tablea WHERE Field2 = @VAR
When I run the code as it is the actual execution plan comes back with 15k estimated rows and 1m actual rows. Yes, this is just a simple
SELECT...FROM TABLE, but remember this is just part of a bigger query, so this estimate difference greatly compounds as we add more joins.
I know the problem is the
@VAR because the optimizer does not know the value of the variable when it compiles the query into a plan so its coming back with the avg # of rows for the estimate for the field. If I add
OPTION (RECOMPILE) it will force in the value when it compiles and come back with the correct # of rows.
Here is the kicker: This is not custom code that my company can edit. It is part of a third party app that I cannot edit. Updating stats will not help (I looked at the histogram and it is correctly listing the value for my variable). I already have a covering NC index on those two fields. The only option I can think of is to use a forced execution plan on this query. I have never been able to successfully do that, though. Anyone have any other ideas or a good How-To reference on forced execution plans?
Since it was asked in the comments, the create statement for the index is:
CREATE NONCLUSTERED INDEX idx ON tablea (Field2)