Sql-Server
非聚集索引更新未顯示在執行計劃中
可能重複:
我正在嘗試計算在特定表上使用非聚集索引的成本,但我在插入的執行計劃中看不到任何維護成本。
該表如下所示:
CREATE TABLE [dbo].[ASPStateTempSessions]( [SessionId] [nvarchar](88) NOT NULL, [Created] [datetime] NOT NULL, [Expires] [datetime] NOT NULL, [LockDate] [datetime] NOT NULL, [LockDateLocal] [datetime] NOT NULL, [LockCookie] [int] NOT NULL, [Timeout] [int] NOT NULL, [Locked] [bit] NOT NULL, [SessionItemShort] [varbinary](7000) NULL, [SessionItemLong] [image] NULL, [Flags] [int] NOT NULL, PRIMARY KEY CLUSTERED ( [SessionId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[ASPStateTempSessions] ADD DEFAULT (getutcdate()) FOR [Created] GO ALTER TABLE [dbo].[ASPStateTempSessions] ADD DEFAULT ((0)) FOR [Flags] GO
以及一個聚集索引
$$ SessionID $$它具有以下非聚集索引
/****** Object: Index [Index_Expires] Script Date: 12/14/2012 10:13:58 ******/ CREATE NONCLUSTERED INDEX [Index_Expires] ON [dbo].[ASPStateTempSessions] ( [Expires] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
但是當我為以下內容執行執行計劃時,它會更新存在非聚集索引的列:
UPDATE [ASPState].dbo.ASPStateTempSessions SET Expires = DATEADD(n, Timeout, GETUTCDATE()) WHERE SessionId = '32gghltsuoesnvlzbehchp2m2014c0f1' RETURN 0
我在執行計劃中沒有看到任何提及成本:
平時看不到嗎?如何計算此索引在寫入操作中的成本?
嘗試在Plan Explorer中生成一個實際的計劃。
對於一個狹窄的計劃,實際成本不包含在 XML 中,因此您不會看到這些,但您至少可以看到非聚集索引受到影響。
在重複的問題中可以找到更多資訊:
以及 Martin 之前指出的這些部落格文章: