BUG: Queries against indexed views may run slower when you install a SQL Server 2000 post-SP3 hotfix, a SQL Server 2000 post-SP3a hotfix, or SQL Server 2000 SP4

Article translations Article translations
Article ID: 906117 - View products that this article applies to.
Bug #: 474643 (SQL Server 8.0)
Expand all | Collapse all

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.

Properties

Article ID: 906117 - Last Review: August 23, 2005 - Revision: 1.1
APPLIES TO
  • Microsoft SQL Server 2000 Service Pack 4
  • Microsoft SQL Server 2000 Service Pack 3
  • Microsoft SQL Server 2000 Service Pack 3a
Keywords: 
kbpending kbbug kbtshoot KB906117

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com