Lesson Learned #235: Finding how Indexed Views impact in my database update performance?

[Guest Jose_Manuel_Jurado]

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.
  

 

 

 

 

 

 

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.

 

 

 

 

 

 

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!

 

Continue reading...