Article ID: 906117
Article Last Modified on 8/23/2005
APPLIES TO
- Microsoft SQL Server 2000 Service Pack 4
- Microsoft SQL Server 2000 Service Pack 3
- Microsoft SQL Server 2000 Service Pack 3a
Bug #: 474643 (SQL Server 8.0)
SYMPTOMS
In Microsoft SQL Server 2000, some queries against indexed views may run slower when one of the following conditions is true:
- You install a SQL Server 2000 post-Service Pack 3 hotfix or a SQL Server 2000 post-Service Pack 3a hotfix, and the hotfix is build 2000.80.811 or a later build.
- You install SQL Server 2000 Service Pack 4 (SP4).
CAUSE
This issue occurs because of a software update that was included in build 2000.80.811 and in later builds. When SQL Server 2000 used a certain type of indexed view query plan, access violations occurred. This plan type was disabled in build 2000.80.811 to fix this issue.
For more information about this fix, click the following article number to view the article in the Microsoft Knowledge Base:
819248 FIX: An access violation exception may occur when you insert a row in a table that is referenced by indexed views in SQL Server 2000
This fix may cause some queries to run more slowly in build 2000.80.811 and in later builds. Before build 2000.80.811, queries used an index seek or scan on a non-clustered index on the view. Then, the queries performed a bookmark lookup against the clustered index on the view. After you install build 2000.80.811 or a later build, the same query may use a less selective index or a clustered index scan.
Microsoft researched other possible fixes. However, we determined that these fixes required changes that were too large and may potentially destabilize SQL Server 2000. Therefore, a hotfix was not created.
WORKAROUND
To work around these performance problems, you may be able to add more columns to a non-clustered index on the indexed view. For example, a non-clustered index may exist on the [col1] column in the following query:
SELECT [col1] FROM [vwIndexedView] WHERE [col1] = ‘x’ AND [col2] = ‘y’
After you install build 2000.80.811 or a later build, SQL Server 2000 may select a plan that relies on a clustered index scan on the [vwIndexedView] view. You may be able to restore a plan that uses a non-clustered index seek by adding the [col2] column to the non-clustered index.
STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.
Keywords: kbtshoot kbbug kbpending KB906117
