Does Non-Clustered Index (B-Tree) Gets Update Asynchronously?

The idea of writing this blog post came to my mind, once one of our clients was insist and determine that Non-Clustered index gets update asynchronously and it does not impact INSERT, DELETE and UPDATE transaction performance. Are you shocked? I understand.

Prior demonstration of how SQL Server handles Non-Clustered indexes, I would like to brief what is Non-Clustered Index (B-Tree). One of the best ways to reduce disk I/O and logical reads is to use an index,. An index allows SQL Server to find data in a table without scanning the entire table. An index in a database is analogous to an index in a book. The non-clustered index usually does not contain all the columns of the table; therefore, a page will be able to store more rows of a non-clustered index than rows of the table itself, which contains all the columns. Consequently, SQL Server will be able to red more values for a column from a page representing a non-clustered index on the column than from a page representing the table that contain the column.  Another benefit of the non-clustered index is that, because it is in a separate structure from the data table, it can be put in a different filegroup, with a different I/O path.

Picture from

When designing indexes, you will be operating from two different points of view: the existing system, already in production, where you need to measure the overall impact of an index, and the tactical approach where all you worry about is the immediate benefits of an index, usually when initially designing a system. When you have to deal with the existing system, you should ensure that the performance benefits of an index outweigh the extra cost in processing resources.

[info]A non-clustered index is most useful when all you want to do is retrieve a small number of rows from a large table. As the number of rows to be retrieved increases, the overhead cost of the bookmark lookup rises proportionately. To retrieve a small number of rows from a table, the indexed column should have a very high selectivity. [/info]

Let’s perform some experiment over this matter, the following script creates a dummy database with a heap and non-clustered index. The non-clustered index is been created on different filegroup for demonstration purpose.

 [codesyntax lang=”tsql”]

Use Master;
Create Database NCIX_Write;
Alter Database NCIX_Write Add Filegroup [NCIX];
Alter Database NCIX_Write Add File (Name='NCIX',Filename='D:DataNCIX_Write.ndf',Size = 512MB) To Filegroup [NCIX];
Use NCIX_Write;
Create Table TestTable (ID Bigint identity(1,1), Padding Binary(30)) On [Primary];
Create Nonclustered Index TestTable_NCIX_ID_Padding on dbo.TestTable (ID) Include (Padding) on [NCIX];


I also start the PROCMON application from SYSINTERNALS suite to monitor the SQL Server process’s disk I/O activities for our created database. The following figure shows the PROCMON filter configuration:-

The following script begins an explicit transaction and inserts three records on the TestTable heap object, the other script also keeps the object locks information into #Locks temporary table prior committing the transaction and last it commits the transaction. Of course of our testing purposes, I also keep track of the execution time by printing the value of SysDateTime() built-in function.

[codesyntax lang=”tsql”]

Create Table #Locks(spid bigint,[dbid] int,[objid] bigint,indid bigint,[type] sysname,[resource] sysname,mode sysname,[status] sysname);

Print 'Insert Into';
Print sysdatetime();
Begin Transaction InsertData
Insert Into dbo.TestTable Default Values
Insert Into dbo.TestTable Default Values
Insert Into dbo.TestTable Default Values
Insert Into #Locks Exec Sp_lock;
Commit Transaction InsertData;
Print Sysdatetime();
Waitfor Delay '00:00:03';
Print 'Checkpoint';
Print sysdatetime();
Print sysdatetime();



In the second batch of the above script, SQL Server waits for three seconds and then execute the CHECKPOINT command to perform checkpoint operation manually. Once the CHECKPOINT command is executed, then PROCMON shows there are few write operations requested by SQL Server’s process for D:DataNCIX_Write.mdf and D:DataNCIX_Write.ndf files; the following figure shows:-

As you see at above figure, SQL Server process writes the data from HEAP and Non-Clustered Index into the disk almost at same time; therefore Non-Clustered Index gets update simultaneously. Our above script also captures the locking behavior within explicit transaction which we are require to join with few other DMVs to find valuable information. The following script does the analytics and joins:-

[codesyntax lang=”tsql”]

select db_name(L.dbid) as DB, object_name(L.objid) as [Table], as [Index],L.type,L.mode,L.status from #Locks L 
Inner Join sys.indexes I on I.index_id = L.indid AND I.object_id = L.objid
Where OBJECT_NAME(L.objid) is not null



The below figure shows that SQL Server acquire Exclusive locks on every inserted record as RID type on HEAP object and KEY type on Non-Clustered Index object.


As per our experiment, it is better to limit the count of indexes between 5-7 on each table to make sure there is not much locking and disk I/O overhead; To rectify the disk I/O overhead, you are able to move the non-clustered indexes on the different filegroup on different disk I/O subsystem to boost the physical I/O performance and reduce file I/O contention. 

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles


Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan