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.

Say I have a table like this:

create table SomeTable
(
    id int identity(1, 1) not null primary key clustered,
    SomeString1 varchar(50) not null,
    SomeString2 varchar(50) not null
)
go

create nonclustered index IX_SomeString1
on SomeTable(SomeString1)
go

If I was to do this:

insert into SomeTable(SomeString1, SomeString2)
values('foo', 'bar')
go

And view the actual execution plan, I only see a Clustered Index Insert. Why am I not seeing a Nonclustered Index Insert in the execution plan?

share|improve this question
    
I'm guessing because until you hit a certain cardinality and rowcount threshold, it's not worthwhile to maintain the stats on the nonclustered index. If you have a single row in a table, the optimizer knows it's not going to use that index so it's not maintaining it. –  JNK Mar 9 '12 at 18:45
    
@JNK But if I do a select * from SomeTable where String1 = 'foo', then I see that the query optimizer does in fact choose the index IX_SomeString1 for an index seek. So it must be updating that index, no? –  anon Mar 9 '12 at 18:47
1  
You can look at the stats for it and see. It may be a shortcoming in the execution plan display, too. Have you checked the xml? –  JNK Mar 9 '12 at 18:50
1  
SQL Server can use either a wide or a narrow plan depending on how many rows are affected. This controls whether the index maintenance operations happen separately and are shown up in the plan as separate operations or together and show up as part of the CI operation. –  Martin Smith Mar 9 '12 at 19:46
1  
@MartinSmith Great explanation and I did not know that. Thanks for the link and comment. That should be an answer, I believe. –  anon Mar 9 '12 at 19:57

3 Answers 3

up vote 8 down vote accepted

For a single row insert you get a narrow/per-row plan

INSERT INTO SomeTable(SomeString1, SomeString2)
SELECT TOP 1 type, type
FROM master..spt_values

Narrow plan

If you select the Clustered Index Insert Operator and View the properties window you can see the same information as shown in the XML.

Properties Window

If you try for 1,000 rows

INSERT INTO SomeTable(SomeString1, SomeString2)
SELECT TOP 1000 type, type
FROM master..spt_values

You get a different wide/per-index plan with the operations split out separately

Wide Plan

See Wide vs. Narrow Plans or Craig Freedman's blog for more information about the two

share|improve this answer

Never trust the graphical plan display, is only for newbies. Pros always look at the XML. The NC operation is right there:

<Update DMLRequestSort="false">
  <Object Database="[testdb]" Schema="[dbo]" Table="[SomeTable]" Index="[PK__SomeTabl__3213E83F4AAF1C98]" IndexKind="Clustered" />
  <Object Database="[testdb]" Schema="[dbo]" Table="[SomeTable]" Index="[IX_SomeString1]" IndexKind="NonClustered" />
share|improve this answer
5  
I wouldn't say it's "only for newbies" by a long stretch. –  Dave Markle Mar 9 '12 at 19:10
    
My 'newb' comment is not about the skill of the viewer, is about experience: those who had been burned by the liberal 'omissions' of the graphical plans know better than to trust it. This applies even more so to deadlock graphs. –  Remus Rusanu Mar 9 '12 at 23:00

Because a clustered index defines the physical sort order of the table, when you insert a record you are in fact inserting a record in the clustered index. I don't ever recall seeing non-clustered index inserts/updates in execution plans for inserts/updates, but I have never looked for them either. I tend to focus on poorly performing portion queries that are used to determine which records to insert/update/delete.

share|improve this answer
    
I've seen NC index inserts and updates both. –  JNK Mar 9 '12 at 18:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.