3

When a SQL Server index is dropped (say it's a duplicate) what are the steps undertaken by the engine to clear and then rebuild the execution plans that reference it? In particular I'm wondering whether it's an all-at-once process, an as-new-queries-occur process or something in between. Thanks.

1
  • Does this answer your question? – Ronaldo Feb 20 at 1:05
5

Welcome to DBA.StackExchange, and interesting question!...one I've never thought of before. I had to double check myself on what I thought the process was, so I think this StackOverflow Answer should be what you're looking for. Specifically the first statement of the answer:

Dropping/Rebuilding an Index will result in invalidation of any cached execution plans using this table/Indexed view. And sql server will generate a new execution plan on next execution.

Essentially, the plans in the plan cache will automatically be marked as invalid of any existing execution plans that reference the dropped index. Then the next time a query runs that involves the entity of which the index was dropped from, a new execution plan is automatically generated and cached in the plan cache because the old one is invalidated. (Eventually the invalidated plans are flushed from the plan cache to make room for new plans being cached.)

Here's an additional resource that concurs this is how it occurs: Dropping unnecessary indexes - effect on query plans in SQL Server 2005

2
  • 2
    Thanks JD. That did answer my question completely. – MinnRick Feb 20 at 6:03
  • @MinnRick Sure thing, np! – J.D. Feb 20 at 13:58
5

To go a bit more into details, the plan isn't flushed when the index is dropped. See repro below.

The plan stay in cache, but it is now invalid. Next time that plan is up for usage, SQL Server check whether it is valid. If for instance a schema change was made (as in dropping an index), it isn't valid. So a new plan will be generated.

Dropping the plan when an index is dropped would require meta-data which AFAIK isn't available. SQL Server would have to have in-memory meta-data for every plan cache regarding which objects they depends on. Or the plan would have to be searched for every meta-data change (like DROP INDEX) to see what plans that could be invalid. The pragmatic approach is to check at usage time if the plan has to be re-compiled, and this is the SQL Server approach (at least according to my repro below).

I recall, some 3 decades ago, when sp_recompile was introduced in the product. Before that, we wrote our own, and we just bumped the schema_ver column in the sysobjects system table (and handling overflow since it was a tinyint). Then came sp_recompile with the product which did just exactly that.

Move forward 3 decades and the product has evolved, including one total re-write (between 6.5 and 7.0) and another total re-write of the meta-data (2000 to 2005), but the same basic principals is obviously in place.

Note that sp_recompile does seem to actively flush an object from cache (if you read the source code). It doesn't seem to happen for a table, though (see the demo), but probably for a code module.

USE Adventureworks

DROP INDEX IF EXISTS x ON Sales.SalesOrderDetail

CREATE INDEX x ON Sales.SalesOrderDetail(OrderQty)
DBCC FREEPROCCACHE
GO

SELECT SUM(UnitPrice) FROM Sales.SalesOrderDetail WHERE OrderQty = 34
GO

SELECT qs.creation_time, sql.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text (qs.plan_handle) AS sql
WHERE sql.text LIKE '
SELECT SUM(UnitPrice) FROM Sales.SalesOrderDetail%'
GO

WAITFOR DELAY '00:00:00.5'

DROP INDEX IF EXISTS x ON Sales.SalesOrderDetail
GO
WAITFOR DELAY '00:00:00.5'

SELECT qs.creation_time, sql.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text (qs.plan_handle) AS sql
WHERE sql.text LIKE '
SELECT SUM(UnitPrice) FROM Sales.SalesOrderDetail%'
GO
--Note same creation_time for the plan for the two above SELECTS. The same.

--Below will produce a new plan, though
GO

SELECT SUM(UnitPrice) FROM Sales.SalesOrderDetail WHERE OrderQty = 34
GO

SELECT qs.creation_time, sql.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text (qs.plan_handle) AS sql
WHERE sql.text LIKE '
SELECT SUM(UnitPrice) FROM Sales.SalesOrderDetail%'
GO

EXEC sp_recompile 'Sales.SalesOrderDetail'
GO

SELECT qs.creation_time, sql.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text (qs.plan_handle) AS sql
WHERE sql.text LIKE '
SELECT SUM(UnitPrice) FROM Sales.SalesOrderDetail%'
GO
--sp_recompile doesn't avticely remove plan from cache, at least not for a table
4
  • Good point, I wondered if my wording with "flushed" would be confusing. I'll update my answer to be clearer. – J.D. Feb 20 at 13:59
  • Hi J. D.! To be very very picky, I don't think that the plans are marked as invalid when you do the drop index. They are just... plans. There's no "invalid" marker in cache for a plan. Sql server will detect when they are about to be used next time that "Drat. I can't use this plan, there has been a schema change. I have to delete it from cache and produce a new plan." I think that is what you meant, but reading your post suggests that they are somewhat flagged as "invalid". :-) – Tibor Karaszi Feb 20 at 17:29
  • I appreciate the pickiness because I learn from it. Sounds like I have more research to do, but I thought there was a way that plans were actually marked as invalidated. If not, how does SQL Server detect when there's been a schema change relative to when the plan was compiled?...are both events timestamped? – J.D. Feb 20 at 17:40
  • 1
    Methinks that for each referenced object the plan stores the schema_ver that the object had when the plan was compiled. So at re-use time, it compares the current schema_ver with what it was when the plan was compiled. If not the same, then a new plan has to be generated. – Tibor Karaszi Feb 20 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.