Indexes Overhead on DML (Insert-Update-Delete) Operations

Indices dramatically affect query performance. A report which can take several hours can be completed in just a few seconds with a proper index. On the other hand every index might affect DML (Insert-Update-Delete) operations, because every DML operation touches clustered index and every related nonclustered index.

To be able to see this effect I have created a demo with the results shown below. I used a copy of AdventureWorks2012.Sales.SalesOrderDetail table and insert, update, delete 100K records for each case. (I used a PC which has 8 Cores and 16 GB RAM for this demo.)

Results are clear. If you have more indices, DML operations take longer to be completed. So indices should be analyzed periodically and NonUsed or Rarely Used indices should be dropped. For this purpose you can use sys.dm_db_index_usage_stats DMV.

If you want to try the same demo you can use the script below.

--DO NOT RUN this script on Production environment

/*

Demo : Indexes Overhead on DML (Insert-Update-Delete) Operations

04/21/2013 - Turgay Sahtiyan - @turgaysahtiyan

*/

use AdventureWorks2012

GO

--to prevent autogrowth events make sure there is enough free space in

-- log and data files of AdventureWorks2012

--Create a work table from Sales.SalesOrderDetail

if exists(select * from sys.tables where name = 'tbl_DMLOverHeadDemo' and type='U')

       drop table tbl_DMLOverHeadDemo

select * into tbl_DMLOverHeadDemo from Sales.SalesOrderDetail

GO

SET IDENTITY_INSERT tbl_DMLOverHeadDemo ON

GO

--Create a temp table to store demo results

if exists(select * from tempdb.sys.tables where name = 'DemoResults' and type='U')

       drop table tempdb.dbo.DemoResults

Create table tempdb.dbo.DemoResults (TestCase int, CaseDescription varchar(50), InsertTime_ms int, Updatetime_ms int, DeleteTime_ms int)

GO

 

--Test 1

--Heap + 0 NonClustered Index

DECLARE @BeginTime_Ins datetime2,

             @EndTime_Ins datetime2,

             @BeginTime_Upd datetime2,

             @EndTime_Upd datetime2,

             @BeginTime_Del datetime2,

             @EndTime_Del datetime2

--Insert

SET NOCOUNT ON

DECLARE @i int = 0,

             @End int = 0,

             @Begin int = 0,

             @rowguid uniqueidentifier

SELECT @Begin = MAX(SalesOrderDetailID) + 1

FROM tbl_DMLOverHeadDemo

SET    @i = @Begin

SET    @End = @Begin + 100001

SET @BeginTime_Ins = getdate()

WHILE @i <= @End

BEGIN 

       Set @rowguid = newID()

       INSERT INTO tbl_DMLOverHeadDemo(SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID

             ,SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate)

       VALUES(CONVERT(int,RAND()*1000000+1), @i, 'TRNo_' + CONVERT(varchar,@i), @i%10, CONVERT(int,RAND()*683+316)

             ,1, 0, 0, 0, @rowguid, getdate())

       SET @i = @i+1

END;

SET @EndTime_Ins = getdate()

--Save tracked times into tempdb.dbo.DemoResults

insert tempdb.dbo.DemoResults (TestCase, CaseDescription, InsertTime_ms, Updatetime_ms, DeleteTime_ms)

       select 1, 'Heap + 0 NonClustered Index'

             ,DATEDIFF(MILLISECOND, @BeginTime_Ins, @EndTime_Ins) as InsertTime_ms

             ,0

             ,0

--Delete inserted records

SET @i = @Begin

DELETE tbl_DMLOverHeadDemo

WHERE SalesOrderDetailID >= @i

GO

--Test 2 - Clustered Index + 0 NonClustered Index

--Clustered Index : SalesOrderDetailId

Create Clustered Index CI1 on tbl_DMLOverHeadDemo (SalesOrderDetailID)

