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 parametersthe 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)
The supplied XML is not a valid SQL Server query plan xml.
I will share the plan in other way.