InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 04:00:00 GMT20http://www.sqlservercentral.com/Forums/Topic887777-360-1.aspx[quote][b]TheSQLGuru (3/25/2010)[/b][hr]Thanks Paul.[/quote]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!Thu, 25 Mar 2010 11:42:25 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspxThanks Paul.Thu, 25 Mar 2010 11:37:02 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspx[quote][b]TheSQLGuru (3/25/2010)[/b][hr]Can you please provide a reference for that statement? Thanks![/quote]Well I said it - reference enough! :-D :laugh:Ok, ok:[u][url=http://technet.microsoft.com/en-us/library/cc966419.aspx#XSLTsection130121120120]TechNet: Statistics used by the Query Optimizer[/url][/u]A relevant extract:[i][quote][color="#0000FF"]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.[/quote][/i][/color]Thu, 25 Mar 2010 11:18:23 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspx>>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!Thu, 25 Mar 2010 11:10:25 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspx[quote][b]TheSQLGuru (3/25/2010)[/b][hr]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:[/quote]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.PaulThu, 25 Mar 2010 11:06:34 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspx[quote][b]Paul White NZ (3/25/2010)[/b][hr]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.[/quote]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:Thu, 25 Mar 2010 10:13:43 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspxThere'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.Thu, 25 Mar 2010 09:49:52 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspxYou're welcome.Thu, 25 Mar 2010 09:19:33 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspxThanx to all that replied to this post ... your comments are greatly appreciated and helpfull.Thu, 25 Mar 2010 08:34:51 GMTSQLDraggonhttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspx[quote][b]lmu92 (3/24/2010)[/b][hr]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[code="sql"]SELECT col1, col2, col3, col4FROM tableWHERE col1='something' AND col2='something different'[/code]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.[/quote]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.Thu, 25 Mar 2010 06:01:42 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspxI'd ask them why they want autocreate stats on if they are going to do it themselves.Wed, 24 Mar 2010 14:15:13 GMTmatt stockhamhttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspxMaybe this argument will help, too:[strike]Within one query, SQL server will only use one index per table. So, if you have the following query[/strike][code="sql"]SELECT col1, col2, col3, col4FROM tableWHERE col1='something' AND col2='something different'[/code][strike]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.[/strike]Edit: Incorrect answer.Wed, 24 Mar 2010 12:19:56 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspx[quote][b]SQLDraggon (3/23/2010)[/b][hr]I told them there is no reason to do that for every column and every table ... but they insist.[/quote]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.Wed, 24 Mar 2010 09:15:58 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspx[quote][b]SQLDraggon (3/23/2010)[/b][hr]I told them there is no reason to do that for every column and every table ... but they insist.[/quote] in that case you can also ask them why they forcing you to have more statisctics.You can refer this link [url]http://www.sqlservercentral.com/articles/Indexing/63534/[/url] it might help you hereWed, 24 Mar 2010 08:23:49 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspxJust 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.Wed, 24 Mar 2010 07:18:41 GMTcfradenburghttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspxI 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...Tue, 23 Mar 2010 01:56:25 GMTSQLDraggonhttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspxI think you are creating more work for yourself and will make maintenance on the database a little more difficult.[url]http://www.developer.com/db/article.php/3622881/Basics-of-Statistics-in-SQL-Server-2005[/url]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.Mon, 22 Mar 2010 18:09:35 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic887777-360-1.aspxHere 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.Mon, 22 Mar 2010 17:33:07 GMTSQLDraggon