FIX: Suboptimal Execution Plan on an Indirect UPDATE to an Indexed View (286234)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q286234
BUG #: 236137 (SHILOH_BUGS)

SYMPTOMS

An update to a table underlying an indexed view may result in a suboptimal plan when the indexed view contains aggregate functions.

CAUSE

Incorrect row estimates result in an index scan instead of an index seek when calculating the aggregate information.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

Avoid using aggregate functions in an indexed view.

STATUS

Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.

MORE INFORMATION

Execute the following code to see the problem. Note that the "compute scalar" step is performing an index seek. 
use tempdb
go
IF EXISTS(SELECT * FROM information_schema.views where table_name = 'v1')
  drop view v1
IF EXISTS(SELECT * FROM information_schema.tables where table_name = 't1')
  drop table t1
go

create table t1 (a int, b bigint NOT NULL)

set nocount on
declare @i int
set @i=1
while (@i<100000)
begin
  insert into t1 values (@i,0)
  set @i=@i+1
end
go

SET NUMERIC_ROUNDABORT OFF 
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO
create view v1 (a,b,c) with schemabinding as select a, sum(b), count_big(*) from dbo.t1 group by a 
go
create unique clustered index i1 on v1(a)
go

set statistics profile on
insert into t1 values (1,1)
set statistics profile off
go
Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbBug kbfix kbSQLServ2000sp1fix KB286234
©2003 Microsoft Corporation. All rights reserved.