solving sql server problems for millions of dbas and developers since 2006



SQL Server DBA Tips SQL Server Developer Tips SQL Server Business Intelligence Tips SQL Server Career Tips SQL Server Tip Categories SQL Server Tutorials SQL Server Webcasts SQL Server Whitepapers SQL Server Tools SQL Server Questions and Answers MSSQLTips Authors About MSSQLTips SQL Server User Groups MSSLQTips Giveaways MSSQLTips Advertising Options

MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Twitter Page MSSQLTips Google+ Page





NORECOMPUTE option of UPDATE STATISTICS in SQL Server

By: | Read Comments (2) | Print

Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009.



Related Tips: More

Problem
Updating statistics is valuable for ensuring the SQL Server optimizer has the current statistical information to most efficiently process the query results.  As a best practice, the UPDATE STATISTICS command should be issued on a regular basis to provide SQL Server with the most recent data.  Automatically updating statistics is possible with the 'Auto Update Statistics' database configuration, but this could cause performance issues if a large table's statistics are updated during the middle of the day.  To prevent this problem, the 'Auto Update Statistics' option can be disabled on a per database basis. This paradigm creates the need to rebuild all of the statistics in a manual manner.  Does a happy medium exist to let SQL Server automatically manage the statistics on small tables and let me manage the statistics on the larger tables?

Solution
The simple answer is 'yes'.  SQL Server statistics can be configured on a per database basis with the ability to not automatically re-compute indexes on larger tables with the NORECOMPUTE option of the UPDATE STATISTICS command.  So let's break down the configurations and commands to make this a reality. 

Where can I find the auto update statistics option? 

SQL Server 2000 - Database Properties
SQL Server 2005 - Database Properties

How can I incorporate the NORECOMPUTE option of the UPDATE STATISTICS command in my code?

SQL Server 2000 SQL Server 2005
UPDATE STATISTICS dbo.Authors WITH NORECOMPUTE
GO
UPDATE STATISTICS dbo.Customer WITH NORECOMPUTE;
GO

Are any commands available to update the statistics in a simple manner?

SQL Server 2000 SQL Server 2005
master.dbo.sp_updatestats master.sys.sp_updatestats

How can I re-enable Auto Update Statistics once I have used the NORECOMPUTE option?

SQL Server 2000 SQL Server 2005
master.dbo.sp_autostats master.sys.sp_autostats

Next Steps

  • Review the 'Auto Update Statistics' option for your database to determine if the configurations are as expected.
  • Determine how statistics are currently being updated and how to best handle this portion of the SQL Server maintenance process.
  • If you are not currently performing SQL Server maintenance including updating the statistics, then take the time to build a maintenance process for your databases.
  • If you have Auto Update Statistics enabled on all of your large databases and are not experiencing any issues consider re-evaluating this approach and use the NORECOMPUTE option of the UPDATE STATISTICS command for the large tables and let SQL Server maintain the statistics for the small tables.
  • Thank you to Armando from the MSSQLTips.com community for sharing this tip idea.  If you would like to share tips of your own, please email your ideas to tips@mssqltips.com.


Related Tips: More | Become a paid author


Last Update: 10/4/2006

Share: Share 






Comments and Feedback:

Tuesday, April 24, 2012 - 3:46:05 PM - it_is_me Read The Tip

My dear fellow DBA's.  I have a ?.  I have the following query in SQL SERVER 2008 R2:

select top 50 * from myview      and it runs in less than 3 sec.

I then change my query to select top 50 * from myview ORDER BY col4 and it runs forever.  My user claim it has been working ok in the past, but last night we failed over to another instance in the cluster.  I am trying to figure out why the query is taking too long when selecting and ORDERing from the view.  I'am at a brick wall.

Any comments anybody


Tuesday, April 24, 2012 - 8:59:13 PM - Jeremy Kadlec Read The Tip

To whom it may concern:

Here are a few items to check out:

Start with these few items and let me know what you find out.

Thank you,
Jeremy Kadlec



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email   Notify for updates
Comments
*Enter Code refresh code


 

Sponsor Information
"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen."

Quickly and accurately deploy database changes with Red Gate's SQL Compare - the industry standard comparison and deployment tool.

What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant.

Get SQL Server Tips Straight from Kevin Kline.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

Free Learning - Introduction to SQL Azure Delivered by Herve Roggero on Wednesday, June 13 @ 3:00 PM EST


Copyright (c) 2006-2012 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups
Some names and products listed are the registered trademarks of their respective owners.


Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com