-> 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.
-> Table Spool operator is used to store a copy of data in a worktable that can be used at a later stage in same or another part of the execution plan without having to produce the same data by calling its child operator all over again.
-> Table spool operator in SQL Server Management Studio graphical execution plan looks as below,
-> Table spool operators helps query performance most of the time by storing data in a worktable without requiring SQL Server to recompute the required data multiple times.
-> We will discuss a scenario where this operator slows down a query execution,
Create a database called JBSWIKI,
create database JBSWIKI
Create required objects,
use JBSWIKI
GO
create table Table1(
COL1 int identity(1,1),
Col2 nvarchar(max))
create table Table2(
COL1 int identity(1,1),
Col2 nvarchar(max))
create table Table3(
COL1 int identity(1,1),
Col2 nvarchar(max))
Populate the objects,
set nocount on
insert into Table1
SELECT '+' + convert(varchar(15),convert(bigint,RAND() * 10000000000))
GO 500000
insert into Table2
SELECT '+' + convert(varchar(15),convert(bigint,RAND() * 10000000000))
GO 50
insert into Table3
SELECT '+' + convert(varchar(15),convert(bigint,RAND() * 10000000000))
GO 120
Execute below query,
set statistics time,io on
select * into #Temp_Table from Table1 where Col2 not in (select SUBSTRING(Col2,CHARINDEX(':',Col2)+1,len(Col2)-CHARINDEX(':',Col2)) from [dbo].Table2)
and Col2 not in (select SUBSTRING(Col2,CHARINDEX(':',Col2)+1,len(Col2)-CHARINDEX(':',Col2)) from [dbo].Table3)
drop table #Temp_Table
set statistics time,io off
-> As per below details this query took 6.8 seconds duration and utilized 35439 MS CPU. Logical reads performed on Table3 and Worktable is around 500009 and 1000792 respectively.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. Table ‘Table3‘. Scan count 17, logical reads 500009, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Table ‘Worktable‘. Scan count 8, logical reads 1000792, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Table ‘Table2’. Scan count 8, logical reads 8, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Table ‘Table1’. Scan count 9, logical reads 2514, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. SQL Server Execution Times: CPU time = 35439 ms, elapsed time = 6851 ms.
-> From the execution plan, Table spool operator stands out when compared to other operators. The logical reads performed for Worktable is comparatively very high.
-> Zooming out specifically to the Spool operator in the execution plan,
-> Object Table3 has 120 rows. Table spool operator estimates and processes 25 Million rows and this explains high logical reads for Worktable.
-> Lets try removing the table spool operator from execution plan using trace flag 8690 as advised in this article.
set statistics time,io on
select * into #Temp_Table from Table1 where Col2 not in (select SUBSTRING(Col2,CHARINDEX(':',Col2)+1,len(Col2)-CHARINDEX(':',Col2)) from [dbo].Table2)
and Col2 not in (select SUBSTRING(Col2,CHARINDEX(':',Col2)+1,len(Col2)-CHARINDEX(':',Col2)) from [dbo].Table3) OPTION (QUERYTRACEON 8690)
drop table #Temp_Table
set statistics time,io off
-> Table spool operator is no longer present. But the resource utilization is really high. This query now takes 2 Minutes 16 Seconds duration and 941780 MS CPU.
-> Removing Table Spool operator from execution plan using trace flag 8690 did not help.
-> I rewrote the query to use a JOIN instead of NOT IN. Below are the results,
set statistics time,io on
select a.* into #Temp_Table from [dbo].Table1 a LEFT OUTER JOIN [dbo].Table2 b ON a.Col2 = SUBSTRING(b.Col2,CHARINDEX(':',b.Col2)+1,len(b.Col2)-CHARINDEX(':',b.Col2))
LEFT OUTER JOIN [dbo].Table3 c ON a.Col2 =SUBSTRING(c.Col2,CHARINDEX(':',c.Col2)+1,len(c.Col2)-CHARINDEX(':',c.Col2))
drop table #Temp_Table
set statistics time,io off
-> Table spool operator is not present. The resource utilization has completely come down. This query now takes just 960 MS duration and 1 logical reads on object Table3. I dont see any logical reads for worktable at all.
SQL Server Execution Times: CPU time = 4500 ms, elapsed time = 960 ms.
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.
-> I recently worked on tuning a query that scans through XML data. -> Below is the query,
set statistics io on
SELECT t.COL1 FROM Table1 t WHERE COL3=N'1' or COL3=N'10' or COL3=N'102'
or COL3=N'103' or COL3=N'104' or COL3=N'105' or COL3=N'106' or COL3=N'11'
or COL3=N'12' or COL3=N'120' or COL3=N'121' or COL3=N'122' or COL3=N'123'
or COL3=N'124' or COL3=N'125' or COL3=N'126' or COL3=N'127' or COL3=N'129'
or COL3=N'13' or COL3=N'130' or COL3=N'131' or COL3=N'132' or COL3=N'133'
or COL3=N'134' or COL3=N'135' or COL3=N'136' or COL3=N'137' or COL3=N'138'
or COL3=N'139' or COL3=N'14' or COL3=N'141' or COL3=N'143' or COL3=N'144'
or COL3=N'145' or COL3=N'146' or COL3=N'147' or COL3=N'148' or COL3=N'149'
or COL3=N'15' or COL3=N'150' or COL3=N'151' or COL3=N'152' or COL3=N'153'
or COL3=N'154' or COL3=N'155' or COL3=N'156' or COL3=N'157' or COL3=N'158'
or COL3=N'159' or COL3=N'16' or COL3=N'160' or COL3=N'161' or COL3=N'162'
or COL3=N'163' or COL3=N'164' or COL3=N'166' or COL3=N'167' or COL3=N'168'
or COL3=N'169' or COL3=N'17' or COL3=N'170' or COL3=N'171' or COL3=N'173'
or COL3=N'174' or COL3=N'175' or COL3=N'176' or COL3=N'177' or COL3=N'178'
or COL3=N'179' or COL3=N'18' or COL3=N'184' or COL3=N'185' or COL3=N'186'
or COL3=N'187' or COL3=N'188' or COL3=N'189' or COL3=N'19' or COL3=N'190'
or COL3=N'191' or COL3=N'192' or COL3=N'193' or COL3=N'194' or COL3=N'195'
or COL3=N'196' or COL3=N'197' or COL3=N'198' or COL3=N'199' or COL3=N'2'
or COL3=N'20' or COL3=N'200' or COL3=N'201' or COL3=N'202' or COL3=N'203'
or COL3=N'204' or COL3=N'205' or COL3=N'206' or COL3=N'207' or COL3=N'208'
or COL3=N'209' or COL3=N'21' or COL3=N'210' or COL3=N'211' or COL3=N'212'
or COL3=N'213' or COL3=N'214' or COL3=N'215' or COL3=N'216' or COL3=N'217'
or COL3=N'218' or COL3=N'22' or COL3=N'227' or COL3=N'228' or COL3=N'229'
or COL3=N'23' or COL3=N'230' or COL3=N'233' or COL3=N'234' or COL3=N'235'
or COL3=N'236' or COL3=N'237' or COL3=N'238' or COL3=N'239' or COL3=N'24'
or COL3=N'240' or COL3=N'241' or COL3=N'242' or COL3=N'25' or COL3=N'26'
or COL3=N'27' or COL3=N'28' or COL3=N'29' or COL3=N'3' or COL3=N'30'
or COL3=N'309' or COL3=N'31' or COL3=N'32' or COL3=N'33' or COL3=N'34'
or COL3=N'35' or COL3=N'36' or COL3=N'37' or COL3=N'38' or COL3=N'39'
or COL3=N'4' or COL3=N'40' or COL3=N'41' or COL3=N'42' or COL3=N'43'
or COL3=N'44' or COL3=N'45' or COL3=N'46' or COL3=N'47' or COL3=N'48'
or COL3=N'49' or COL3=N'5' or COL3=N'51' or COL3=N'52' or COL3=N'53'
or COL3=N'54' or COL3=N'55' or COL3=N'56' or COL3=N'57' or COL3=N'58'
or COL3=N'59' or COL3=N'6' or COL3=N'62' or COL3=N'64' or COL3=N'66'
or COL3=N'67' or COL3=N'68' or COL3=N'69' or COL3=N'7' or COL3=N'70'
or COL3=N'71' or COL3=N'72' or COL3=N'73' or COL3=N'74' or COL3=N'76'
or COL3=N'77' or COL3=N'78' or COL3=N'79' or COL3=N'8' or COL3=N'80'
or COL3=N'81' or COL3=N'82' or COL3=N'83' or COL3=N'84' or COL3=N'85'
or COL3=N'86' or COL3=N'87' or COL3=N'88' or COL3=N'89' or COL3=N'9'
or COL3=N'90' or COL3=N'91' or COL3=N'92' or COL3=N'93' or COL3=N'94'
or COL3=N'95' or COL3=N'96' or COL3=N'97' or COL3=N'98'
ORDER BY COL6 ,COL5
,ISNULL(COL2.value('(/row/c9)[1]', 'VARCHAR(256)'), CHAR(1)) ,COL1
set statistics io off
-> Object Table1 definition,
CREATE TABLE [dbo].[Table1](
[COL1] [varchar](255) NOT NULL,
[COL2] [xml] NULL,
[COL3] AS ([dbo].[udTable1_C4]([COL2])) PERSISTED,
[COL4] AS ([dbo].[udTable1_C2]([COL2])) PERSISTED,
[COL5] AS ([dbo].[udTable1_C8]([COL2])) PERSISTED,
[COL6] AS ([dbo].[udTable1_C1]([COL2])) PERSISTED,
[COL7] AS ([dbo].[udTable1_C3]([COL2])) PERSISTED,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[COL1] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-> Execution plan for the above query,
-> Checking the “Table Valued Function” operator from the execution plan,
-> We are spending most of our time reading the XML data from Table Table1.
-> Lets introduce a new computed column using below code to avoid the costly read on XML column,
CREATE FUNCTION [dbo].[udTable1_C9](@COL2 XML)
RETURNS nvarchar(20)
WITH SCHEMABINDING
BEGIN
RETURN ISNULL(@COL2.value('(/row/c9)[1]', 'VARCHAR(256)'), CHAR(1))
END
GO
ALTER TABLE dbo.Table1 ADD COL8 AS ([dbo].[udTable1_C9]([COL2])) PERSISTED
GO
CREATE INDEX IX_TABLE1_C3_C6_C5_C8_C1 ON Table1 (COL3) INCLUDE (COL6,COL5,COL8,COL1)
GO
-> Executing below query. The only change to this query is that we are using the computed column in order by.
set statistics io on
SELECT t.COL1 FROM Table1 t WHERE COL3=N'1' or COL3=N'10' or COL3=N'102'
or COL3=N'103' or COL3=N'104' or COL3=N'105' or COL3=N'106' or COL3=N'11'
or COL3=N'12' or COL3=N'120' or COL3=N'121' or COL3=N'122' or COL3=N'123'
or COL3=N'124' or COL3=N'125' or COL3=N'126' or COL3=N'127' or COL3=N'129'
or COL3=N'13' or COL3=N'130' or COL3=N'131' or COL3=N'132' or COL3=N'133'
or COL3=N'134' or COL3=N'135' or COL3=N'136' or COL3=N'137' or COL3=N'138'
or COL3=N'139' or COL3=N'14' or COL3=N'141' or COL3=N'143' or COL3=N'144'
or COL3=N'145' or COL3=N'146' or COL3=N'147' or COL3=N'148' or COL3=N'149'
or COL3=N'15' or COL3=N'150' or COL3=N'151' or COL3=N'152' or COL3=N'153'
or COL3=N'154' or COL3=N'155' or COL3=N'156' or COL3=N'157' or COL3=N'158'
or COL3=N'159' or COL3=N'16' or COL3=N'160' or COL3=N'161' or COL3=N'162'
or COL3=N'163' or COL3=N'164' or COL3=N'166' or COL3=N'167' or COL3=N'168'
or COL3=N'169' or COL3=N'17' or COL3=N'170' or COL3=N'171' or COL3=N'173'
or COL3=N'174' or COL3=N'175' or COL3=N'176' or COL3=N'177' or COL3=N'178'
or COL3=N'179' or COL3=N'18' or COL3=N'184' or COL3=N'185' or COL3=N'186'
or COL3=N'187' or COL3=N'188' or COL3=N'189' or COL3=N'19' or COL3=N'190'
or COL3=N'191' or COL3=N'192' or COL3=N'193' or COL3=N'194' or COL3=N'195'
or COL3=N'196' or COL3=N'197' or COL3=N'198' or COL3=N'199' or COL3=N'2'
or COL3=N'20' or COL3=N'200' or COL3=N'201' or COL3=N'202' or COL3=N'203'
or COL3=N'204' or COL3=N'205' or COL3=N'206' or COL3=N'207' or COL3=N'208'
or COL3=N'209' or COL3=N'21' or COL3=N'210' or COL3=N'211' or COL3=N'212'
or COL3=N'213' or COL3=N'214' or COL3=N'215' or COL3=N'216' or COL3=N'217'
or COL3=N'218' or COL3=N'22' or COL3=N'227' or COL3=N'228' or COL3=N'229'
or COL3=N'23' or COL3=N'230' or COL3=N'233' or COL3=N'234' or COL3=N'235'
or COL3=N'236' or COL3=N'237' or COL3=N'238' or COL3=N'239' or COL3=N'24'
or COL3=N'240' or COL3=N'241' or COL3=N'242' or COL3=N'25' or COL3=N'26'
or COL3=N'27' or COL3=N'28' or COL3=N'29' or COL3=N'3' or COL3=N'30'
or COL3=N'309' or COL3=N'31' or COL3=N'32' or COL3=N'33' or COL3=N'34'
or COL3=N'35' or COL3=N'36' or COL3=N'37' or COL3=N'38' or COL3=N'39'
or COL3=N'4' or COL3=N'40' or COL3=N'41' or COL3=N'42' or COL3=N'43'
or COL3=N'44' or COL3=N'45' or COL3=N'46' or COL3=N'47' or COL3=N'48'
or COL3=N'49' or COL3=N'5' or COL3=N'51' or COL3=N'52' or COL3=N'53'
or COL3=N'54' or COL3=N'55' or COL3=N'56' or COL3=N'57' or COL3=N'58'
or COL3=N'59' or COL3=N'6' or COL3=N'62' or COL3=N'64' or COL3=N'66'
or COL3=N'67' or COL3=N'68' or COL3=N'69' or COL3=N'7' or COL3=N'70'
or COL3=N'71' or COL3=N'72' or COL3=N'73' or COL3=N'74' or COL3=N'76'
or COL3=N'77' or COL3=N'78' or COL3=N'79' or COL3=N'8' or COL3=N'80'
or COL3=N'81' or COL3=N'82' or COL3=N'83' or COL3=N'84' or COL3=N'85'
or COL3=N'86' or COL3=N'87' or COL3=N'88' or COL3=N'89' or COL3=N'9'
or COL3=N'90' or COL3=N'91' or COL3=N'92' or COL3=N'93' or COL3=N'94'
or COL3=N'95' or COL3=N'96' or COL3=N'97' or COL3=N'98'
ORDER BY COL6 ,COL5 ,ISNULL(COL8, CHAR(1)) ,COL1
set statistics io off
-> Below execution plan uses the index created. It no longer performs a read on the XML column,
-> Reads are greatly reduced and the query performs comparably good. All good till now as far as scalar promotion is concerned. Lets look at issues that this computed column can cause,
1) All queries using Table1 will be serial after computed column with scalar function is defined on Table1. 2) Parallel rebuild on this table will not be possible if SQL Server Enterprise edition is used. 3) More space used by the computed column and indexes. 4) Many more….
-> It is very important to test with important workloads before using computed columns in production.
-> In my case the script cannot be rewritten and this query is very important for our business. Further Table1 had computed columns with scalar function before I created this, so went with this choice.
-> I would have definitely thought twice if this was the first computed column for Table1.
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
