Automatically Create Statistics

What are the benefits of allowing sql server to automatically create
statistics? I know that auto update statistic can have drawbacks on
performance.
Regards
JTC ^..^
Hi
Without up to date statistics, the query optimizer can make terrible
decisions and produce an execution plan that is not optimal. Query
performance then goes down the drain for those queries.
Updating statistics incurs a bit of an overhead and when it kicks in, causes
a delay in completing your data modification.
In SQL Server 2005, MS have added an feature of allowing statistics to be
updated as-synchronously, not as part of the data modification.
Unless you have a very specific situation, leave Auto Statistics on.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^
|||Thanks for you reply, but my question is specific to Automatically Creating
Statistics?
Regards
JTC ^..^
|||Here is an excellent article by Lubor that should help explaining things for
you.
http://msdn.microsoft.com/library/de...server2000.asp
-oj
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^
|||I think Mike responding to auto-stats...
When auto-stats is turned on you can not control WHEN it runs, and it does
lots of IO and can interfere with other production work. Additionally,
Auto-stats will automatically do a sample of rows for large tables, instead
of doing a 100% sample, which is preferred and something which you can
specify when you run stats yourself.
I agree with Mike, unless you are experiencing problems which you can
specifically trace back to auto-stats running, leave it on..
However I would still schedule complete index rebuilds and/or stats creation
during your normal maintenance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^
|||On Thu, 11 Aug 2005 19:27:37 +0000 (UTC), "JTC ^..^"
<dave@.(nospam)JazzTheCat.co.uk> wrote:
>What are the benefits of allowing sql server to automatically create
>statistics? I know that auto update statistic can have drawbacks on
>performance.
It's possible if you have an unusually static database with lots of
updates that don't change any keys, that turning off the auto
statistics could save you a tiny percentage. That is, the stats
computed on day one might be close enough for the next month, that you
could save a tiny bit of processing that updates them in real time.
Anybody ever do that on purpose?
J.
|||Did you forget "sync topic" oj? ;-)
My guess is that you meant to post below URL:
http://msdn.microsoft.com/library/de...asp?frame=true
JTC,
I see that many replied about auto-update statistics and you explicitly was asking about auto
*create* statistics. In short, there are situation where the optimizer would benefit from knowing
about the distribution of the data even if you don't have an index on the column. Perhaps it would
pick different execution plans for a GROUP BY depending on uniqueness, for example. For these
scenarios, it is good to let the optimizer create statistics to aid in picking a good query plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"oj" <nospam_ojngo@.home.com> wrote in message news:eTIS0HsnFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Here is an excellent article by Lubor that should help explaining things for you.
> http://msdn.microsoft.com/library/de...server2000.asp
>
> --
> -oj
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> news:Xns96AFD050DD23daveJTC@.213.123.26.234...
>
|||argh...thanks for posting the correct link, Tibor. ;-)
-oj
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OzN6HOxnFHA.572@.TK2MSFTNGP15.phx.gbl...
> Did you forget "sync topic" oj? ;-)
> My guess is that you meant to post below URL:
> http://msdn.microsoft.com/library/de...asp?frame=true
>
> JTC,
> I see that many replied about auto-update statistics and you explicitly
> was asking about auto *create* statistics. In short, there are situation
> where the optimizer would benefit from knowing about the distribution of
> the data even if you don't have an index on the column. Perhaps it would
> pick different execution plans for a GROUP BY depending on uniqueness, for
> example. For these scenarios, it is good to let the optimizer create
> statistics to aid in picking a good query plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eTIS0HsnFHA.4028@.TK2MSFTNGP10.phx.gbl...
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OzN6HOxnFHA.572@.TK2MSFTNGP15.phx.gbl:

> Did you forget "sync topic" oj? ;-)
> My guess is that you meant to post below URL:
> http://msdn.microsoft.com/library/de...y/en-us/dnsql2
> k/html/statquery.asp?frame=true
>
> JTC,
> I see that many replied about auto-update statistics and you
> explicitly was asking about auto *create* statistics. In short, there
> are situation where the optimizer would benefit from knowing about the
> distribution of the data even if you don't have an index on the
> column. Perhaps it would pick different execution plans for a GROUP BY
> depending on uniqueness, for example. For these scenarios, it is good
> to let the optimizer create statistics to aid in picking a good query
> plan.
>
Thanks Tibor. I should have made my original post even clearer.
Regards
JTC ^..^
|||Even if you were to try this, you could leave auto-create ON and auto-update
OFF in such a case to make sure that you have not missed any cases in your
queries where statistics are needed.
In general, please just leave them on unless you have a specific scenario
where performance is specifically impacted by auto-stats. In almost all of
our user cases, leaving this on has no impact.
Thanks,
Conor Cunningham
SQL Server Query Optimization Development Lead
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:a3unf1d2d4et3ocs5um5i8i7enoirejv16@.4ax.com...
> On Thu, 11 Aug 2005 19:27:37 +0000 (UTC), "JTC ^..^"
> <dave@.(nospam)JazzTheCat.co.uk> wrote:
> It's possible if you have an unusually static database with lots of
> updates that don't change any keys, that turning off the auto
> statistics could save you a tiny percentage. That is, the stats
> computed on day one might be close enough for the next month, that you
> could save a tiny bit of processing that updates them in real time.
> Anybody ever do that on purpose?
> J.
>