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.

When I'm inserting into a table that has a view with a clustered index, the estimated execution plan appears to do the table insert and the view's clustered index insert operations in parallel.

  1. Is this true?
  2. Why do the %s not add up to 100%?

Below is somewhat of a contrived example.

/*
DROP INDEX [IX__AView] ON [dbo].[_AView]  
DROP VIEW _AView
DROP TABLE _A
*/

CREATE TABLE _A (Name VARCHAR(10) NOT NULL)
GO

CREATE VIEW _AView WITH SCHEMABINDING AS 
SELECT Name FROM [dbo]._A
GO

CREATE UNIQUE CLUSTERED INDEX [IX__AView] ON [dbo].[_AView] ([Name] ASC) 
GO

/*
--run the estimated execution on this
INSERT INTO _A (Name) values ('cheese')
*/

index-view-updates

share|improve this question
    
+1 @MartinSmith - I didn't even look closely to see its a sequence, not parallelism. –  JNK Feb 10 '12 at 13:46

1 Answer 1

up vote 5 down vote accepted

It appears to do it in sequence in your plan. Not in parallel. The insert happens into the heap. The inserted rows are inserted into the eager spool which is a blocking operator. When all rows are inserted into the base table the rows are inserted from the spool to the view.

As for the percent issue it appears that the spool confuses SSMS. Despite showing up twice in the plan (with the same NodeId) its cost should only be counted once but it seems as though its cost is completely disregarded when calculating the overall estimated subtree cost for the plan and then it displays the operator percentages as a proportion of this incorrect total.

+---------------------+-----------+------------+--------------------------+
|                     |           | % of total | % of total without spool |
+---------------------+-----------+------------+--------------------------+
| Table Insert        | 0.0100022 | 39.84%     | 50.00%                   |
| Spool               | 0.0051012 | 20.32%     | 25.50%                   |
| CX Insert           | 0.010001  | 39.83%     | 49.99%                   |
| Sequence            | 0.000002  | 0.01%      | 0.01%                    |
| Total               | 0.0251064 |            |                          |
| Total without spool | 0.0200052 |            |                          |
+---------------------+-----------+------------+--------------------------+

SQL Sentry Plan Explorer correctly handles the issue.

Plan

share|improve this answer
    
+1 SSMS has some issues with simple math. As Martin points out it often ignores some of the nodes in the plan when calculating relative costs. As a disclaimer, I work for SQL Sentry, who procedures the free Plan Explorer tool Martin linked to. –  Aaron Bertrand Feb 10 '12 at 18:37

Your Answer

 
discard

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

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