Optimized Non-clustered Index Maintenance in Per-Index Plans
In my last post, I showed how SQL Server 2005 only updates non-clustered indexes when the data in the index actually changes. For my example, I used a simple update statement that results in a per-row or narrow plan. In this post, I'll show how this optimization works in a per-index or wide update plan.
Let's use the same schema and update statement from last week. To get a per-index plan, we can trick the optimizer into believing that the table is much larger than it really is by running some UPDATE STATISTICS statements with the ROWCOUNT and PAGECOUNT options. (We could, of course, make the table larger by actually inserting more rows, but the UPDATE STATISTICS route is faster.)
CREATE TABLE T (PK INT, A INT, B INT, C INT, D INT, E INT)
CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK)
CREATE INDEX TB ON T(B)
CREATE INDEX TCD ON T(C,D)
CREATE INDEX TE ON T(E)
INSERT T VALUES(0, 10, 20, 30, 40, 50)
UPDATE STATISTICS T WITH ROWCOUNT = 100000, PAGECOUNT = 10000
UPDATE STATISTICS T (TB) WITH ROWCOUNT = 100000, PAGECOUNT = 1000
UPDATE STATISTICS T (TCD) WITH ROWCOUNT = 100000, PAGECOUNT = 1000
UPDATE STATISTICS T (TE) WITH ROWCOUNT = 100000, PAGECOUNT = 1000
SET STATISTICS PROFILE ON
UPDATE T SET B = 29, C = 39, D = 49
By running this statement with SET STATISTICS PROFILE ON, we can see the exact number of rows processed by each index update operator:
Rows Executes
2 1 |--Sequence
2 1 |--Index Update(OBJECT:([T].[TB]), SET:([PK1036] = [T].[PK],[B1037] = [T].[B]) WITH ORDERED PREFETCH)
2 1 | |--Sort(ORDER BY:([T].[B] ASC, [T].[PK] ASC, [Act1035] ASC))
2 1 | |--Filter(WHERE:(NOT [Expr1031]))
2 1 | |--Table Spool
2 1 | |--Split
1 1 | |--Clustered Index Update(OBJECT:([T].[TPK]), SET:([T].[B] = [Expr1025],[T].[C] = [Expr1026],[T].[D] = [Expr1027]))
1 1 | |--Compute Scalar(DEFINE:([Expr1031]=[Expr1031], [Expr1032]=[Expr1032]))
0 0 | |--Compute Scalar(DEFINE:([Expr1031]=CASE WHEN [Expr1003] THEN (1) ELSE (0) END, [Expr1032]=CASE WHEN [Expr1004] AND [Expr1005] THEN (1) ELSE (0) END))
0 0 | |--Compute Scalar(DEFINE:([Expr1025]=(29), [Expr1026]=(39), [Expr1027]=(49)))
0 0 | |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [T].[B] = (29) THEN (1) ELSE (0) END, [Expr1004]=CASE WHEN [T].[C] = (39) THEN (1) ELSE (0) END, [Expr1005]=CASE WHEN [T].[D] = (49) THEN (1) ELSE (0) END))
1 1 | |--Top(ROWCOUNT est 10000)
1 1 | |--Clustered Index Scan(OBJECT:([T].[TPK]), ORDERED FORWARD)
2 1 |--Index Update(OBJECT:([T].[TCD]), SET:([PK1038] = [T].[PK],[C1039] = [T].[C],[D1040] = [T].[D]) WITH ORDERED PREFETCH)
2 1 |--Filter(WHERE:(NOT [Expr1032]))
2 1 |--Sort(ORDER BY:([T].[C] ASC, [T].[D] ASC, [T].[PK] ASC, [Act1035] ASC))
2 1 |--Table Spool
Let's review the various operators in this query plan. First, observe that the portion between the clustered index scan and clustered index update operators looks essentially identical to the per-row plan from my last post. In particular, notice how we have similar compute scalars to compute which indexes have really changed and need to be updated. [Expr1031] indicates whether index TB must be updated and [Expr1032] indicates whether index TCD must be updated. The only real difference between this portion of the plan and a per-row plan is that in this plan the clustered index update only updates the clustered index and does not update either of the non-clustered indexes. The non-clustered indexes are updated by separate index update operators.
Next, we have a split operator. The split operator converts a single row update into a delete followed by an insert. This is why a single input row to the split turns into two output rows. The split and sort operators in this plan are purely for performance and can be ignored for the purpose of this discussion.
The table spool is a common subexpression spool. It eagerly consumes all of its input rows and then plays back these rows multiple times. In this example, the spool plays back its rows twice: once for the update of index TB and once for the update of index TCD. Note that the table spool below the index update of TCD is a secondary spool. It has no input. Instead, it plays back the rows from the primary table spool which is located between the index update of TB and the clustered index update.
The sequence operator glues the two portions of the plan together. As its name suggests, it executes each child subtree in a sequential fashion.
Finally, the filter operators are new in SQL Server 2005. They check the results of the compute scalars to determine whether each non-clustered index needs to be updated for each row. In this example, both indexes need to be updated for the single row we are updating and so we see that the row passes through the filters and the index update operators each process two rows. (Recall that the split converted the single row update into two rows: a delete followed by an insert.)
If we repeat the same update, we can see that this time the filters discard the rows as the data is not changing and the non-clustered indexes do not need to be updated. Notice that both index update operators process no rows:
Rows Executes
0 1 |--Sequence
0 1 |--Index Update(OBJECT:([T].[TB]), SET:([PK1036] = [T].[PK],[B1037] = [T].[B]) WITH ORDERED PREFETCH)
0 1 | |--Sort(ORDER BY:([T].[B] ASC, [T].[PK] ASC, [Act1035] ASC))
0 1 | |--Filter(WHERE:(NOT [Expr1031]))
2 1 | |--Table Spool
2 1 | |--Split
1 1 | |--Clustered Index Update(OBJECT:([T].[TPK]), SET:([T].[B] = [Expr1025],[T].[C] = [Expr1026],[T].[D] = [Expr1027]))
1 1 | |--Compute Scalar(DEFINE:([Expr1031]=[Expr1031], [Expr1032]=[Expr1032]))
0 0 | |--Compute Scalar(DEFINE:([Expr1031]=CASE WHEN [Expr1003] THEN (1) ELSE (0) END, [Expr1032]=CASE WHEN [Expr1004] AND [Expr1005] THEN (1) ELSE (0) END))
0 0 | |--Compute Scalar(DEFINE:([Expr1025]=(29), [Expr1026]=(39), [Expr1027]=(49)))
0 0 | |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [T].[B] = (29) THEN (1) ELSE (0) END, [Expr1004]=CASE WHEN [T].[C] = (39) THEN (1) ELSE (0) END, [Expr1005]=CASE WHEN [T].[D] = (49) THEN (1) ELSE (0) END))
1 1 | |--Top(ROWCOUNT est 10000)
1 1 | |--Clustered Index Scan(OBJECT:([T].[TPK]), ORDERED FORWARD)
0 1 |--Index Update(OBJECT:([T].[TCD]), SET:([PK1038] = [T].[PK],[C1039] = [T].[C],[D1040] = [T].[D]) WITH ORDERED PREFETCH)
0 1 |--Filter(WHERE:(NOT [Expr1032]))
2 1 |--Sort(ORDER BY:([T].[C] ASC, [T].[D] ASC, [T].[PK] ASC, [Act1035] ASC))
2 1 |--Table Spool
We could also use the other techniques which I demonstrated in my previous post (that is, checking the sys.dm_db_index_operational_stats DMV, checking locks held, and checking logical reads) to verify that the non-clustered indexes were not really updated. I'll leave trying these other techniques as an exercise.