Przygody z oprogramowaniem
  • Start
  • Szkolenia
    • Szkolenia otwarte
    • Katalog szkoleń
  • Usługi
    • Konsulting
    • Mentoring
    • Research & Development
  • Blog
  • Wiedza
    • Strefa wiedzy
    • BFsharp
    • SaaS
  • Klienci
  • Kontakt
0

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;

    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
    

Kategorie

Architecture BFsharp Blog Business Framework C# CqRS DDD Debugging DSL EntityFramework Formula JavaScript Linq NHibernate SaaS Silverlight SQL Visual Studio WPF Wzorce

O mnie


RSS Feed

© macmichal.pl 2011 Wszystkie prawa zastrzeżone