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!
Microsoft
