Is It Necessary to Update Statistics?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Is It Necessary to Update Statistics?

Is It Necessary to Update Statistics?

Rate Topic

Display Mode

Topic Options

Author

Message

dbaforever

Posted Wednesday, November 07, 2012 8:45 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 3:02 PM
Points: 135, Visits: 460

My 2008 R2 database has Auto Create Statistics and Auto Update Statistics both set to TRUE.

Is it necessary to also perform UPDATE STATISTICS on every table in my Maint Plan?

Thanks, Bill

Post #1382244

Stewart "Arturius" Campbell

Posted Thursday, November 08, 2012 12:08 AM

Hall of Fame

Group: General Forum Members
Last Login: Monday, May 20, 2013 3:48 AM
Points: 3,125, Visits: 4,311

Usually, SQL takes care of the UPDATE Statistics automatically.
However, there may be situations where this does not occur, e.g. when the number of UPDATEd / INSERTed / DELETEd rows does not reach or exceed the specified threshold (default 20%, changable from SQL2008R2 SP1 using trace flag 2371) for auto-updates (especially on large tables)

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #1382287

Stewart "Arturius" Campbell

Posted Thursday, November 08, 2012 12:14 AM

Hall of Fame

Group: General Forum Members
Last Login: Monday, May 20, 2013 3:48 AM
Points: 3,125, Visits: 4,311

for ease of reference:
http://www.microsoft.com/casestudies/Microsoft-SQL-Server-2012-Enterprise/Microsoft-IT/Microsoft-Ensures-Smooth-Operation-of-ERP-System-and-Cuts-Disaster-Recovery-Time/710000000493
http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #1382288

dbaforever

Posted Thursday, November 08, 2012 8:39 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 3:02 PM
Points: 135, Visits: 460

Stewart. thanks for your ideas and for the post about SS 2012 and its' new features for updating stats.

My instance is 2008 so I'll probably leave the UPDATE STATISTICS in my maint. plan, just to be sure they are nice and tidy.

Bill in the USA

Post #1382803

« Prev Topic | Next Topic »

Permissions