The stuff are pretty complex to share the original code (a lot of routines, a lot of tables), so I will try to summarize.
- SQL Server 2016
- standard edition
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
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
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)