Recompilations after index creation

Yesterday I got an interesting question: Does adding an index to a table force the execution plans to be recreated?
Books online says this:

 

Please note the first, second and fourth bulleted points.

It says either a structure should be changed or index used by the query should be changed. In other words not any change in the index will not force recompile.


Books online also lists at least 11 scenarios where a recompile could occur. They are listed on the same article. As you may know, we can capture recompile event using profiler. SP:Recompile and SQL:StmtRecompile events could be used to capture it. These events have EventSubClass which represents the reason for recompile. These are the reasons.

1. Schema changed.
2. Statistics changed.
3. Deferred compile.
4. SET option changed.
5. Temporary table changed.
6. Remote rowset changed.
7. FOR BROWSE permission changed.
8. Query notification environment changed.
9. Partitioned view changed.
10. Cursor options changed.
11. OPTION (RECOMPILE) requested.

As you may have noticed any structural changes to a table force all execution plans for depending tables to be marked as invalid. At the next execution of the code, the execution plan will be recreated. It may even come up with the same execution plan.
Interestingly, event sub class does not show anything connected to indexes. Now the question is does SQL Server consider a change in index as structural change?
So I decided to perform a test on this. This is my plan.

• Get a query which uses a clustered index scan due to unavailability of index
• Create the index and see whether it forces the execution plan to recompile
• Create a completely unrelated index on the same table and see whether it forces recompile of the query.
• I need to perform this for ad-hoc queries and stored procedures

While the above tests are performed I monitored SP:Recompile and SQL:StmtRecompile events

USE AdventureWorks;
EXEC sp_helpindex 'sales.SalesOrderHeader'
-- TerritoryID, ContactID columns are not indexed
GO

Now I created the stored procedure named Get_SalesHeader_ByTerritoryID. I will use the same query with the same parameter for all the tests.

Then I opened SQL Profiler and create a new trace event. (For this test, using profiler window to capture the data may be okay, but if you are using a busy system it is recommended to use server side trace.)
I selected the following events and columns:

• Showplan XML for Query Compile (TextData, SPID)
• SP: Completed (TextData,SPID)
• SP:Recompile (TextData, SPID , EventSubClass)
• SQL: BatchCompleted (TextData, SPID)
• SQL:StmtRecmpile (TextData, SPID,EventSubClass)

Before starting the test, let us understand what these events are
SP: Recompile is fired when either an entire procedure or an individual statement is recompiled. SQL:StmtRecompile is fired when an ad-hoc SQL statement is fired. (EXEC too could be considered as an ad-hoc statement) Showplan XML for Query Compile is fired only when query is compiled and it shows the execution plan)
These events will help me to identify the not only the recompile events, but also the new execution plans.
I wrote the following script to perform the tests.

use AdventureWorks;
exec sp_helpindex 'sales.SalesOrderHeader'
-- TerritoryID, ContactID columns are not indexed
go
USE [AdventureWorks]
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Get_SalesHeader_ByTerritoryID]
(
@TerritoryID int
)
AS
SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE TerritoryID = @TerritoryID

GO

DBCC FREEPROCCACHE
GO
SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TerritoryID =5
go
CREATE INDEX IX_SalesOrderHeader_TerritoryID on Sales.SalesOrderHeader(TerritoryID)
go
SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TerritoryID =5
go
DROP INDEX Sales.SalesOrderHeader.IX_SalesOrderHeader_TerritoryID
GO

DBCC FREEPROCCACHE
GO
SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TerritoryID =5
go
CREATE INDEX IX_SalesOrderHeader_ContactID on Sales.SalesOrderHeader(ContactID)
go
SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TerritoryID =5
go
DROP INDEX Sales.SalesOrderHeader.IX_SalesOrderHeader_ContactID
GO



DBCC FREEPROCCACHE
GO
EXEC Get_SalesHeader_ByTerritoryID @TerritoryID = 5
go
CREATE INDEX IX_SalesOrderHeader_TerritoryID on Sales.SalesOrderHeader(TerritoryID)
go
EXEC Get_SalesHeader_ByTerritoryID @TerritoryID = 5
go
DROP INDEX Sales.SalesOrderHeader.IX_SalesOrderHeader_TerritoryID
GO

DBCC FREEPROCCACHE
GO
EXEC Get_SalesHeader_ByTerritoryID @TerritoryID = 5
go
CREATE INDEX IX_SalesOrderHeader_ContactID on Sales.SalesOrderHeader(ContactID)
go
EXEC Get_SalesHeader_ByTerritoryID @TerritoryID = 5
GO
DROP INDEX Sales.SalesOrderHeader.IX_SalesOrderHeader_ContactID
GO

Here are the results:

The above results are for ad-hoc queries.  The results below show the behavior for stored procedures.

 

It shows that irrespective of whether it is a stored procedure or ad-hoc query, the results are identical. Not only that, Irrespective of whether the index created is useful for the query or not, the recompile event occurred and a new plan is created. If the new index is found to be useful for the query a new plan is created. Otherwise the same plan is created again.
Another point to note is, the recompile event occurs because SQL Server considers an index creation as part of schema change. (Refer the event sub class value for the changes)
This may be another place for Microsoft to change in books online.