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