Non-clustered Index Update not displayed in execution plan Possible Duplicate:
  Nonclustered Index Insert  




I'm trying to work out the cost of having a nonclustered index on a particular table, but I don't see any maintenance cost in the execution plan for an insert.

The table looks like this:

    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


As well as a Clustered index on [SessionID] it has the following non-clustered index

/****** 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]


But when I run an execution plan for the following, which updates the column on which there is a non-clustered index:

UPDATE [ASPState].dbo.ASPStateTempSessions
            SET Expires = DATEADD(n, Timeout, GETUTCDATE())
            WHERE SessionId = '32gghltsuoesnvlzbehchp2m2014c0f1'
            RETURN 0 


I don't see any mention of cost in the execution plan:



Is it usual to not see it? How can I calculate the cost of this index in write operations?
Try generating an actual plan in SentryOne Plan Explorer.


  


For a narrow plan the actual costs are not included in the XML, so you won't see those, but you at least have the ability to see that non-clustered indexes are affected.

A lot more information found in the duplicate question:


Nonclustered Index Insert


As well as these blog posts that Martin pointed out previously:


Wide vs. Narrow Plans
Optimized Non-clustered Index Maintenance in Per-Index Plans