Non-clustered index maintenance

Non-clustered index are only physically updated when the value in the clustered index is changed. It can be seen in several places:

• SET STATISTICS IO ON
• sys.dm_db_index_operational_stats
• Execution Plan
• Transaction Locks

Let’s look at an example:

USE tempdb;

DROP TABLE T;
CREATE TABLE T (a INT PRIMARY KEY, b INT);
CREATE INDEX x_B ON T(b);
INSERT T VALUES(1, 1)

SELECT index_id, leaf_insert_count, leaf_update_count
FROM sys.dm_db_index_operational_stats (db_id(), object_id('T'), NULL, NULL)

Returns

index_id      leaf_insert_count      leaf_update_count
----------- -------------------- --------------------
1               1                            0
2               1                            0

There were 2 leaf inserts, 1 in clustered index and 1 in nonclustered – (1,1) record.

SET STATISTICS IO ON
SET STATISTICS PROFILE ON

BEGIN TRAN;
UPDATE T SET b = 7

• Table ‘T’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
• Both indexes were updated.

  index_id      leaf_insert_count      leaf_update_count
  ----------- -------------------- --------------------
  1               1                           1
  2               2                           0
  

• In the plan there are several Compute Scalar operators. Expr1003 is evaluated to 1 when the clustered index is changed. This value is used internally for the optimization.

  UPDATE [T] set [b] = @1
    |--Clustered Index Update(OBJECT: ([tempdb].[dbo].[T].[PK__T__2A363CC5]), OBJECT: ([tempdb].[dbo].[T].[x_B]), SET: ([tempdb].[dbo].[T].[b] = [Expr1014]))
         |--Compute Scalar(DEFINE: ([Expr1016]=[Expr1016]))
              |--Compute Scalar(DEFINE: ([Expr1016]=CASE WHEN [Expr1003] THEN (1) ELSE (0) END))
                   |--Compute Scalar(DEFINE: ([Expr1014]=(7)))
                        |--Compute Scalar(DEFINE: ([Expr1003]=CASE WHEN [tempdb].[dbo].[T].[b] = (7) THEN (1) ELSE (0) END))
                             |--Top(ROWCOUNT est 0)
                                  |--Clustered Index Scan(OBJECT: ([tempdb].[dbo].[T].[PK__T__2A363CC5]))
  

• index_id  request_mode  request_type  request_status
  --------- ------------- ------------- --------------
  1         IX            LOCK          GRANT
  2         IX            LOCK          GRANT
  2         X             LOCK          GRANT
  2         X             LOCK          GRANT
  1         X             LOCK          GRANT
  

Now, let’s update the row with the same value

COMMIT;

BEGIN TRAN;
UPDATE T SET b = 7;

Table ‘T’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

• Only the clustered index was updated

  index_id      leaf_insert_count      leaf_update_count
  ----------- -------------------- --------------------
  1               1                           2
  2               2                           0
  

• Only locks on the clustered index were taken

  index_id  request_mode  request_type  request_status
  --------- ------------- ------------- --------------
  1         IX            LOCK          GRANT
  1         X             LOCK          GRANT