SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Why create statistics on every column of every table in a database.


Why create statistics on every column of every table in a database.

Author
Message
SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41187 Visits: 18565
You're welcome.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Paul White
Paul White
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20556 Visits: 11359
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
TheSQLGuru
TheSQLGuru
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17307 Visits: 8601
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... Hehe

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Paul White
Paul White
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20556 Visits: 11359
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... Hehe

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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
TheSQLGuru
TheSQLGuru
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17307 Visits: 8601
>>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 on googles mail service
Paul White
Paul White
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20556 Visits: 11359
TheSQLGuru (3/25/2010)
Can you please provide a reference for that statement? Thanks!

Well I said it - reference enough! :-D Laugh

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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
TheSQLGuru
TheSQLGuru
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17307 Visits: 8601
Thanks Paul.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Paul White
Paul White
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20556 Visits: 11359
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
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