FIX: Suboptimal Execution Plan on an Indirect UPDATE to an Indexed View
This article was previously published under Q286234
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 236137 (SHILOH_BUGS)
An update to a table underlying an indexed view may result in a suboptimal plan when the indexed view contains aggregate functions.
Incorrect row estimates result in an index scan instead of an index seek when calculating the aggregate information.
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 theMicrosoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
Avoid using aggregate functions in an indexed view.
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.
Execute the following code to see the problem. Note that the "compute scalar" step is performing an index seek.
use tempdbgoIF EXISTS(SELECT * FROM information_schema.views where table_name = 'v1') drop view v1IF EXISTS(SELECT * FROM information_schema.tables where table_name = 't1') drop table t1gocreate table t1 (a int, b bigint NOT NULL)set nocount ondeclare @i intset @i=1while (@i<100000)begin insert into t1 values (@i,0) set @i=@i+1endgoSET NUMERIC_ROUNDABORT OFF SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ONGOcreate view v1 (a,b,c) with schemabinding as select a, sum(b), count_big(*) from dbo.t1 group by a gocreate unique clustered index i1 on v1(a)goset statistics profile oninsert into t1 values (1,1)set statistics profile offgo
iv performance slow fast speed optimization query plan optimizer
Article ID: 286234 - Last Review: 01/16/2015 21:28:05 - Revision: 3.2
Microsoft SQL Server 2000 Standard Edition
- kbnosurvey kbarchive kbbug kbfix kbsqlserv2000sp1fix KB286234