Execution Plan lists deleted index

Question

Looking at the profiler, I am seeing an execution plan that lists a deleted Columnstore index:

However, I deleted this index 10 days ago, and it does not appear in the indexes for that table, nor in a query I found that lists all the indexes in the DB.

Do I need to do something?

These inserts are taking an extremely long time, on the order of 1/2 second each.

Answer 1

Hi @jed ,

May be the execution plan is from plan cache. Please try to add OPTION (RECOMPILE) in SQL server query? Adding OPTION(RECOMPILE) rebuilds the execution plan every time that your query executes. Such as below;

INSERT INTO PROP_NUMERICLIMIT (ID,PROP_RESULT,COMP_OPERATOR,THRESHOLD_TYPE,NOMINAL_VALUE,UPPER_THRESHOLD,LOWER_THRESHOLD,HIGH_LIMIT,LOW_LIMIT,UNITS,STATUS) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11)  
OPTION (RECOMPILE)  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

object_id	name	                                                 index_id	type	   type_desc	              is_unique  
853578079	PROP_NUMERICLIMIT_CONSTRAINT	1	        1	    CLUSTERED	             1  
853578079	NonClusteredIndex-20220527-150255	9	        2	    NONCLUSTERED	     0  

Sorry for the late response, I didn't see your post.

Yeah, this is weird. The old index does not appear on the system, as far as I can tell.

Answer 3

So what does this query return:

   SELECT *  
   FROM    ATE.sys.indexes  
   WHERE object_id = object_id('[ATE].[dbo].[PROP_NUMERICLIMIT]')  

return?

The only reason that the index appears in the plan is that it exists. If you drop an index, that marks all plans with the table in question for recompile.

Answer 4

Ha! Cool site.

https://www.brentozar.com/pastetheplan/?id=SkzTArSK5

The main issue is that the index

[ATE].[dbo].[PROP_NUMERICLIMIT].[NonClusteredColumnStoreIndex-20210928-121540]

no longer exists