3

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

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

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

  • +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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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