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

Why create statistics on every column of every table in a database. Expand / Collapse
Author
Message
Posted Thursday, March 25, 2010 9:19 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 17,823, Visits: 15,754
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
Post #889918
Posted Thursday, March 25, 2010 9:49 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
Posted Thursday, March 25, 2010 10:13 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:52 PM
Points: 4,404, Visits: 6,264
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
Posted Thursday, March 25, 2010 11:06 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
Posted Thursday, March 25, 2010 11:10 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:52 PM
Points: 4,404, Visits: 6,264
>>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
Posted Thursday, March 25, 2010 11:18 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
Posted Thursday, March 25, 2010 11:37 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:52 PM
Points: 4,404, Visits: 6,264
Thanks Paul.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #890080
Posted Thursday, March 25, 2010 11:42 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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 »

Add to briefcase ««12

Permissions Expand / Collapse