Lesson Learned #235: Finding how Indexed Views impact in my database update performance?
Published Aug 19 2022 04:50 AM 1,396 Views

Today, we worked on a very interesting service request where our customer is facing a performance issue running simple query UPDATE Header SET DateOfBirth = '01/01/2021' where ID < 100000. We don't have any conversion implicit and the column ID is the Primary Key of the table. Following, I would like to share with you what was the reason that the query took more resources that we might expect.

 

Well, basically, enabling the execution plan of this query we saw two things

 

  • One is the UPDATE execution. 
  • Two is an additional process using ALLDATA object that needs to run, several operations like sort, hash match etc.. Checking the object I saw that the ALLDATA object is a view with schemabinding that we used to call INDEXED VIEWS. So, it seems, that the update process is taking other resources to update the view ALLDATA.

 

Jose_Manuel_Jurado_2-1660852376499.png

 

In order to reproduce this issue, I created the followed tables and add around several millions of rows. 

 

 

drop table IF EXISTS Header 
drop table IF EXISTS Details
drop view IF EXISTS ALLDATA

CREATE table Header (id int PRIMARY KEY IDENTITY(1,1), name varchar(20), DateOfBirth datetime)
CREATE table Details (id int PRIMARY KEY IDENTITY(1,1), name varchar(20))

INSERT INTO Header  ( name,DateOfBirth) VALUES('DummyM1',DATEADD(day,CEILING(RAND()*(200-100)+100),getdate()))
INSERT INTO Details ( name) VALUES('DummyJ1')

insert into Header  (name,DateOfBirth) select name,DATEADD(day,CEILING(RAND()*(200-100)+100),getdate()) from Header
insert into Details (name) select name from Details

 

 

Without having defined the indexed view any INSERT or UPDATE are not taking too much resources.

 

Jose_Manuel_Jurado_0-1660851534082.png

 

Well, I created the view, joining the two tables and filtering the data.

 

 

create or alter VIEW ALLDATA
WITH SCHEMABINDING
AS
 SELECT  Header.Id, Header.Name as MName, Details.Name as JName  FROM dbo.Header INNER JOIN dbo.Details on Details.Id = Header.Id
 where Details.Id between 1000 and 1000000 
 and year(dateofbirth)<>2022


 CREATE UNIQUE CLUSTERED INDEX IX_ALLDATA ON ALLDATA (ID)


 SELECT * FROM ALLDATA WITH (NOEXPAND)

 

 

And voila!, running the UPDATE command, I found that depending how many rows that you are updating and if you have indexed views you might have an impact in terms of resource consumption that is completely expected. 

 

 

 UPDATE Header SET DateOfBirth = '01/01/2021' where ID < 100000

 

Enjoy!

Version history
Last update:
‎Aug 19 2022 07:51 AM
Updated by: