Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

enter image description here

Is it usual to not see it? How can I calculate the cost of this index in write operations?

share|improve this question

marked as duplicate by Martin Smith, Paul White, Mike Walsh, Mark Storey-Smith, Derek Downey Dec 17 '12 at 15:37

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer 1

up vote 2 down vote accepted

Try generating an actual plan in SQL Sentry Plan Explorer.

enter image description here

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

Disclaimer: I work for SQL Sentry.

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

share|improve this answer

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