Microsoft KB Archive/286234

= FIX: Suboptimal Execution Plan on an Indirect UPDATE to an Indexed View =

Article ID: 286234

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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 &quot;compute scalar&quot; 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

Additional query words: iv performance slow fast speed optimization query plan optimizer

Keywords: kbbug kbfix kbsqlserv2000sp1fix KB286234

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.