Execution Plan lists deleted index

jed 41 Reputation points
2022-06-13T21:12:37.257+00:00

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

210996-screenshot-2022-06-13-140927.png

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,441 questions
{count} votes

4 answers

Sort by: Most helpful
  1. CathyJi-MSFT 22,346 Reputation points Microsoft Vendor
    2022-06-14T06:53:42.127+00:00

    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".

    1 person found this answer helpful.

  2. jed 41 Reputation points
    2022-06-13T23:56:57.823+00:00

    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

    0 comments No comments

  3. Erland Sommarskog 116.5K Reputation points MVP
    2022-06-14T21:31:44.5+00:00

    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.

    0 comments No comments

  4. jed 41 Reputation points
    2022-06-22T01:02:00.873+00:00
    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.