DECLARE @BeginTime_Ins datetime2,

             @EndTime_Ins datetime2,

             @BeginTime_Upd datetime2,

             @EndTime_Upd datetime2,

             @BeginTime_Del datetime2,

             @EndTime_Del datetime2

--Insert

SET NOCOUNT ON

DECLARE @i int = 0,

             @End int = 0,

             @Begin int = 0,

             @rowguid uniqueidentifier

SELECT @Begin = MAX(SalesOrderDetailID) + 1

FROM tbl_DMLOverHeadDemo

SET    @i = @Begin

SET    @End = @Begin + 100001

SET @BeginTime_Ins = getdate()

WHILE @i <= @End

BEGIN 

       Set @rowguid = newID()

       INSERT INTO tbl_DMLOverHeadDemo(SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID

             ,SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate)

       VALUES(CONVERT(int,RAND()*1000000+1), @i, 'TRNo_' + CONVERT(varchar,@i), @i%10, CONVERT(int,RAND()*683+316)

             ,1, 0, 0, 0, @rowguid, getdate())

       SET @i = @i+1

END;

SET @EndTime_Ins = getdate()

--Update

SET @BeginTime_Upd = getdate()

SET NOCOUNT ON

SET @i = @Begin

WHILE @i <= @End

BEGIN

       UPDATE tbl_DMLOverHeadDemo

       SET ProductId = CONVERT(int,RAND()*683+316) ,

             CarrierTrackingNumber = 'TRNo_' + CONVERT(varchar,@i+1)

       WHERE SalesOrderDetailID = @i

      

       SET @i = @i + 1

END;

SET @EndTime_Upd = getdate()

--Delete

SET @BeginTime_Del = getdate()

SET NOCOUNT ON

SET @i = @Begin

WHILE @i <= @End

BEGIN

       DELETE tbl_DMLOverHeadDemo

       WHERE SalesOrderDetailID = @i

      

       SET @i = @i + 1

END;

SET @EndTime_Del = getdate()

--Save tracked times into tempdb.dbo.DemoResults

insert tempdb.dbo.DemoResults (TestCase, CaseDescription, InsertTime_ms, Updatetime_ms, DeleteTime_ms)

       select 2, 'Clustered Index + 0 NonClustered Index'

             ,DATEDIFF(MILLISECOND, @BeginTime_Ins, @EndTime_Ins) as InsertTime_ms

             ,DATEDIFF(MILLISECOND, @BeginTime_Upd, @EndTime_Upd) as UpdateTime_ms

             ,DATEDIFF(MILLISECOND, @BeginTime_Del, @EndTime_Del) as DeleteTime_ms

GO

--Test 3 - Clustered Index + 1 NonClustered Index

--Clustered Index : SalesOrderDetailId

--NonClustered Index 1 : CarrierTrackingNumber

Create NonClustered Index IX1 on tbl_DMLOverHeadDemo (CarrierTrackingNumber)

DECLARE @BeginTime_Ins datetime2,

             @EndTime_Ins datetime2,

             @BeginTime_Upd datetime2,

             @EndTime_Upd datetime2,

             @BeginTime_Del datetime2,

             @EndTime_Del datetime2

--Insert

SET NOCOUNT ON

DECLARE @i int = 0,

             @End int = 0,

             @Begin int = 0,

             @rowguid uniqueidentifier

SELECT @Begin = MAX(SalesOrderDetailID) + 1

FROM tbl_DMLOverHeadDemo

SET    @i = @Begin

SET    @End = @Begin + 100001

SET @BeginTime_Ins = getdate()

WHILE @i <= @End

BEGIN 

       Set @rowguid = newID()

       INSERT INTO tbl_DMLOverHeadDemo(SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID

             ,SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate)

       VALUES(CONVERT(int,RAND()*1000000+1), @i, 'TRNo_' + CONVERT(varchar,@i), @i%10, CONVERT(int,RAND()*683+316)

             ,1, 0, 0, 0, @rowguid, getdate())

       SET @i = @i+1

