Non-clustered index maintenance
24 July, 2008-SQL
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;
- 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
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.