Sql-server – Updating column in underlying table causes massive transaction log growth

clustered-indexmaterialized-viewsql-server-2008-r2update

I have an underlying table ~14k records and an indexed view that contains ~11 million records. When updating a column in the small underlying table this seems to cause a massive transaction log entry on the clustered index when associated with a large amount of records (~2.5 million in this case), the indexed view which in turn creates a massive entry in the transaction log. I'm wondering what I could try to avoid this massive entry into the transaction log, without disabling the index on the view? I know the field that is being updated is not a part of any index, it is just part of this view. My google-fu is failing me at present, so wanted to get this up while I continue searching to see if anything pops up here.

CREATE TABLE Big ( Id INT IDENTITY, SmallId VARCHAR(3) NOT NULL ); -- 11 million rows, 2.5 million where SmallId = 'xxx'
CREATE TABLE Small ( Id VARCHAR(3) NOT NULL, [Description] VARCHAR(128) NOT NULL ); -- 12k rows
CREATE VIEW [View] WITH SCHEMABINDING AS
    SELECT Id, b.SmallId, s.Description AS SmallDescription
    FROM Big b
    INNER JOIN Small s
        ON b.SmallId = s.Id;
CREATE CLUSTERED INDEX [PK_View] ON [View]([Id]);
UPDATE Small SET [Description] = 'x' WHERE Id = 'xxx'; --- massive amount of transaction log entries for PK_View

Best Answer

One idea might be to create a second view that doesn't persist 2.5 million copies of the same value.

CREATE VIEW dbo.innerview
WITH SCHEMABINDING
AS
  SELECT b.Id, SmallId = s.Id
   FROM dbo.Big AS b
   INNER JOIN dbo.Small AS s
   ON b.SmallId = s.Id;
GO
CREATE UNIQUE CLUSTERED INDEX PK_View On innerview([Id]);
GO

Then you can create a view around this, without an index:

ALTER VIEW dbo.View
WITH SCHEMABINDING
AS
  SELECT v.Id, v.SmallId, s.description AS SmallDescription
    FROM dbo.innerview AS v
    INNER JOIN dbo.Small AS s
    ON v.SmallID = s.Id;
GO

Now you can change the small description and it won't affect all those rows, and the queries against dbo.View should exhibit about the same performance as your current queries against the indexed version. Your indexed view shouldn't contain many, many copies of a value that is likely to change.

Of course this view doesn't need to be indexed at all IMHO. A lot of people think indexing a view magically speeds up the query; in a lot of cases it actually just slows down your overall workload. What is the indexed view gaining you that the clustered index on the source table (Big) doesn't already do? Transaction log madness aside, have you compared queries against the indexed view with a simple query like:

SELECT 
  Id, -- please be specific, both tables have a column named Id
  b.SmallId, s.Description AS SmallDescription
FROM dbo.Big AS b
INNER JOIN dbo.Small AS s
    ON b.SmallId = s.Id;

?

Related Question