END;

SET @EndTime_Ins = getdate()

--Update

SET @BeginTime_Upd = getdate()

SET NOCOUNT ON

SET @i = @Begin

WHILE @i <= @End

BEGIN

       UPDATE tbl_DMLOverHeadDemo

       SET ProductId = CONVERT(int,RAND()*683+316) ,

             CarrierTrackingNumber = 'TRNo_' + CONVERT(varchar,@i+1)

       WHERE SalesOrderDetailID = @i

      

       SET @i = @i + 1

END;

SET @EndTime_Upd = getdate()

--Delete

SET @BeginTime_Del = getdate()

SET NOCOUNT ON

SET @i = @Begin

WHILE @i <= @End

BEGIN

       DELETE tbl_DMLOverHeadDemo

       WHERE SalesOrderDetailID = @i

      

       SET @i = @i + 1

END;

SET @EndTime_Del = getdate()

--Save tracked times into tempdb.dbo.DemoResults

insert tempdb.dbo.DemoResults (TestCase, CaseDescription, InsertTime_ms, Updatetime_ms, DeleteTime_ms)

       select 3, 'Clustered Index + 1 NonClustered Index'

             ,DATEDIFF(MILLISECOND, @BeginTime_Ins, @EndTime_Ins) as InsertTime_ms

             ,DATEDIFF(MILLISECOND, @BeginTime_Upd, @EndTime_Upd) as UpdateTime_ms

             ,DATEDIFF(MILLISECOND, @BeginTime_Del, @EndTime_Del) as DeleteTime_ms

GO

--Test 4 - Clustered Index + 2 NonClustered Index

--Clustered Index : SalesOrderDetailId

--NonClustered Index 1 : CarrierTrackingNumber

--NonClustered Index 2 : ProductID

Create NonClustered Index IX2 on tbl_DMLOverHeadDemo (ProductID)

DECLARE @BeginTime_Ins datetime2,

             @EndTime_Ins datetime2,

             @BeginTime_Upd datetime2,

             @EndTime_Upd datetime2,

             @BeginTime_Del datetime2,

             @EndTime_Del datetime2

--Insert

SET NOCOUNT ON

DECLARE @i int = 0,

             @End int = 0,

             @Begin int = 0,

             @rowguid uniqueidentifier

SELECT @Begin = MAX(SalesOrderDetailID) + 1

FROM tbl_DMLOverHeadDemo

SET    @i = @Begin

SET    @End = @Begin + 100001

SET @BeginTime_Ins = getdate()

WHILE @i <= @End

BEGIN 

       Set @rowguid = newID()

       INSERT INTO tbl_DMLOverHeadDemo(SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID

             ,SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate)

       VALUES(CONVERT(int,RAND()*1000000+1), @i, 'TRNo_' + CONVERT(varchar,@i), @i%10, CONVERT(int,RAND()*683+316)

             ,1, 0, 0, 0, @rowguid, getdate())

       SET @i = @i+1

END;

SET @EndTime_Ins = getdate()

--Update

SET @BeginTime_Upd = getdate()

SET NOCOUNT ON

SET @i = @Begin

WHILE @i <= @End

BEGIN

       UPDATE tbl_DMLOverHeadDemo

       SET ProductId = CONVERT(int,RAND()*683+316) ,

             CarrierTrackingNumber = 'TRNo_' + CONVERT(varchar,@i+1)

       WHERE SalesOrderDetailID = @i

      

       SET @i = @i + 1

END;

SET @EndTime_Upd = getdate()

--Delete

SET @BeginTime_Del = getdate()

SET NOCOUNT ON

SET @i = @Begin

WHILE @i <= @End

BEGIN

       DELETE tbl_DMLOverHeadDemo

       WHERE SalesOrderDetailID = @i

      

       SET @i = @i + 1

END;

SET @EndTime_Del = getdate()

--Save tracked times into tempdb.dbo.DemoResults

