January 13, 2011 at 12:01 pm
I'm having trouble understanding an execution plan and was hoping one of you gurus could enlighten me.
Given the following code:
CREATE TABLE [dbo].[ATable](
[storage_id] [varchar](5) NOT NULL,
[transaction_id] [int] NOT NULL,
[rec_num] [int] NOT NULL,
[submission_ID] [varchar](20) NOT NULL,
[SomeData] [varchar] (50) NULL,
CONSTRAINT [PK_ATable] PRIMARY KEY CLUSTERED
(
[storage_id] ASC,
[transaction_id] ASC,
[rec_num] ASC
)
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_ATable] ON [dbo].[ATable]
(
[submission_ID] ASC,
[storage_id] ASC,
[transaction_id] ASC,
[rec_num] ASC
)
GO
INSERT INTO ATable (storage_id, transaction_id, rec_num, submission_ID, SomeData)
VALUES ('A0001', 1, 10, 'Pending', 'aaaaaaaaaaaaaaaaaaaaaaa')
INSERT INTO ATable (storage_id, transaction_id, rec_num, submission_ID, SomeData)
VALUES ('A0001', 1, 12, 'Pending', 'bbbbbbbbbbbbbbbbbbbbbbb')
INSERT INTO ATable (storage_id, transaction_id, rec_num, submission_ID, SomeData)
VALUES ('A0001', 1, 14, 'Pending', 'ccccccccccccccccccccccc')
INSERT INTO ATable (storage_id, transaction_id, rec_num, submission_ID, SomeData)
VALUES ('A0001', 1, 16, 'Pending', 'ddddddddddddddddddddddd')
INSERT INTO ATable (storage_id, transaction_id, rec_num, submission_ID, SomeData)
VALUES ('A0001', 1, 18, 'Pending', 'eeeeeeeeeeeeeeeeeeeeeee')
GO
set showplan_all on
go
DECLARE @storage_id varchar(5)
DECLARE @transaction_id int
SET @storage_id = 'A0001'
SET @transaction_id = 1
UPDATE [dbo].[ATable]
SET SUBMISSION_ID='QUEUED'
WHERE storage_id=@storage_id AND transaction_id=@transaction_id
GO
set showplan_all off
go
drop table [dbo].[Atable]
GO
If one examines the output from the showplan_all, one finds that the costliest item is listed as "Clustered Index Update". This confuses me. The only item being updated is the submission_id field, which is included in the nonclustered index but not in the clustered index. Why would it be doing a clustered index update (even more confusing -- the detail text seems to indicate that it is the nonclustered index which is being updated, which is what I would expect). Is this just a poor choice of words or is the explanation something on the order of: since the clustered index contains the data in the leaf nodes, any update is considered a clustered index update if there is, indeed, a clustered index?
Thanks.
- Les
January 13, 2011 at 12:07 pm
For all practical purposes, the clustered index IS the table. If you update anything in the table, it has to update the clustered index.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2011 at 12:21 pm
GSquared (1/13/2011)
For all practical purposes, the clustered index IS the table. If you update anything in the table, it has to update the clustered index.
I thought, perhaps, that was it. It just seems somewhat unintuitive to me -- for me anyway, logically, the table is distinct from the index even if it is physically implemented as part of the index.
Thanks.
- Les
January 13, 2011 at 12:33 pm
lnoland (1/13/2011)
GSquared (1/13/2011)
For all practical purposes, the clustered index IS the table. If you update anything in the table, it has to update the clustered index.I thought, perhaps, that was it. It just seems somewhat unintuitive to me -- for me anyway, logically, the table is distinct from the index even if it is physically implemented as part of the index.
Thanks.
- Les
The table is just a set of definitions for columns and the data in them. The data in the table is either stored in a heap, or in a clustered index. The clustered index includes all the data in the table and defines the physical storage of the data, except in heaps of course.
Hence, any change in data in a table with a clustered index is done in the clustered index. It could be called a "table update" instead of a "clustered index update", but it would still be the same thing, would still be necessary, and would still show up in your execution plan. Calling it a "clustered index update" makes it clear that it's not a "heap update", and vice versa, where "table update" wouldn't tell you which one it was, and would thus be less useful data for performance tuning.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply