SQL Server 何時確定使用新索引？

我一直在對執行公司購買的某些軟體的伺服器進行一些維護，因此我無法真正更改查詢的編寫方式。我使用了一些工具來確定伺服器上一些缺失的索引。但是，使用我的工具，我沒有看到哪些查詢表明了這些。但是，我確實會監控索引的寫入和讀取量。

所以，我的問題是，在添加已確定為缺失的新索引時，SQL Server 何時會注意到新索引並確定它們是否真的有助於查詢？是在創建索引並再次執行查詢之後立即執行，還是更複雜？

SQL Server 何時注意到新索引並確定它們是否真的有助於查詢？

立即地。 由於架構更改，創建索引會導致重新編譯。

這是一個例子。首先在測試數據庫中設置對象：

use TestDatabase;
go

if exists (select 1 from sys.tables where object_id = object_id('dbo.TestTable1'))
   drop table dbo.TestTable1;
create table dbo.TestTable1
(
   Id int identity(1, 1) not null
);
go

insert into dbo.TestTable1
default values;
go 1000

if exists (select 1 from sys.procedures where object_id = object_id('dbo.GetIdFromTestTable1'))
   drop proc dbo.GetIdFromTestTable1;
go
create proc dbo.GetIdFromTestTable1
   @Id int
as
   select Id
   from dbo.TestTable1
   where Id = @Id;
go

如您所料，執行此儲存過程將導致表掃描：

exec dbo.GetIdFromTestTable1 500;
go

現在讓我們創建一個索引，我們知道這個查詢可以從中受益：

create unique index IX_TestTable1_Id
on dbo.TestTable1 (Id);
go

重新執行儲存過程，看看執行後的計劃：

exec dbo.GetIdFromTestTable1 500;
go

我們可以看到我們的表掃描變成了索引查找。這表明產生了一個新的計劃。

為什麼？

我們可以使用 XEvents 會話跟踪此行為：

if exists (select 1 from sys.server_event_sessions where name = N'RecompileTracing')
   drop event session RecompileTracing
   on server;
go
create event session RecompileTracing
on server
add event sqlserver.sql_statement_recompile
(
   set
       collect_statement = 1,
       collect_object_name = 1
)
add target package0.event_file
(
   set
       filename = N'RecompileTracing.xel'
);
go

alter event session RecompileTracing
on server
state = start;
go

/*
alter event session RecompileTracing
on server
state = stop;
go

drop event session RecompileTracing
on server;
go
*/

我們從上面的插圖/repro 中得到的輸出是我們有一個sql_statement_recompileon 語句select Id from dbo.TestTable1 where Id = @Id;（與刪除的空格/製表符/換行符壓縮）並且recompile_cause是Schema changed。

我的經驗是，一個有用的索引幾乎可以立即使用。畢竟它確實改變了優化器可用的資訊。但是，如果優化器拒絕它的值，新索引可能永遠不會被使用。

您應該注意，儲存過程可能會保留現有計劃一段時間。如果是這樣，您可以使用exec sp_recompile procedurename使儲存過程*（是）*重新編譯並評估新索引的值。

如果您想了解更多關於優化器和索引的資訊，您應該閱讀 Benjamin Nevarez 的這篇文章：索引選擇和查詢優化器

https://www.simple-talk.com/sql/performance/index-selection-and-the-query-optimizer/

這應該讓您對事物的工作方式有所了解，至少足以讓您開始測試可能更好的索引。

引用自：https://dba.stackexchange.com/questions/89213