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
Comments
Anonymous
May 28, 2013
искам да премахна от моя профил в Linkedin Една информация.Как да го направя.Помогнете миAnonymous
May 28, 2013
Please help me delete fon my profil sum masageAnonymous
May 28, 2013
please write mi in BulgarienAnonymous
May 28, 2013
I wont to delete in my profile sum ;message