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 ««12

SQL Server Backups & Maintenance - Best Practices Expand / Collapse
Author
Message
Posted Thursday, July 18, 2013 8:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 42,342, Visits: 35,406
John Mitchell-245523 (7/18/2013)
(3) The statistics update automatically triggered by ALTER INDEX REBUILD is based on a limited scan of the data, whereas UPDATE STATISTICS is based on a full scan.


Other way around.

When an index rebuild is run, the statistics on that index are updated with fullscan as part of the index rebuild process. It's not that the rebuild is triggering an automatic update, it's not. The update is part and parcel of the index rebuild operation.
On larger tables, UPDATE STATISTICS is sampled unless WITH FULLSCAN is specified. The details of what constitutes large and the sample rates are detailed in Books Online.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1475054
Posted Thursday, July 18, 2013 8:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:51 AM
Points: 5,208, Visits: 9,367
GilaMonster (7/18/2013)

Other way around.

When an index rebuild is run, the statistics on that index are updated with fullscan as part of the index rebuild process. It's not that the rebuild is triggering an automatic update, it's not. The update is part and parcel of the index rebuild operation.
On larger tables, UPDATE STATISTICS is sampled unless WITH FULLSCAN is specified. The details of what constitutes large and the sample rates are detailed in Books Online.

Gail is right. I must have been thinking about partitioned indexes, and that only applies in SQL Server 2012 anyway. And I was mostly wrong about UPDATE STATISTICS, so please ignore my third point!

John
Post #1475058
Posted Friday, July 19, 2013 12:05 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:49 AM
Points: 4,386, Visits: 9,499
Andrew Pruski (7/17/2013)
Sorry but an update of the statistics is necessary as a rebuild index will not update the statistics on non indexed columns.

Andrew


Correct - however, it is rather wasteful to update statistics for index statistics that have just been updated due to an index rebuild. And, if you are not careful - you end up with a sampling rate instead of the recently built full scan.

Depending on the system, the maintenance window, etc... I will usually setup a process to rebuild all indexes weekly, and follow that with a task to update column statistics only with a full scan. That is usually good enough for the smaller systems I manage.

For larger systems, it gets much more complicated...


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1475634
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse