Wrong Query Execution Plan

Question

Hi,

A query using clustered index key has performed an index scan instead of seek. The query was captured by heavy queries Extended Event and had Physical reads that matched the size of the table. Also the execution time was several minutes.

Statement is similar to the following:
(@P1 bigint)INSERT INTO ProductionTable WITH(ROWLOCK) ("ALL", "THE", "COLUMNS" FROM (DELETE FROM HistoricTable WITH(ROWLOCK) OUTPUT deleted.* WHERE ClusterKey = @P1) AS deleted

Both tables have the same Cluster key and it is used to fetch the row.

We actually retrieve a row from the historic table to the production one.

Running the query will execute in milliseconds and use clustered index seek ( as it should). It once happened (until now) to scan the table and I cannot imagine why.

• Is there something special I miss when using output ? Should we change the statement to 2 simple insert , delete statements?
• There is a possibility for statistics were updated during the execution time. Is there any way this could impact the optimizer although we are using the cluster key in the query?

Thank you in advance,
Marios

Answer 1

This is very hard to answer without seeing the actual execution plan and knowing the exact tables and indexes. And for that matter the exact statement.

But I will ask one question: is ClusterKey a unique key? If it is non-unique, it is possible that the optimizer could get the idea that it's cheaper to scan a non-clustered index, because it thinks the value for which the plan has been sniffed would hit many rows.

I fail to see that the OUTPUT clause and the composable DML could have anything to do with it.

Answer 2

It the key is unique, the only answer I can think of things like someone was fooling around and had dropped the clustered index at the time, or that there were some data type mismatch that precluded a seek of the index.

More realistically, I think we have to close this as unresolved, as there is not enough information to tell what may have happened.