Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is It Necessary to Update Statistics? Expand / Collapse
Author
Message
Posted Wednesday, November 7, 2012 8:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:41 PM
Points: 150, Visits: 499
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
Posted Thursday, November 8, 2012 12:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:27 AM
Points: 4,107, Visits: 5,468
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
Posted Thursday, November 8, 2012 12:14 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:27 AM
Points: 4,107, Visits: 5,468
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
Posted Thursday, November 8, 2012 8:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:41 PM
Points: 150, Visits: 499
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 »

Add to briefcase

Permissions Expand / Collapse