sql - Why adding a new DateTime filter in a Where clausule involves bad performance?

Question

I have the next query, that returns about 100 rows:

SELECT fecha,p_01,p_02,p_03,p_04,p_05,p_06
FROM values_periods_view
WHERE 
    indicator_number='460' 
    AND indicador_date>='20210101' 
    AND indicator_date<='20211231'

The query takes up to 5 minutes. If I modify the filters in the Where clausule like this: (removing the last datetime filter)

SELECT fecha,p_01,p_02,p_03,p_04,p_05,p_06
FROM values_periods_view
WHERE 
    indicator_number='460' 
    AND indicador_date>='20210101' 

the query time it's reduce at 10 seconds.

Somebody can tell me how to improve the response times and why is happening this bad performance when i use DateTimes in Where clausule?

Before answer, i'm going to let you some information about DB state:

• All columns that are used for search have index and the index as been rebuild before to throw the query
• Statistics are updated
• values_periods_view it's a simple view that not have any conversion over the indicador_date column, this column it's just a DateTime Nullable.
• Database have many inserts and modifications daily (i thought that updating the statistics could be resolve the problem, but after update statistics the results are very similar)
• Execution Plan: https://www.brentozar.com/pastetheplan/?id=BJGmt3Ord

Note The DB is: Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

标签： sqlsql-server