insert tempdb.dbo.DemoResults (TestCase, CaseDescription, InsertTime_ms, Updatetime_ms, DeleteTime_ms)

       select 4, 'Clustered Index + 2 NonClustered indices'

             ,DATEDIFF(MILLISECOND, @BeginTime_Ins, @EndTime_Ins) as InsertTime_ms

             ,DATEDIFF(MILLISECOND, @BeginTime_Upd, @EndTime_Upd) as UpdateTime_ms

             ,DATEDIFF(MILLISECOND, @BeginTime_Del, @EndTime_Del) as DeleteTime_ms

GO

--Test 5 - Clustered Index + 3 NonClustered Index

--Clustered Index : SalesOrderDetailId

--NonClustered Index 1 : CarrierTrackingNumber

--NonClustered Index 2 : ProductID

--NonClustered Index 3 : CarrierTrackingNumber, ProductID -- this index is redundanty with NonClustered Index 1

Create NonClustered Index IX3 on tbl_DMLOverHeadDemo (CarrierTrackingNumber, ProductID) -- this index is duplicated with NonClustered Index 1CarrierTrackingNumber

DECLARE @BeginTime_Ins datetime2,

             @EndTime_Ins datetime2,

             @BeginTime_Upd datetime2,

             @EndTime_Upd datetime2,

             @BeginTime_Del datetime2,

             @EndTime_Del datetime2

--Insert

SET NOCOUNT ON

DECLARE @i int = 0,

             @End int = 0,

             @Begin int = 0,

             @rowguid uniqueidentifier

SELECT @Begin = MAX(SalesOrderDetailID) + 1

FROM tbl_DMLOverHeadDemo

SET    @i = @Begin

SET    @End = @Begin + 100001

SET @BeginTime_Ins = getdate()

WHILE @i <= @End

BEGIN 

       Set @rowguid = newID()

       INSERT INTO tbl_DMLOverHeadDemo(SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID

             ,SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate)

       VALUES(CONVERT(int,RAND()*1000000+1), @i, 'TRNo_' + CONVERT(varchar,@i), @i%10, CONVERT(int,RAND()*683+316)

             ,1, 0, 0, 0, @rowguid, getdate())

       SET @i = @i+1

END;

SET @EndTime_Ins = getdate()

--Update

SET @BeginTime_Upd = getdate()

SET NOCOUNT ON

SET @i = @Begin

WHILE @i <= @End

BEGIN

       UPDATE tbl_DMLOverHeadDemo

       SET ProductId = CONVERT(int,RAND()*683+316) ,

             CarrierTrackingNumber = 'TRNo_' + CONVERT(varchar,@i+1)

       WHERE SalesOrderDetailID = @i

      

       SET @i = @i + 1

END;

SET @EndTime_Upd = getdate()

--Delete

SET @BeginTime_Del = getdate()

SET NOCOUNT ON

SET @i = @Begin

WHILE @i <= @End

BEGIN

       DELETE tbl_DMLOverHeadDemo

       WHERE SalesOrderDetailID = @i

      

       SET @i = @i + 1

END;

SET @EndTime_Del = getdate()

--Save tracked times into tempdb.dbo.DemoResults

insert tempdb.dbo.DemoResults (TestCase, CaseDescription, InsertTime_ms, Updatetime_ms, DeleteTime_ms)

       select 5, 'Clustered Index + 3 NonClustered indices'

             ,DATEDIFF(MILLISECOND, @BeginTime_Ins, @EndTime_Ins) as InsertTime_ms

             ,DATEDIFF(MILLISECOND, @BeginTime_Upd, @EndTime_Upd) as UpdateTime_ms

             ,DATEDIFF(MILLISECOND, @BeginTime_Del, @EndTime_Del) as DeleteTime_ms

GO

--Results

select * from tempdb.dbo.DemoResults

--Drop worktables

drop table tbl_DMLOverHeadDemo

drop table tempdb.dbo.DemoResults