Filtered Index can lead to a reduction in query performance

This month, I faced a compelling performance challenge that I am eager to share. My client indicated that one of their older data cleanup tasks is executing very slowly. Upon reviewing the Query Store and the table indexes, I discovered something quite intriguing.

Below, the image depicts the execution plan obtained from the query store.

At its essence, the query is simple; it consists of a DELETE statement with a WHERE clause that filters the rows to be deleted based on an AccountId column, which is already indexed as non-clustered.

Interestingly, the query execution plan is not just about using the nonclustered index on AccountId to retrieve the qualified rows (visible on the far right of the plan graph); it also involves a Key Lookup on the clustered index of the table.

Also, there is a suggestion for a missing index that suggests adding a duplicate index while including the ToBeDeleted column. This seems strange, as the DELETE query does not refer to any elements from the ToBeDeleted column. 

Let’s examine the Key Lookup operator more closely; it claims to be used for retrieving the ToBeDeleted column, which is not included in the nonclustered index at the rightmost top operator.

Indeed, a filtered index is available on that table, specifically filtered by the ToBeDeleted column, as shown below.

The filtered index features a key column called AccountId, filtered based on the ToBeDeleted column, yet it lacks any INCLUDED columns.

The following blog post clarifies the need for an extra Key Lookup in the query and advises incorporating the filtered column into either the key column or the included column.

sql server - Should the filtering column(s) always be in the keys / includes? - Database Administrators Stack Exchange

In my opinion, it is important to assess the actual usefulness of a filtered index before adding it, ensuring that it can significantly enhance your query performance. The filtering value should only be a constant, as sometimes its effectiveness can be overestimated.