Getting the Most out of Statistics

Question

Getting the Most out of Statistics

Ed Pollack

Hall of Fame

Points: 3199
More actions
June 7, 2014 at 3:22 pm

#294094

Comments posted to this topic are about the item Getting the Most out of Statistics

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

dr.blowfin Say Hey Kid Points: 686 More actions · Answer 1

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/[/url]

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

Thank you Ed for this very good peace, nicely done indeed!

😎

Sean Smith SSCertifiable Points: 5977 More actions · Answer 3

Sean Smith

SSCertifiable

Points: 5977

June 9, 2014 at 6:33 am

#1720057

Great article. Thanks for sharing!

feroz.durani SSC Rookie Points: 43 More actions · Answer 4

Excellent reasoning. Does sp_updatestats perform a FULL_SCAN or 50% sampling ?

Ed Pollack Hall of Fame Points: 3199 More actions · Answer 5

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 SSCertifiable Points: 7319 More actions · Answer 6

Very concise and informative. Thank you.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

SQLOzzie Default port Points: 1441 More actions · Answer 7

Great article.

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

Doug

Jonathan Cohen SSC Enthusiast Points: 126 More actions · Answer 8

Very nice and clear Ed.

Hope you are not getting overworked on SQL Saturday.

Jonathan Cohen

Ed Pollack Hall of Fame Points: 3199 More actions · Answer 9

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 : )

deanna_15_kim Newbie Points: 5 More actions · Answer 10

Great article. I have been working with SQL Server for a year and this article is very informative for someone like myself. Thanks.