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
SQLDraggon
SQLDraggon
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 340
Here is the issue:

Developers want statistics created on every column w00t on every table w00t in the database.

SQL version = SQL Server 2005 SP2+.
database < 200GB.
"Auto update" and "Auto create" stats are enabled.
There are currently 11000+ user created statistics.

Here is the question:
Will SQL Server ever use those statistics ??
What other SQL process besides the optimizer uses the statistics ??
Is there any way to determine statistic usage ??

I need some ammo for my argument ... sock it to me.

Enjoy
"Give them the toolsCrazy ... Not the keysSmooooth"
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: 41197 Visits: 18565
I think you are creating more work for yourself and will make maintenance on the database a little more difficult.
http://www.developer.com/db/article.php/3622881/Basics-of-Statistics-in-SQL-Server-2005

That link discusses some of the workings of statistics. Statistics are not always just created on a single column at a time. Typically the engine has a very good understanding of data usage and can create accurate statistics for use by the queries being submitted. If every table has individual statistics created on every column, you could end up with a bunch of out of date statistics that could impair performance.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SQLDraggon
SQLDraggon
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 340
I told them there is no reason to do that for every column and every table ... but they insist.
I need to come up with some good reasons not to do it.

that is why I'm asking for more ammo...

Enjoy
"Give them the toolsCrazy ... Not the keysSmooooth"
chrisfradenburg
chrisfradenburg
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2210 Visits: 2068
Just like indexes any additional statistics create maintenance overhead in both space used (it has to store it someplace) and the duration of maintenance (it does take time to update them.) If auto update stats is on then that can lead to slowness during the day while SQL updates stats that it may not even be using. If it's not on that means time needs to be put into developing a maintenance plan that will do that and a longer duration for maintenance. I would have to defer to someone with more knowledge on this point but I would also think that with additional statistics to consider when calculating the query plan compiling plans can take longer.

There are also columns where creating stats will never have the opportunity to be used. If SQL isn't using that column to search then the stats won't be used. And if a column is frequently being used but isn't indexed and doesn't have a manually created stat on it then SQL can create one if auto create stats is on. We have created stats in our DB but for the most part we rely on SQL to do that when needed.
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7066 Visits: 4076
SQLDraggon (3/23/2010)
I told them there is no reason to do that for every column and every table ... but they insist.
in that case you can also ask them why they forcing you to have more statisctics.
You can refer this link http://www.sqlservercentral.com/articles/Indexing/63534/ it might help you here

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
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: 41197 Visits: 18565
SQLDraggon (3/23/2010)
I told them there is no reason to do that for every column and every table ... but they insist.



Put the burden on them. Ask them to provide you with reasons why you should do it. Have them provide reasons and sources. Thus you can more easily counterpoint their demands.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

LutzM
LutzM
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13181 Visits: 13559
Maybe this argument will help, too:
Within one query, SQL server will only use one index per table. So, if you have the following query
SELECT col1, col2, col3, col4
FROM table
WHERE col1='something' AND col2='something different'


and you have an index on each and every single column then the query optimizer may not consider any of those indexes, since a bookmark lookup would be required to get all values used in the SELECT clause.
It might be appropriate to use a single index on col1 and col2 with col3 an col4 as included columns.
SQL server will not combine various indexes for one table within one query.

Furthermore, it might even drop performance if the table in question is heavily used for updates/inserts/deletes, since each and every index would need to be changed.


Edit: Incorrect answer.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
matt stockham
matt stockham
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1524 Visits: 3178
I'd ask them why they want autocreate stats on if they are going to do it themselves.
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: 17315 Visits: 8601
lmu92 (3/24/2010)
Maybe this argument will help, too:
Within one query, SQL server will only use one index per table. So, if you have the following query
SELECT col1, col2, col3, col4
FROM table
WHERE col1='something' AND col2='something different'


and you have an index on each and every single column then the query optimizer may not consider any of those indexes, since a bookmark lookup would be required to get all values used in the SELECT clause.
It might be appropriate to use a single index on col1 and col2 with col3 an col4 as included columns.
SQL server will not combine various indexes for one table within one query.

Furthermore, it might even drop performance if the table in question is heavily used for updates/inserts/deletes, since each and every index would need to be changed.


Your statement is false. The optimizer can use more than one index in a select statement on a single table. From this location in Books Online (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7c1f2130-5574-4058-bcfb-31c115e9bd00.htm)

--------------------
Do not use multiple aliases for a single table in the same query to simulate index intersection. This is no longer necessary because SQL Server automatically considers index intersection and can make use of multiple indexes on the same table in the same query. Consider the sample query:

SELECT * FROM lineitem
WHERE partkey BETWEEN 17000 AND 17100 AND
shipdate BETWEEN '1/1/1994' AND '1/31/1994'

SQL Server can exploit indexes on both the partkey and shipdate columns, and then perform a hash match between the two subsets to obtain the index intersection.
--------------------

I also note that this thread has nothing to do with indexes - it is about statistics.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
SQLDraggon
SQLDraggon
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 340
Thanx to all that replied to this post ... your comments are greatly appreciated and helpfull.

Enjoy
"Give them the toolsCrazy ... Not the keysSmooooth"
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