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

The estimated reading time for this post is 4 minutes

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 https://technet.microsoft.com/en-us/library/ms177484(v=sql.105).aspx

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.

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.

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.

 

USE Master;
Go
CREATE DATABASE NCIX_Write;
Go
ALTER DATABASE NCIX_Write ADD Filegroup [NCIX];
Go
ALTER DATABASE NCIX_Write ADD FILE (Name='NCIX',Filename='D:\Data\NCIX_Write.ndf',SIZE = 512MB) TO Filegroup [NCIX];
Go
USE NCIX_Write;
Go
CREATE TABLE TestTable (ID BIGINT IDENTITY(1,1), Padding BINARY(30)) ON [PRIMARY];
Go
CREATE NONCLUSTERED INDEX TestTable_NCIX_ID_Padding ON dbo.TestTable (ID) Include (Padding) ON [NCIX];
Go

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.

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();
Go
WAITFOR Delay '00:00:03';
Go
PRINT 'Checkpoint';
PRINT sysdatetime();
CHECKPOINT;
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:\Data\NCIX_Write.mdf and D:\Data\NCIX_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:-

SELECT DB_NAME(L.dbid) AS DB, OBJECT_NAME(L.objid) AS [TABLE],I.name 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.

Conclusion

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. 

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn