CODEWITHSUNDEEP
sql-servert-sql
thibaudlosc
thibaudlosc

Reputation: 45

Why a clustered index on view is updated and reordered when I update a value of the view which is not contained in the index?

I have the table attribute :

attributeId     INT IDENTITY,
entity          NVARCHAR(150) NOT NULL,
rank            INT NOT NULL,
label           NVARCHAR(500) NOT NULL,
CONSTRAINT pk_attribute PRIMARY KEY CLUSTERED (attributeId)

The table attributeValues: 

attributeId         INT NOT NULL,
entityId            INT NOT NULL,
value               SQL_VARIANT NOT NULL,
CONSTRAINT pk_attributeValues PRIMARY KEY CLUSTERED (attributeId, entityId),

So I made a view : 

CREATE VIEW dbo.vw_attributevalues
WITH SCHEMABINDING, VIEW_METADATA
AS
    SELECT  a.entity, av.entityId, a.attributeId, av.value,
            a.rank, a.label,
    FROM    dbo.attribute a
    JOIN    dbo.attributeValue v
            ON a.attributeId = v.attributeId
GO

CREATE UNIQUE CLUSTERED INDEX idx_vw_attributevalues_for_entity_entityId
    ON dbo.vw_attributevalues (entity, entityId, attributeId) WITH (DATA_COMPRESSION = PAGE);
GO

When I make an update of a rank (which it is not in the index), it updates the index and reorder it : 

UPDATE dbo.attribute
SET rank = 25000
WHERE attributeId = 100011;

execution plan

This part is very costly. I don't understand why sql server reorder and update the index.

Upvotes: 2

Views: 65

Answers (1)

Alan Burstein
Alan Burstein

Reputation: 7928

Since the indexed view is materialized, when any materialized objects referenced in the view are updated, the associated index on that view is also updated. This is a very important consideration when creating indexed views.

Per BOL:

When executing DML1 on a table referenced by a large number of indexed views, or fewer but very complex indexed views, those referenced indexed views will have to be updated as well. As a result, DML query performance can degrade significantly, or in some cases, a query plan cannot even be produced. In such scenarios, test your DML queries before production use, analyze the query plan and tune/simplify the DML statement.

Such as UPDATE, DELETE or INSERT operations.

When you update dbo.attribute, you are updating two indexes: pk_attribute and idx_vw_attributevalues_for_entity_entityId. idx_vw_attributevalues_for_entity_entityId uses a three column cluster key which includes entity, an NVARCHAR(150) column. That will be a costly update depending on the number of records in dbo.attribute and dbo.attributeValue.

Note the following DDL and execution plans:

-- sample data
CREATE TABLE dbo.a(c INT PRIMARY KEY CLUSTERED);
CREATE TABLE dbo.b(c INT PRIMARY KEY CLUSTERED);
INSERT dbo.a(c) VALUES(1),(2),(3);
INSERT dbo.b(c) VALUES(1),(2),(5);
GO

-- Sample indexed view
CREATE VIEW dbo.vw_ab
WITH SCHEMABINDING AS
SELECT ac = a.c, bc = b.c
FROM   dbo.a
JOIN   dbo.b ON a.c < b.c;
GO
CREATE UNIQUE CLUSTERED INDEX uq_dbo_vw_ab ON dbo.vw_ab(ac, bc);
GO

Here we're joining dbo.a and dbo.b, then adding an indexed view. Note the execution plan, when updating dbo.a OR dbo.b I update the clustered index on each table as well as the index on my view. Note the Inserts and associated execution plans:

-- Inserts:
INSERT dbo.a(c) VALUES(20);
INSERT dbo.b(c) VALUES(50);

Execution Plans:

enter image description here

To better understand why your updates/inserts/deletes are costly begin testing by dropping different indexes, adding/modifying data, then comparing the impact with and without the index. I suspect the NVARCHAR(150) column is what is killing you here though.

Upvotes: 1

Related Questions