The stuff are pretty complex to share the original code (a lot of routines, a lot of tables), so I will try to summarize.
Environment:
- SQL Server 2016
- standard edition
Objects:
-
wide table with the following columns:
ID BIGINT PK IDENTITY Filter01 Filter02 Filter03 .. and many columns
-
stored procedure returning visible
ID
from the given table depending on filter parameters -
the table has the following indexes:
PK on ID NCI on Filter01 INCLUDE(Filter02, Filter03) NCI on Filter02 INCLUDE(Filter01, Filter03)
Basically, in the routine I am creating three temporary tables – each holding current filtering values and then join them with the main table. In some cases, Filter02
values are not specified (so the join with this table is skipped) – the other tables are always joined. So, I have something like this:
SELECT *
FROM maintable
INNER JOIN #Filter01Values -- always exists
INNER JOIN #Filter02Values -- sometimes skipped
INNER JOIN #Filter03Values -- always exists
So, how the IDs
are distributed – in 99% of the cases it will be best to filter by Filter02Value
and I guess, because of this, the engine is using the NCI on Filter02 INCLUDE(Filter01, Filter03)
index.
The issue is that in the rest 1% the query fails badly:
In green is the Filter02
values table and you can see that filtering on this does not reduce the read rows at all. Then when the filtering by Filter01
is done (in red) about 100 rows are returned.
So, this is happening only when the stored procedure is executed. If I execute its code with these parameters I nice execution plan:
In such case, the engine is filtering by Filter01
first and Filter02
third.
I am building and executing dynamic T-SQL statement and I add OPTION(RECOMPILE) at
at the end, but it does not change anything. If I add WITH RECOMPILE
on the stored procedure level, everything is fine.
Note, the values in the temporary tables for filtering are not populating in the dynamic-tsql statement. The tables are defined, populated and then the statement is built.
So, my questions are:
- is the engine building a new plan for my dynamic statement as I have
OPTION(recompile)
– if yes, why is wrong - is the engine using the values populated in my filter02 temporary table to build the initial plan – maybe yes, that's why it is choosing the wrong plan
- using
recompile
on procedure level feels very hard/lazy fix – do you have any ideas how I can assist the engine further and skip this option – new indexes for examples (I have try a lot)
Best Answer
From your plans I see the problem in this statement:
There is no need in
recompile
here since there is noparameters
at all, you have only constants here. The difference in two plans is in the JOIN ORDER:The correct join order is first
and then everything else, this join applies all the filters and produces only 104 rows that go to final resultset.
Your "slow" plan shows different join order:
that produces 17 mln of rows.
You don't want this join order so your choices are to use
FORCE ORDER hint
or to simply save the result of the correct join to anothertemp
table and then tojoin
it with the rest of your query:I suggest you to use the second method.