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.
>
Showing posts with label statistics. Show all posts
Showing posts with label statistics. Show all posts
Thursday, March 22, 2012
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/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.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/default.asp?url=/library/en-us/dnsql2k/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.
--
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/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.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 ^..^
>|||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/default.asp?url=/library/en-us/dnsql2k/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.
> --
> 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/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.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 ^..^
>>
>|||"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/default.asp?url=/library/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:
>>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.
>
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/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.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/default.asp?url=/library/en-us/dnsql2k/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.
--
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/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.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 ^..^
>|||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/default.asp?url=/library/en-us/dnsql2k/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.
> --
> 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/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.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 ^..^
>>
>|||"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/default.asp?url=/library/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:
>>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.
>
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/d...
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/d...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 co
lumn. Perhaps it would
pick different execution plans for a GROUP BY depending on uniqueness, for e
xample. For these
scenarios, it is good to let the optimizer create statistics to aid in picki
ng 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...[
vbcol=seagreen]
> Here is an excellent article by Lubor that should help explaining things f
or you.
> http://msdn.microsoft.com/library/d...lserver2000.asp
>
> --
> -oj
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> news:Xns96AFD050DD23daveJTC@.213.123.26.234...
>[/vbcol]|||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/d...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/d...ry/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.
>
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/d...
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/d...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 co
lumn. Perhaps it would
pick different execution plans for a GROUP BY depending on uniqueness, for e
xample. For these
scenarios, it is good to let the optimizer create statistics to aid in picki
ng 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...[
vbcol=seagreen]
> Here is an excellent article by Lubor that should help explaining things f
or you.
> http://msdn.microsoft.com/library/d...lserver2000.asp
>
> --
> -oj
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> news:Xns96AFD050DD23daveJTC@.213.123.26.234...
>[/vbcol]|||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/d...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/d...ry/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.
>
Tuesday, March 20, 2012
Automatic statistics update
Hi. I have automatic statistic update turned on for all my databases. Is
this an overhead I can do without? Could I update them overnight when the
database is hardly in use?
this an overhead I can do without? Could I update them overnight when the
database is hardly in use?
Thanks
--
Chris WestonChris Weston (chrisweston[losethislot]@.ntlworld.com) writes:
> Hi. I have automatic statistic update turned on for all my databases. Is
> this an overhead I can do without? Could I update them overnight when the
> database is hardly in use?
Unless you can deduct that auto-stats is causing you performance problems,
I would not consider doing this.
What may be important, though, is if you have large table with monotonically
growing keys, is that you run UPDATE STATISTICS on these tables with some
frequency. This is because auto-stats only sets in when 20% of the rows
have changed. For a 10 million-row table, that means that you need another
two million before autostats set in. Since the key grows monotonically, this
means that the statistics for the newly inserted rows is grossly inaccurate.
If you run a defragmentation job regularly, you don't need to do UPDATE
STATISTICS as well, as when you rebuild the index, the statistics are
updatead automatically. What we ran into was that a colleague set up a
maintenance job that would only defragment indexes with a certainly
level of fragmentation. Tables with a clustered key that grows monotically,
do not get fragmented easily, so we ran into problems with stale statistics.
Our maintenance job now performs UPDATE STATISTICS WITH FULLSCAN INDEX
on tables that don't get defragmented. (We're restricting the stats update
to indexes, because else it took too long time.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Automatic statistics on compound index
I just read a note in a book that says that SQL Server 2000 only
automatically updates the statistics on just the first column of a compound
index (provided automatic statistics feature is turned on). It suggests
that the DBA should create statistics on the other columns.
I'd never heard that before, and can't seem to find that little fasinating
tidbit in any other source.
Can anybody confirm that this is true?"Emmet Gray" <egray1@.hot.rr.com> wrote in message
news:uc9EhPDXDHA.2464@.TK2MSFTNGP09.phx.gbl...
> I just read a note in a book that says that SQL Server 2000 only
> automatically updates the statistics on just the first column of a
compound
> index (provided automatic statistics feature is turned on). It suggests
> that the DBA should create statistics on the other columns.
> I'd never heard that before, and can't seem to find that little fasinating
> tidbit in any other source.
> Can anybody confirm that this is true?
>
That's correct - this is a situation where you may want to consider manually
statistics on columns other than the first column of a composite index.
Steve
automatically updates the statistics on just the first column of a compound
index (provided automatic statistics feature is turned on). It suggests
that the DBA should create statistics on the other columns.
I'd never heard that before, and can't seem to find that little fasinating
tidbit in any other source.
Can anybody confirm that this is true?"Emmet Gray" <egray1@.hot.rr.com> wrote in message
news:uc9EhPDXDHA.2464@.TK2MSFTNGP09.phx.gbl...
> I just read a note in a book that says that SQL Server 2000 only
> automatically updates the statistics on just the first column of a
compound
> index (provided automatic statistics feature is turned on). It suggests
> that the DBA should create statistics on the other columns.
> I'd never heard that before, and can't seem to find that little fasinating
> tidbit in any other source.
> Can anybody confirm that this is true?
>
That's correct - this is a situation where you may want to consider manually
statistics on columns other than the first column of a composite index.
Steve
Monday, March 19, 2012
Automatic index (Statistics) vs Manually created indexes
Hi,
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
Erik
Hi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:
> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created indexes
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexes
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
Erik
Hi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:
> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created indexes
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexes
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>
Automatic index (Statistics) vs Manually created indexes
Hi,
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
ErikHi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:
> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created indexes
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexes
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
ErikHi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:
> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created indexes
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexes
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>
Automatic index (Statistics) vs Manually created indexes
Hi,
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
ErikHi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:
> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created index
es
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexe
s
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
ErikHi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:
> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created index
es
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexe
s
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>
Friday, February 24, 2012
autocreate statistics
hi,
my query is really running slow in sql2005... they said i should try turning on the autocreate statistics. where and how do i supposed to do that?
thanks a lot
cherriesh
Use this query:
ALTER DATABASE [MyDataBase]
SET AUTO_UPDATE_STATISTICS ON
Sunday, February 19, 2012
auto_create & update statistics is null
Does this have the effect of not creating any statistics for the db and therefore forces queries to FTS rather than use an index?
How can I tell if stats exist for the tables/indexes?
Thanks,
MikeBetter to leave the option as it is by default, and also make sure to run SP_UPDATESTATS to update statistics and also generate DBCC checks to keep up the performance.
Use SP_HELPSTATS to return the information on stats.
How can I tell if stats exist for the tables/indexes?
Thanks,
MikeBetter to leave the option as it is by default, and also make sure to run SP_UPDATESTATS to update statistics and also generate DBCC checks to keep up the performance.
Use SP_HELPSTATS to return the information on stats.
auto update/create statistics
For an OLTP application with 100+ users, is it better to disable the
following two options:
Auto create statistics
Auto update statistics
...and instead run
exec sp_updatestats daily/weekly?
TIA
Depends on your setup. If you have a reasonably sized maintenance window, I
would go for a daily sp_updatestats.
The problem with Auto update statistics IMO is that it is most likely to
kick in when your system is at its busiest.
Jacco Schalkwijk
SQL Server MVP
"Nimi" <Nimi@.discussions.microsoft.com> wrote in message
news:F6727E2C-8EFA-4291-B00A-E84E4143E57C@.microsoft.com...
> For an OLTP application with 100+ users, is it better to disable the
> following two options:
> Auto create statistics
> Auto update statistics
> ...and instead run
> exec sp_updatestats daily/weekly?
> TIA
|||you might want to disable AutoUpdate statistics IF you are seeing that it is
causing problems.
You likely do not want to disable AutoCreate Statistics.
Greg Jackson
PDX, Oregon
following two options:
Auto create statistics
Auto update statistics
...and instead run
exec sp_updatestats daily/weekly?
TIA
Depends on your setup. If you have a reasonably sized maintenance window, I
would go for a daily sp_updatestats.
The problem with Auto update statistics IMO is that it is most likely to
kick in when your system is at its busiest.
Jacco Schalkwijk
SQL Server MVP
"Nimi" <Nimi@.discussions.microsoft.com> wrote in message
news:F6727E2C-8EFA-4291-B00A-E84E4143E57C@.microsoft.com...
> For an OLTP application with 100+ users, is it better to disable the
> following two options:
> Auto create statistics
> Auto update statistics
> ...and instead run
> exec sp_updatestats daily/weekly?
> TIA
|||you might want to disable AutoUpdate statistics IF you are seeing that it is
causing problems.
You likely do not want to disable AutoCreate Statistics.
Greg Jackson
PDX, Oregon
auto update/create statistics
For an OLTP application with 100+ users, is it better to disable the
following two options:
Auto create statistics
Auto update statistics
...and instead run
exec sp_updatestats daily/weekly?
TIADepends on your setup. If you have a reasonably sized maintenance window, I
would go for a daily sp_updatestats.
The problem with Auto update statistics IMO is that it is most likely to
kick in when your system is at its busiest.
--
Jacco Schalkwijk
SQL Server MVP
"Nimi" <Nimi@.discussions.microsoft.com> wrote in message
news:F6727E2C-8EFA-4291-B00A-E84E4143E57C@.microsoft.com...
> For an OLTP application with 100+ users, is it better to disable the
> following two options:
> Auto create statistics
> Auto update statistics
> ...and instead run
> exec sp_updatestats daily/weekly?
> TIA|||you might want to disable AutoUpdate statistics IF you are seeing that it is
causing problems.
You likely do not want to disable AutoCreate Statistics.
Greg Jackson
PDX, Oregon
following two options:
Auto create statistics
Auto update statistics
...and instead run
exec sp_updatestats daily/weekly?
TIADepends on your setup. If you have a reasonably sized maintenance window, I
would go for a daily sp_updatestats.
The problem with Auto update statistics IMO is that it is most likely to
kick in when your system is at its busiest.
--
Jacco Schalkwijk
SQL Server MVP
"Nimi" <Nimi@.discussions.microsoft.com> wrote in message
news:F6727E2C-8EFA-4291-B00A-E84E4143E57C@.microsoft.com...
> For an OLTP application with 100+ users, is it better to disable the
> following two options:
> Auto create statistics
> Auto update statistics
> ...and instead run
> exec sp_updatestats daily/weekly?
> TIA|||you might want to disable AutoUpdate statistics IF you are seeing that it is
causing problems.
You likely do not want to disable AutoCreate Statistics.
Greg Jackson
PDX, Oregon
auto update/create statistics
For an OLTP application with 100+ users, is it better to disable the
following two options:
Auto create statistics
Auto update statistics
...and instead run
exec sp_updatestats daily/weekly?
TIADepends on your setup. If you have a reasonably sized maintenance window, I
would go for a daily sp_updatestats.
The problem with Auto update statistics IMO is that it is most likely to
kick in when your system is at its busiest.
Jacco Schalkwijk
SQL Server MVP
"Nimi" <Nimi@.discussions.microsoft.com> wrote in message
news:F6727E2C-8EFA-4291-B00A-E84E4143E57C@.microsoft.com...
> For an OLTP application with 100+ users, is it better to disable the
> following two options:
> Auto create statistics
> Auto update statistics
> ...and instead run
> exec sp_updatestats daily/weekly?
> TIA|||you might want to disable AutoUpdate statistics IF you are seeing that it is
causing problems.
You likely do not want to disable AutoCreate Statistics.
Greg Jackson
PDX, Oregon
following two options:
Auto create statistics
Auto update statistics
...and instead run
exec sp_updatestats daily/weekly?
TIADepends on your setup. If you have a reasonably sized maintenance window, I
would go for a daily sp_updatestats.
The problem with Auto update statistics IMO is that it is most likely to
kick in when your system is at its busiest.
Jacco Schalkwijk
SQL Server MVP
"Nimi" <Nimi@.discussions.microsoft.com> wrote in message
news:F6727E2C-8EFA-4291-B00A-E84E4143E57C@.microsoft.com...
> For an OLTP application with 100+ users, is it better to disable the
> following two options:
> Auto create statistics
> Auto update statistics
> ...and instead run
> exec sp_updatestats daily/weekly?
> TIA|||you might want to disable AutoUpdate statistics IF you are seeing that it is
causing problems.
You likely do not want to disable AutoCreate Statistics.
Greg Jackson
PDX, Oregon
Auto update Statistics Option
The developer of a finance application suggests us not to select the "Auto
Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
Model). The reason he mentions is that it will affect the database
performance. However, from a number of articles, they suggest us to turn it
ON and it is the default setting as well.
The size of the database file is around 10GB and there are around 20
concurrent users (mainly retrieving information).
I would like to know does the Execution Plan is fixed for Stored Procedure
OR it changes from time to time (Just like running query according to the
data structure) ?
Your advice is sought.
The Execution Plan for Stored Procedure may change from time to time. For
example, after 'Update Statistics' of related table(s), the SQL Server will
generate a new Execution Plan for the SP. Below is more information from BOL
(Execution Plan Caching and Reuse):
Recompiling Execution Plans
Certain changes in a database can cause an execution plan to be either
inefficient or invalid, given the new state of the database. SQL Server
detects the changes that invalidate an execution plan, and marks the plan as
invalid. A new plan must then be recompiled for the next connection that
executes the query. The conditions that cause a plan to be invalidated
include:
Any structural changes made to a table or view referenced by the query
(ALTER TABLE and ALTER VIEW).
New distribution statistics generated either explicitly from a statement
such as UPDATE STATISTICS or automatically.
Dropping an index used by the execution plan.
An explicit call to sp_recompile.
Large numbers of changes to keys (generated by INSERT or DELETE statements
from other users that modify a table referenced by the query).
For tables with triggers, if the number of rows in the inserted or deleted
tables grows significantly.
"Jason" wrote:
> The developer of a finance application suggests us not to select the "Auto
> Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
> Model). The reason he mentions is that it will affect the database
> performance. However, from a number of articles, they suggest us to turn it
> ON and it is the default setting as well.
> The size of the database file is around 10GB and there are around 20
> concurrent users (mainly retrieving information).
> I would like to know does the Execution Plan is fixed for Stored Procedure
> OR it changes from time to time (Just like running query according to the
> data structure) ?
> Your advice is sought.
Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
Model). The reason he mentions is that it will affect the database
performance. However, from a number of articles, they suggest us to turn it
ON and it is the default setting as well.
The size of the database file is around 10GB and there are around 20
concurrent users (mainly retrieving information).
I would like to know does the Execution Plan is fixed for Stored Procedure
OR it changes from time to time (Just like running query according to the
data structure) ?
Your advice is sought.
The Execution Plan for Stored Procedure may change from time to time. For
example, after 'Update Statistics' of related table(s), the SQL Server will
generate a new Execution Plan for the SP. Below is more information from BOL
(Execution Plan Caching and Reuse):
Recompiling Execution Plans
Certain changes in a database can cause an execution plan to be either
inefficient or invalid, given the new state of the database. SQL Server
detects the changes that invalidate an execution plan, and marks the plan as
invalid. A new plan must then be recompiled for the next connection that
executes the query. The conditions that cause a plan to be invalidated
include:
Any structural changes made to a table or view referenced by the query
(ALTER TABLE and ALTER VIEW).
New distribution statistics generated either explicitly from a statement
such as UPDATE STATISTICS or automatically.
Dropping an index used by the execution plan.
An explicit call to sp_recompile.
Large numbers of changes to keys (generated by INSERT or DELETE statements
from other users that modify a table referenced by the query).
For tables with triggers, if the number of rows in the inserted or deleted
tables grows significantly.
"Jason" wrote:
> The developer of a finance application suggests us not to select the "Auto
> Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
> Model). The reason he mentions is that it will affect the database
> performance. However, from a number of articles, they suggest us to turn it
> ON and it is the default setting as well.
> The size of the database file is around 10GB and there are around 20
> concurrent users (mainly retrieving information).
> I would like to know does the Execution Plan is fixed for Stored Procedure
> OR it changes from time to time (Just like running query according to the
> data structure) ?
> Your advice is sought.
Auto update Statistics Option
The developer of a finance application suggests us not to select the "Auto
Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
Model). The reason he mentions is that it will affect the database
performance. However, from a number of articles, they suggest us to turn it
ON and it is the default setting as well.
The size of the database file is around 10GB and there are around 20
concurrent users (mainly retrieving information).
I would like to know does the Execution Plan is fixed for Stored Procedure
OR it changes from time to time (Just like running query according to the
data structure) ?
Your advice is sought.The Execution Plan for Stored Procedure may change from time to time. For
example, after 'Update Statistics' of related table(s), the SQL Server will
generate a new Execution Plan for the SP. Below is more information from BOL
(Execution Plan Caching and Reuse):
Recompiling Execution Plans
Certain changes in a database can cause an execution plan to be either
inefficient or invalid, given the new state of the database. SQL Server
detects the changes that invalidate an execution plan, and marks the plan as
invalid. A new plan must then be recompiled for the next connection that
executes the query. The conditions that cause a plan to be invalidated
include:
Any structural changes made to a table or view referenced by the query
(ALTER TABLE and ALTER VIEW).
New distribution statistics generated either explicitly from a statement
such as UPDATE STATISTICS or automatically.
Dropping an index used by the execution plan.
An explicit call to sp_recompile.
Large numbers of changes to keys (generated by INSERT or DELETE statements
from other users that modify a table referenced by the query).
For tables with triggers, if the number of rows in the inserted or deleted
tables grows significantly.
"Jason" wrote:
> The developer of a finance application suggests us not to select the "Auto
> Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
> Model). The reason he mentions is that it will affect the database
> performance. However, from a number of articles, they suggest us to turn it
> ON and it is the default setting as well.
> The size of the database file is around 10GB and there are around 20
> concurrent users (mainly retrieving information).
> I would like to know does the Execution Plan is fixed for Stored Procedure
> OR it changes from time to time (Just like running query according to the
> data structure) ?
> Your advice is sought.
Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
Model). The reason he mentions is that it will affect the database
performance. However, from a number of articles, they suggest us to turn it
ON and it is the default setting as well.
The size of the database file is around 10GB and there are around 20
concurrent users (mainly retrieving information).
I would like to know does the Execution Plan is fixed for Stored Procedure
OR it changes from time to time (Just like running query according to the
data structure) ?
Your advice is sought.The Execution Plan for Stored Procedure may change from time to time. For
example, after 'Update Statistics' of related table(s), the SQL Server will
generate a new Execution Plan for the SP. Below is more information from BOL
(Execution Plan Caching and Reuse):
Recompiling Execution Plans
Certain changes in a database can cause an execution plan to be either
inefficient or invalid, given the new state of the database. SQL Server
detects the changes that invalidate an execution plan, and marks the plan as
invalid. A new plan must then be recompiled for the next connection that
executes the query. The conditions that cause a plan to be invalidated
include:
Any structural changes made to a table or view referenced by the query
(ALTER TABLE and ALTER VIEW).
New distribution statistics generated either explicitly from a statement
such as UPDATE STATISTICS or automatically.
Dropping an index used by the execution plan.
An explicit call to sp_recompile.
Large numbers of changes to keys (generated by INSERT or DELETE statements
from other users that modify a table referenced by the query).
For tables with triggers, if the number of rows in the inserted or deleted
tables grows significantly.
"Jason" wrote:
> The developer of a finance application suggests us not to select the "Auto
> Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
> Model). The reason he mentions is that it will affect the database
> performance. However, from a number of articles, they suggest us to turn it
> ON and it is the default setting as well.
> The size of the database file is around 10GB and there are around 20
> concurrent users (mainly retrieving information).
> I would like to know does the Execution Plan is fixed for Stored Procedure
> OR it changes from time to time (Just like running query according to the
> data structure) ?
> Your advice is sought.
Auto update Statistics Option
The developer of a finance application suggests us not to select the "Auto
Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
Model). The reason he mentions is that it will affect the database
performance. However, from a number of articles, they suggest us to turn it
ON and it is the default setting as well.
The size of the database file is around 10GB and there are around 20
concurrent users (mainly retrieving information).
I would like to know does the Execution Plan is fixed for Stored Procedure
OR it changes from time to time (Just like running query according to the
data structure) ?
Your advice is sought.The Execution Plan for Stored Procedure may change from time to time. For
example, after 'Update Statistics' of related table(s), the SQL Server will
generate a new Execution Plan for the SP. Below is more information from BOL
(Execution Plan Caching and Reuse):
Recompiling Execution Plans
Certain changes in a database can cause an execution plan to be either
inefficient or invalid, given the new state of the database. SQL Server
detects the changes that invalidate an execution plan, and marks the plan as
invalid. A new plan must then be recompiled for the next connection that
executes the query. The conditions that cause a plan to be invalidated
include:
Any structural changes made to a table or view referenced by the query
(ALTER TABLE and ALTER VIEW).
New distribution statistics generated either explicitly from a statement
such as UPDATE STATISTICS or automatically.
Dropping an index used by the execution plan.
An explicit call to sp_recompile.
Large numbers of changes to keys (generated by INSERT or DELETE statements
from other users that modify a table referenced by the query).
For tables with triggers, if the number of rows in the inserted or deleted
tables grows significantly.
"Jason" wrote:
> The developer of a finance application suggests us not to select the "Auto
> Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
> Model). The reason he mentions is that it will affect the database
> performance. However, from a number of articles, they suggest us to turn
it
> ON and it is the default setting as well.
> The size of the database file is around 10GB and there are around 20
> concurrent users (mainly retrieving information).
> I would like to know does the Execution Plan is fixed for Stored Procedure
> OR it changes from time to time (Just like running query according to the
> data structure) ?
> Your advice is sought.
Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
Model). The reason he mentions is that it will affect the database
performance. However, from a number of articles, they suggest us to turn it
ON and it is the default setting as well.
The size of the database file is around 10GB and there are around 20
concurrent users (mainly retrieving information).
I would like to know does the Execution Plan is fixed for Stored Procedure
OR it changes from time to time (Just like running query according to the
data structure) ?
Your advice is sought.The Execution Plan for Stored Procedure may change from time to time. For
example, after 'Update Statistics' of related table(s), the SQL Server will
generate a new Execution Plan for the SP. Below is more information from BOL
(Execution Plan Caching and Reuse):
Recompiling Execution Plans
Certain changes in a database can cause an execution plan to be either
inefficient or invalid, given the new state of the database. SQL Server
detects the changes that invalidate an execution plan, and marks the plan as
invalid. A new plan must then be recompiled for the next connection that
executes the query. The conditions that cause a plan to be invalidated
include:
Any structural changes made to a table or view referenced by the query
(ALTER TABLE and ALTER VIEW).
New distribution statistics generated either explicitly from a statement
such as UPDATE STATISTICS or automatically.
Dropping an index used by the execution plan.
An explicit call to sp_recompile.
Large numbers of changes to keys (generated by INSERT or DELETE statements
from other users that modify a table referenced by the query).
For tables with triggers, if the number of rows in the inserted or deleted
tables grows significantly.
"Jason" wrote:
> The developer of a finance application suggests us not to select the "Auto
> Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
> Model). The reason he mentions is that it will affect the database
> performance. However, from a number of articles, they suggest us to turn
it
> ON and it is the default setting as well.
> The size of the database file is around 10GB and there are around 20
> concurrent users (mainly retrieving information).
> I would like to know does the Execution Plan is fixed for Stored Procedure
> OR it changes from time to time (Just like running query according to the
> data structure) ?
> Your advice is sought.
Auto Update Statistics and Tempdb
Is there any use for "Auto Update Statistics" enabled on TempDB?
Or would it be an hinderance?If you have people who create tables and indexes in tempdb, then insert rows
and use the data. you should probably leave it turned on.
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
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:%23Ir7u5JTEHA.1472@.TK2MSFTNGP09.phx.gbl...
> Is there any use for "Auto Update Statistics" enabled on TempDB?
> Or would it be an hinderance?
>|||frankm,
Yep, I agree with Wayne, leave it on.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
frankm wrote:
> Is there any use for "Auto Update Statistics" enabled on TempDB?
> Or would it be an hinderance?
>
Or would it be an hinderance?If you have people who create tables and indexes in tempdb, then insert rows
and use the data. you should probably leave it turned on.
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
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:%23Ir7u5JTEHA.1472@.TK2MSFTNGP09.phx.gbl...
> Is there any use for "Auto Update Statistics" enabled on TempDB?
> Or would it be an hinderance?
>|||frankm,
Yep, I agree with Wayne, leave it on.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
frankm wrote:
> Is there any use for "Auto Update Statistics" enabled on TempDB?
> Or would it be an hinderance?
>
Auto Update Statistics and Tempdb
Is there any use for "Auto Update Statistics" enabled on TempDB?
Or would it be an hinderance?
If you have people who create tables and indexes in tempdb, then insert rows
and use the data. you should probably leave it turned on.
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
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:%23Ir7u5JTEHA.1472@.TK2MSFTNGP09.phx.gbl...
> Is there any use for "Auto Update Statistics" enabled on TempDB?
> Or would it be an hinderance?
>
|||frankm,
Yep, I agree with Wayne, leave it on.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
frankm wrote:
> Is there any use for "Auto Update Statistics" enabled on TempDB?
> Or would it be an hinderance?
>
Or would it be an hinderance?
If you have people who create tables and indexes in tempdb, then insert rows
and use the data. you should probably leave it turned on.
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
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:%23Ir7u5JTEHA.1472@.TK2MSFTNGP09.phx.gbl...
> Is there any use for "Auto Update Statistics" enabled on TempDB?
> Or would it be an hinderance?
>
|||frankm,
Yep, I agree with Wayne, leave it on.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
frankm wrote:
> Is there any use for "Auto Update Statistics" enabled on TempDB?
> Or would it be an hinderance?
>
Auto Update Statistics and Tempdb
Is there any use for "Auto Update Statistics" enabled on TempDB?
Or would it be an hinderance?If you have people who create tables and indexes in tempdb, then insert rows
and use the data. you should probably leave it turned on.
--
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
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:%23Ir7u5JTEHA.1472@.TK2MSFTNGP09.phx.gbl...
> Is there any use for "Auto Update Statistics" enabled on TempDB?
> Or would it be an hinderance?
>|||frankm,
Yep, I agree with Wayne, leave it on.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
frankm wrote:
> Is there any use for "Auto Update Statistics" enabled on TempDB?
> Or would it be an hinderance?
>
Or would it be an hinderance?If you have people who create tables and indexes in tempdb, then insert rows
and use the data. you should probably leave it turned on.
--
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
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:%23Ir7u5JTEHA.1472@.TK2MSFTNGP09.phx.gbl...
> Is there any use for "Auto Update Statistics" enabled on TempDB?
> Or would it be an hinderance?
>|||frankm,
Yep, I agree with Wayne, leave it on.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
frankm wrote:
> Is there any use for "Auto Update Statistics" enabled on TempDB?
> Or would it be an hinderance?
>
Thursday, February 16, 2012
Auto update statistics and Auto create statistics options
Recovery Model of the Production Database is FULL.
We have activated the Auto update statistics and Auto
create statistics options. We would like to know
switching off these options, will the size of the
Transaction Log be smaller ?
ThanksNo, these doesn't affect the size of the transaction log. If you have
problem with the size of the transaction log, there are other places to
look. Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:9b5501c3eaa8$aae1dcc0$a401280a@.phx.gbl...
> Recovery Model of the Production Database is FULL.
> We have activated the Auto update statistics and Auto
> create statistics options. We would like to know
> switching off these options, will the size of the
> Transaction Log be smaller ?
> Thanks
We have activated the Auto update statistics and Auto
create statistics options. We would like to know
switching off these options, will the size of the
Transaction Log be smaller ?
ThanksNo, these doesn't affect the size of the transaction log. If you have
problem with the size of the transaction log, there are other places to
look. Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:9b5501c3eaa8$aae1dcc0$a401280a@.phx.gbl...
> Recovery Model of the Production Database is FULL.
> We have activated the Auto update statistics and Auto
> create statistics options. We would like to know
> switching off these options, will the size of the
> Transaction Log be smaller ?
> Thanks
Subscribe to:
Posts (Atom)