A maintenance plan that contains an Update Statistics task runs slowly after you install SQL Server 2005 Service Pack 2

Article translations Article translations
Article ID: 953117 - View products that this article applies to.
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

Symptoms

In Microsoft SQL Server 2005 or in SQL Server 2005 Service Pack 1 (SP1), you create a maintenance plan that contains an Update Statistics task. After you install SQL Server 2005 Service Pack 2 (SP2), the maintenance plan runs slowly.

Cause

This problem occurs because SQL Server 2005 SP2 applies the Full scan option on all the tables and on all the views in the Update Statistics task.

Workaround

To work around this problem, specify a small sample size instead of using the Full scan option. For example, you can specify a sample size of 50 percent for the tables and for the views.

To specify a small sample size, follow these steps:
  1. In SQL Server Management Studio, open the maintenance plan.
  2. In the maintenance plan, double-click the Update Statistics task.
  3. In the Update Statistics Task dialog box, click Sample by.
  4. In the Sample by box, enter a small value, click Percent in the list next to the Sample by box, and then click OK.

    Note The smallest sample size is 1 percent. When you specify a sample size of 100 percent, the effect is the same as when you use the Full scan option.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

More information

SQL Server 2005 SP2 adds a feature for the Update Statistics task in a maintenance plan. By using this feature, you can use the Full scan option, or you can specify a sample size for the Update Statistics task. By default, SQL Server 2005 SP2 applies the Full scan option on all the tables and on all the views in the existing Update Statistics tasks.

References

For more information about the new features in SQL Server 2005 SP2, visit the following Microsoft Web site:
http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/WhatsNewSQL2005SP2.htm

Properties

Article ID: 953117 - Last Review: November 2, 2013 - Revision: 2.0
Applies to
  • Microsoft SQL Server 2005 Service Pack 2
Keywords: 
kbnosurvey kbarchive kbexpertiseadvanced kbtshoot kbprb KB953117

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