<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Performance Tuning
»
Why create statistics on every column of...
18 posts, Page 2 of 2
««
1
2
Why create statistics on every column of every table in a database.
Rate Topic
Display Mode
Topic Options
Author
Message
SQLRNNR
SQLRNNR
Posted Thursday, March 25, 2010 9:19 AM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733,
Visits: 12,332
You're welcome.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #889918
Paul White
Paul White
Posted Thursday, March 25, 2010 9:49 AM
SSChampion
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989,
Visits: 10,529
There's no real harm in doing what they are asking, so it's hard to come up with a compelling argument not to do it.
My best shot would probably be to say, look, if we turn auto-create stats on, and async update stats, SQL Server will take care of it all for us. Now let's talk about something interesting, or potentially useful...and so on.
Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #889941
TheSQLGuru
TheSQLGuru
Posted Thursday, March 25, 2010 10:13 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 12:32 AM
Points: 3,574,
Visits: 5,112
Paul White NZ (3/25/2010)
There's no real harm in doing what they are asking, so it's hard to come up with a compelling argument not to do it.
My best shot would probably be to say, look, if we turn auto-create stats on, and async update stats, SQL Server will take care of it all for us. Now let's talk about something interesting, or potentially useful...and so on.
Not sure I agree with that. The update stats stuff (even if you go async) can hit performance pretty hard and on a busy/critical system you simply cannot affort that sometimes. I am of the "don't do unnecessary crap" mantra personally, and this certainly smells like that...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #889973
Paul White
Paul White
Posted Thursday, March 25, 2010 11:06 AM
SSChampion
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989,
Visits: 10,529
TheSQLGuru (3/25/2010)
Not sure I agree with that. The update stats stuff (even if you go async) can hit performance pretty hard and on a busy/critical system you simply cannot affort that sometimes. I am of the "don't do unnecessary crap" mantra personally, and this certainly smells like that...
I agree with the sentiment of avoiding unnecessary stuff. But...consider:
1. It's hard to argue that the idea is entirely daft, given that built-in facility to do it (sp_createstats)
2. SQL Server will only update statistics if they are potentially useful to the optimizer, and found to be out of date. Statistics that are never useful will never be updated, and therefore add little overhead.
3. If the statistics would be useful, they will get created at some stage anyway.
Swings and roundabouts. It depends. Horses for courses. And so on.
As I said, I think I would just convince the people concerned that 99.9% of systems are happy with auto-create and async-update stats options. Creating all the statistics would be kinda pointless, but maybe not actually dumb.
Paul
Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #890034
TheSQLGuru
TheSQLGuru
Posted Thursday, March 25, 2010 11:10 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 12:32 AM
Points: 3,574,
Visits: 5,112
>>2. SQL Server will only update statistics if they are potentially useful to the optimizer, and found to be out of date. Statistics that are never useful will never be updated, and therefore add little overhead.
Can you please provide a reference for that statement? Thanks!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #890040
Paul White
Paul White
Posted Thursday, March 25, 2010 11:18 AM
SSChampion
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989,
Visits: 10,529
TheSQLGuru (3/25/2010)
Can you please provide a reference for that statement? Thanks!
Well I said it - reference enough!
Ok, ok:
TechNet: Statistics used by the Query Optimizer
A relevant extract:
After a series of INSERTs, DELETEs, and/or UPDATEs are performed on a table, the statistics may not reflect the true data distribution in a given column or index. If the SQL Server query optimizer requires statistics for a particular column in a table that has undergone substantial update activity since the last time the statistics were created or updated, SQL Server automatically updates the statistics by sampling the column values (using auto update statistics). The statistics auto update is triggered by query optimization or by execution of a compiled plan, and involves only a subset of the columns referred to in the query. Statistics are updated before query compilation if AUTO_UPDATE_STATISTCS_ASYNC is OFF, and asynchronously if it is ON.
When a query is first compiled, if the optimizer needs a particular statistics object, and that statistics object exists, the statistics object is updated if it is out of date. When a query is executed and its plan is in the cache, the statistics the plan depends on are checked to see if they are out of date. If so, the plan is removed from the cache and during re-compilation of the query the statistics are updated. The plan also is removed from the cache if any of the statistics it depends on have changed.
Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #890050
TheSQLGuru
TheSQLGuru
Posted Thursday, March 25, 2010 11:37 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 12:32 AM
Points: 3,574,
Visits: 5,112
Thanks Paul.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #890080
Paul White
Paul White
Posted Thursday, March 25, 2010 11:42 AM
SSChampion
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989,
Visits: 10,529
TheSQLGuru (3/25/2010)
Thanks Paul.
No worries. I was a little concerned that I had missed your point - and I should not have said 'never' updated...there's always an exception, even if its just someone running sp_updatestats!
Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #890091
« Prev Topic
|
Next Topic »
18 posts, Page 2 of 2
««
1
2
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
