This is one of the blog post in a series of posts about optimizing SQL Server queries. A list of blog posts about query tuning can be found here – https://jbswiki.com/2021/04/15/sql-server-query-optimization-introduction/

-> Customer advised that insert performance on a database server is very bad. When checked further, I was able to understand that inserting 100 rows onto a table JB_Table1 took 252 Seconds,

INSERT INTO [dbo].[JB_Table1]([SNO],[firstname],[lastname],[age],[salary],[Lastsalary]) VALUES (1,'abc','def',34,54367.3,getdate())
go 100

-> I initially thought that there may be several indexes on the table that might be slowing down the inserts. Hence I checked the available indexes on the table and saw just 1 index,

-> You can download the Database backup file from here and try it in your lab.

-> I then executed a single insert statement with an execution plan,

-> From the execution plan it is clear that “Query cost” for Insert statement is 0%, but the update statement is 100%. But where is the update coming from, as we have not executed any updates.

-> That is when I realized that there is an underlying trigger that performs this update.

-> I captured a profiler trace using below events,

-> From the above screenshot we are clearly able to understand that Trigger “trg_jb_Table1_create_date” is utilizing all the resources. It executes for 2.3 seconds, performs close to 1.5 GB Reads and utilizes 2.3 Seconds of CPU. The time taken by the insert statement is high because we are executing the trigger for every insert.

-> Above execution plan shows a missing Index. Lets try creating it and check the performance,

USE [JBDB]
GO
CREATE NONCLUSTERED INDEX [IX_Test1] ON [dbo].[JB_Table1] ([CreateDate])
GO

-> Lets execute the insert statement and then capture a trace,

-> Single Insert statement is now just taking 30 MS duration, 440 KB reads and 0 CPU. This is much better.

-> Lets check the trigger definition,

-> Every time a row is inserted into Table JB_Table1, this trigger is used to update the “CreateDate” column with getdate(). I think this trigger can be replaced with a default constraint as below,

ALTER TABLE dbo.JB_Table1
  ADD CONSTRAINT DF_CreateDate_JB_Table1
  DEFAULT getdate() FOR CreateDate;
GO

-> Once the constraint is created. Backup the trigger and drop it,

USE [JBDB]
GO

DROP TRIGGER [dbo].[trg_jb_table1_create_date]
GO

-> Lets execute the insert statement and then capture a trace,

-> Now the insert statement takes just 1 MS Duration, 56 KB reads and 0 CPU. The best solution in this scenario will be to use default constraint and remove the trigger.

-> Dont forgot to remove Index IX_Test1 which was created to speed up trigger “trg_jb_Table1_create_date”.

USE [JBDB]
GO
DROP INDEX [IX_Test1] ON [dbo].[JB_Table1]
GO

-> Inserting 100 rows now completes in Milliseconds.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.