Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting the Most out of Statistics


Getting the Most out of Statistics

Author
Message
Ed Pollack
Ed Pollack
Mr or Mrs. 500
Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)

Group: General Forum Members
Points: 598 Visits: 513
Comments posted to this topic are about the item Getting the Most out of Statistics
dr.blowfin
dr.blowfin
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 252
Good article, just wanted to add that we use the Ola Hallagren script suite for maintaining statistics at a lot of our larger client sites. Basically, updating stats was taking far too long in our maintenance cycle and we were looking for ways to make less it time consuming. This script suite is also great for Index maintenance and has saved us a lot of time there, but that's beside the point. It handles updating Statistics with the following options:
You can choose to update all statistics, statistics on indexes only, or statistics on columns only. You can also choose to update the statistics only if any rows have been modified since the most recent statistics update.

http://ola.hallengren.com/
Eirikur Eiriksson
Eirikur Eiriksson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10781 Visits: 18385
Thank you Ed for this very good peace, nicely done indeed!
Cool
Sean Smith (SSC)
Sean Smith (SSC)
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 980
Great article. Thanks for sharing!
feroz.durani
feroz.durani
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 254
Excellent reasoning. Does sp_updatestats perform a FULL_SCAN or 50% sampling ?
Ed Pollack
Ed Pollack
Mr or Mrs. 500
Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)

Group: General Forum Members
Points: 598 Visits: 513
feroz.durani (6/9/2014)
Excellent reasoning. Does sp_updatestats perform a FULL_SCAN or 50% sampling ?


Good question! sp_updatestats will run an UPDATE STATISTICS statement on all statistics in the database. This will use the default sampling that UPDATE STATISTICS would use if you ran that instead and did not include any additional parameters or options.

The default sampling of UPDATE STATISTICS is based on the data distribution and type, and therefore is not uniform. You can pass a resample parameter into sp_updatestats, but all that does is tell it if it should use the same sample size as last time or not (default is to not reuse the previous sample size). So sp_updatestats does not use FULLSCAN or 50%, nor can you tell it to do so.

For more granular control over the updating of statistics, use UPDATE STATISTICS, where you can specify more options,if desired. If the defaults are cool with you, then sp_updatestats is worth the convenience.
Andre Guerreiro
Andre Guerreiro
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1197 Visits: 1515
Very concise and informative. Thank you.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Douglas Osborne-229812
Douglas Osborne-229812
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 360
Great article.

Aligns perfectly with your SQL Saturday Presentation 2 days ago - awesome contributions to the cause!

Doug
Jonathan Cohen
Jonathan Cohen
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 77
Very nice and clear Ed.
Hope you are not getting overworked on SQL Saturday.

Jonathan Cohen
Ed Pollack
Ed Pollack
Mr or Mrs. 500
Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)

Group: General Forum Members
Points: 598 Visits: 513
Jonathan Cohen (6/10/2014)
Very nice and clear Ed.
Hope you are not getting overworked on SQL Saturday.

Jonathan Cohen


Thanks!!

So far so good w/ our SQL Saturday---I've got a bunch of great volunteers that are helping to take some of the load off, and lots of help with marketing. Looking forward to Rochester and Maine before getting a month "off" from traveling : )
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search