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 auto. Show all posts
Showing posts with label auto. 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.
>
Saturday, February 25, 2012
AutoIncrement Primary Key
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>
AutoIncrement Primary Key
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
You could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>
access but for SQL 2000? Thanks,
- Gabe
You could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>
AutoIncrement Primary Key
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
You could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>
access but for SQL 2000? Thanks,
- Gabe
You could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>
AutoIncrement Primary Key
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>
Auto-increment key id field help
I have a key field ID (int) for a table. How can I auto incremnt the key so that when I insert a new record I can get a new key. I should not have to know the next available number in my table. What settings do I use for the field.
Thanks in advance
MoonWa
Check out the IDENTITY property.
AutoGrowth - Which data file does SQL Prefers to auto grow & why ?
Win2k3, SQL 2000, SP4
If we have multiple datafiles in a filegroup, lets say 8,
How does SQL distribute data ?(Will all data for a single table will go in
one file or will it be spread)
When there is need for space are all files auto grown uniformely OR just one
file ?
If just one file, will the same file auto grow next time too ?
Sorry to put too many questions ? Just trying to understand the Auto grow
feature of SQL.
Advance thanks for sharing the knowledge...
RangaHi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_9sab.asp
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_2ak3.asp
Filegroups use a proportional fill strategy across all the files within each
filegroup. If there are multiple files in a filegroup, they do not autogrow
until all the files are full. The file to grow is then chosen using a
round-robin algorithm.
John
"Ranga" wrote:
> Win2k3, SQL 2000, SP4
> If we have multiple datafiles in a filegroup, lets say 8,
> How does SQL distribute data ?(Will all data for a single table will go in
> one file or will it be spread)
> When there is need for space are all files auto grown uniformely OR just one
> file ?
> If just one file, will the same file auto grow next time too ?
> Sorry to put too many questions ? Just trying to understand the Auto grow
> feature of SQL.
> Advance thanks for sharing the knowledge...
> Ranga
If we have multiple datafiles in a filegroup, lets say 8,
How does SQL distribute data ?(Will all data for a single table will go in
one file or will it be spread)
When there is need for space are all files auto grown uniformely OR just one
file ?
If just one file, will the same file auto grow next time too ?
Sorry to put too many questions ? Just trying to understand the Auto grow
feature of SQL.
Advance thanks for sharing the knowledge...
RangaHi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_9sab.asp
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_2ak3.asp
Filegroups use a proportional fill strategy across all the files within each
filegroup. If there are multiple files in a filegroup, they do not autogrow
until all the files are full. The file to grow is then chosen using a
round-robin algorithm.
John
"Ranga" wrote:
> Win2k3, SQL 2000, SP4
> If we have multiple datafiles in a filegroup, lets say 8,
> How does SQL distribute data ?(Will all data for a single table will go in
> one file or will it be spread)
> When there is need for space are all files auto grown uniformely OR just one
> file ?
> If just one file, will the same file auto grow next time too ?
> Sorry to put too many questions ? Just trying to understand the Auto grow
> feature of SQL.
> Advance thanks for sharing the knowledge...
> Ranga
AutoGrowth - Which data file does SQL Prefers to auto grow & why ?
Win2k3, SQL 2000, SP4
If we have multiple datafiles in a filegroup, lets say 8,
How does SQL distribute data ?(Will all data for a single table will go in
one file or will it be spread)
When there is need for space are all files auto grown uniformely OR just one
file ?
If just one file, will the same file auto grow next time too ?
Sorry to put too many questions ? Just trying to understand the Auto grow
feature of SQL.
Advance thanks for sharing the knowledge...
Ranga
Hi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_9sab.asp
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_2ak3.asp
Filegroups use a proportional fill strategy across all the files within each
filegroup. If there are multiple files in a filegroup, they do not autogrow
until all the files are full. The file to grow is then chosen using a
round-robin algorithm.
John
"Ranga" wrote:
> Win2k3, SQL 2000, SP4
> If we have multiple datafiles in a filegroup, lets say 8,
> How does SQL distribute data ?(Will all data for a single table will go in
> one file or will it be spread)
> When there is need for space are all files auto grown uniformely OR just one
> file ?
> If just one file, will the same file auto grow next time too ?
> Sorry to put too many questions ? Just trying to understand the Auto grow
> feature of SQL.
> Advance thanks for sharing the knowledge...
> Ranga
If we have multiple datafiles in a filegroup, lets say 8,
How does SQL distribute data ?(Will all data for a single table will go in
one file or will it be spread)
When there is need for space are all files auto grown uniformely OR just one
file ?
If just one file, will the same file auto grow next time too ?
Sorry to put too many questions ? Just trying to understand the Auto grow
feature of SQL.
Advance thanks for sharing the knowledge...
Ranga
Hi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_9sab.asp
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_2ak3.asp
Filegroups use a proportional fill strategy across all the files within each
filegroup. If there are multiple files in a filegroup, they do not autogrow
until all the files are full. The file to grow is then chosen using a
round-robin algorithm.
John
"Ranga" wrote:
> Win2k3, SQL 2000, SP4
> If we have multiple datafiles in a filegroup, lets say 8,
> How does SQL distribute data ?(Will all data for a single table will go in
> one file or will it be spread)
> When there is need for space are all files auto grown uniformely OR just one
> file ?
> If just one file, will the same file auto grow next time too ?
> Sorry to put too many questions ? Just trying to understand the Auto grow
> feature of SQL.
> Advance thanks for sharing the knowledge...
> Ranga
Friday, February 24, 2012
AutoGrowth - Which data file does SQL Prefers to auto grow & why ?
Win2k3, SQL 2000, SP4
If we have multiple datafiles in a filegroup, lets say 8,
How does SQL distribute data ?(Will all data for a single table will go in
one file or will it be spread)
When there is need for space are all files auto grown uniformely OR just one
file ?
If just one file, will the same file auto grow next time too ?
Sorry to put too many questions ? Just trying to understand the Auto grow
feature of SQL.
Advance thanks for sharing the knowledge...
RangaHi
Check out
http://msdn.microsoft.com/library/d...r />
_9sab.asp
and
http://msdn.microsoft.com/library/d...>
_02_2ak3.asp
Filegroups use a proportional fill strategy across all the files within each
filegroup. If there are multiple files in a filegroup, they do not autogrow
until all the files are full. The file to grow is then chosen using a
round-robin algorithm.
John
"Ranga" wrote:
> Win2k3, SQL 2000, SP4
> If we have multiple datafiles in a filegroup, lets say 8,
> How does SQL distribute data ?(Will all data for a single table will go in
> one file or will it be spread)
> When there is need for space are all files auto grown uniformely OR just o
ne
> file ?
> If just one file, will the same file auto grow next time too ?
> Sorry to put too many questions ? Just trying to understand the Auto grow
> feature of SQL.
> Advance thanks for sharing the knowledge...
> Ranga
If we have multiple datafiles in a filegroup, lets say 8,
How does SQL distribute data ?(Will all data for a single table will go in
one file or will it be spread)
When there is need for space are all files auto grown uniformely OR just one
file ?
If just one file, will the same file auto grow next time too ?
Sorry to put too many questions ? Just trying to understand the Auto grow
feature of SQL.
Advance thanks for sharing the knowledge...
RangaHi
Check out
http://msdn.microsoft.com/library/d...r />
_9sab.asp
and
http://msdn.microsoft.com/library/d...>
_02_2ak3.asp
Filegroups use a proportional fill strategy across all the files within each
filegroup. If there are multiple files in a filegroup, they do not autogrow
until all the files are full. The file to grow is then chosen using a
round-robin algorithm.
John
"Ranga" wrote:
> Win2k3, SQL 2000, SP4
> If we have multiple datafiles in a filegroup, lets say 8,
> How does SQL distribute data ?(Will all data for a single table will go in
> one file or will it be spread)
> When there is need for space are all files auto grown uniformely OR just o
ne
> file ?
> If just one file, will the same file auto grow next time too ?
> Sorry to put too many questions ? Just trying to understand the Auto grow
> feature of SQL.
> Advance thanks for sharing the knowledge...
> Ranga
Autogenerate reply or ServerProc generated message............
Hello,
I got my sample application to work that I am building my proof of concept out of. I need to be able to auto generate a reply message that would normally be in the run routine. The only way I see to do this is pull it from a table but I do not want to do that. I have tried tests where I change the code to see if I can send a message back but I have to reinstall the assembly into the database which is not what I am looking for. I am looking for a way to change the message by either accessing the GUI and getting the string, calling another function to do this, or something like that. I want it so I can change the code in the run routine in VS 2005 but this does not work. I am sure their is a trick to do this but am not sure what that trick is. What is a good way to do this other than accessing a table in the database?
Thanks,
Scott Allison...
What do you mean by auto-generate a reply? A reply could either be static (i.e. independent of the request and state), a stateless (i.e. dependent only on the request) or stateful (i.e. dependent on both the request as well as some state... the state would normally be stored in the database, but may also be stored separately). I cannot see why you need to recompile and redeploy your app to meet any of the above patterns.
Rushi
|||Hello Rushi,
It calls the ServiceProc in my code and that is loaded into the database. I am writing this for applications that will need to report the status of a current event. If this is not stored in the database then they would not want to write anything to the database. This means they will get the request then need to reply to that request with a text message that they generate. In the worst case situation it can come from the database but would rather just be retrieved from a function call. It seems as though from your message this can be done so I am sure I have a setting incorrect. What I have is currently static and I want the response dependent on the request. What I meant from Autogenerated is retrieved from another assembly or another part of that service.
Thanks,
Scott Allison...
|||If the function that generates a response from request is a method in a different assembly, you could deploy that assembly into the database and simply invoke the method from ServiceProc.|||Hello Rushi,
This is a remote system and will not be able to deploy all of the assemblies to the database. It consists of several services and needs dynamic access to what the message. Are you telling me that the only way to get a dynamic message is to write it to a database and have the ServiceProc read it? This is a draw back beacuse it will slow things down but I want to make sure before I tell people that they have to do that.
Thanks,
Scott Allison...
Autofill
Hi, Is there any way to copy functions from one textbox to another with auto
fill? There are cases which the programmer spends enormous time over formulas
just to change textbox names into the same formulas.>>There are cases which the programmer spends enormous time over formulas
just to change textbox names into the same formulas.
View the RDL as code (XML) and do a Find and Replace?
>L<
"Kostas Bourikas" <KostasBourikas@.discussions.microsoft.com> wrote in
message news:0B5E6D85-36BF-4E2C-994B-B5C49487A44A@.microsoft.com...
> Hi, Is there any way to copy functions from one textbox to another with
> auto
> fill? There are cases which the programmer spends enormous time over
> formulas
> just to change textbox names into the same formulas.|||Thank you but this still doesn't solve my problem...
What is the difference between imputing textbox values into visual studio or
the same into xml code (find/replace)... The effort is still remains the
same.
Microsoft should seriously consider an autofill (excel-like) option, in
future updates.
Actually surprises me that they haven't released a patch for this issue
yet... Iâ'm representing a huge amount of programmers that share my opinion.
"Lisa Slater Nicholls" wrote:
> >>There are cases which the programmer spends enormous time over formulas
> just to change textbox names into the same formulas.
> View the RDL as code (XML) and do a Find and Replace?
> >L<
> "Kostas Bourikas" <KostasBourikas@.discussions.microsoft.com> wrote in
> message news:0B5E6D85-36BF-4E2C-994B-B5C49487A44A@.microsoft.com...
> > Hi, Is there any way to copy functions from one textbox to another with
> > auto
> > fill? There are cases which the programmer spends enormous time over
> > formulas
> > just to change textbox names into the same formulas.
>|||>>
> What is the difference between imputing textbox values into visual studio
> or
> the same into xml code (find/replace)... The effort is still remains the
> same.
<<
The effort is *not* the same because you can do a Replace All in
the XML, whereas you have to do the textboxes one at a time <shrug>.
You may not want to do this but you asked me what the difference is, so
that's what it is. Look: I was trying to help you. If you don't like the
idea, that's okay.
>> Iâ'm representing a huge amount of programmers that share my opinion.
That's nice. Maybe somebody else reading the thread will like my
suggestion, then ! <s>
FWIW... I have been writing a design-time utility for the Report
Designer. It's nothing very fancy. It's just what I do instead of waiting
for Microsoft to do something.
The premise of my utility is different from what you need, but could
potentially
be extended to what you are asking for. Again, not very fancily, and
probably
*not* to everybody's satisfaction.
Here's my point:
In writing this, and in writing other utilities for reporting environments,
I
have learned that it's quite difficult to do this type of thing and meet
everybody's expectations. In your particular case, the utility would have
to be very specific about what it decided to change (especially in custom
expressions) so that it worked in all cases without harming anybody's
report in a way that they might not realize until after they had saved the
changes. This is likely the reason why MS would have hesitated to do it at
this stage in the product's life.
I don't, personally, believe this is a "bug" requiring a "patch".
It is an underimplementation, certainly. I see lots of those in this
product.
But this particular underimplementation, no matter how much it bothers you,
should not be addressed without a great deal of thought IMHO, so it will
likely take some time to do correctly. I would hope that is of concern to
you --
because a botched implementation would make you a lot unhappier than
you are right now <s>.
Please don't shoot the messenger. And please realize that I am representing
*nobody* and asserting *nothing* about what body of programmers share my
opinions. This is just what I think.
>L<
"Kostas Bourikas" <KostasBourikas@.discussions.microsoft.com> wrote in
message news:00725C4D-A1E6-44DC-B5E7-8DA741918B96@.microsoft.com...
> Thank you but this still doesn't solve my problem...
> What is the difference between imputing textbox values into visual studio
> or
> the same into xml code (find/replace)... The effort is still remains the
> same.
> Microsoft should seriously consider an autofill (excel-like) option, in
> future updates.
> Actually surprises me that they haven't released a patch for this issue
> yet... Iâ'm representing a huge amount of programmers that share my
> opinion.
>
> "Lisa Slater Nicholls" wrote:
>> >>There are cases which the programmer spends enormous time over formulas
>> just to change textbox names into the same formulas.
>> View the RDL as code (XML) and do a Find and Replace?
>> >L<
>> "Kostas Bourikas" <KostasBourikas@.discussions.microsoft.com> wrote in
>> message news:0B5E6D85-36BF-4E2C-994B-B5C49487A44A@.microsoft.com...
>> > Hi, Is there any way to copy functions from one textbox to another with
>> > auto
>> > fill? There are cases which the programmer spends enormous time over
>> > formulas
>> > just to change textbox names into the same formulas.
>>
fill? There are cases which the programmer spends enormous time over formulas
just to change textbox names into the same formulas.>>There are cases which the programmer spends enormous time over formulas
just to change textbox names into the same formulas.
View the RDL as code (XML) and do a Find and Replace?
>L<
"Kostas Bourikas" <KostasBourikas@.discussions.microsoft.com> wrote in
message news:0B5E6D85-36BF-4E2C-994B-B5C49487A44A@.microsoft.com...
> Hi, Is there any way to copy functions from one textbox to another with
> auto
> fill? There are cases which the programmer spends enormous time over
> formulas
> just to change textbox names into the same formulas.|||Thank you but this still doesn't solve my problem...
What is the difference between imputing textbox values into visual studio or
the same into xml code (find/replace)... The effort is still remains the
same.
Microsoft should seriously consider an autofill (excel-like) option, in
future updates.
Actually surprises me that they haven't released a patch for this issue
yet... Iâ'm representing a huge amount of programmers that share my opinion.
"Lisa Slater Nicholls" wrote:
> >>There are cases which the programmer spends enormous time over formulas
> just to change textbox names into the same formulas.
> View the RDL as code (XML) and do a Find and Replace?
> >L<
> "Kostas Bourikas" <KostasBourikas@.discussions.microsoft.com> wrote in
> message news:0B5E6D85-36BF-4E2C-994B-B5C49487A44A@.microsoft.com...
> > Hi, Is there any way to copy functions from one textbox to another with
> > auto
> > fill? There are cases which the programmer spends enormous time over
> > formulas
> > just to change textbox names into the same formulas.
>|||>>
> What is the difference between imputing textbox values into visual studio
> or
> the same into xml code (find/replace)... The effort is still remains the
> same.
<<
The effort is *not* the same because you can do a Replace All in
the XML, whereas you have to do the textboxes one at a time <shrug>.
You may not want to do this but you asked me what the difference is, so
that's what it is. Look: I was trying to help you. If you don't like the
idea, that's okay.
>> Iâ'm representing a huge amount of programmers that share my opinion.
That's nice. Maybe somebody else reading the thread will like my
suggestion, then ! <s>
FWIW... I have been writing a design-time utility for the Report
Designer. It's nothing very fancy. It's just what I do instead of waiting
for Microsoft to do something.
The premise of my utility is different from what you need, but could
potentially
be extended to what you are asking for. Again, not very fancily, and
probably
*not* to everybody's satisfaction.
Here's my point:
In writing this, and in writing other utilities for reporting environments,
I
have learned that it's quite difficult to do this type of thing and meet
everybody's expectations. In your particular case, the utility would have
to be very specific about what it decided to change (especially in custom
expressions) so that it worked in all cases without harming anybody's
report in a way that they might not realize until after they had saved the
changes. This is likely the reason why MS would have hesitated to do it at
this stage in the product's life.
I don't, personally, believe this is a "bug" requiring a "patch".
It is an underimplementation, certainly. I see lots of those in this
product.
But this particular underimplementation, no matter how much it bothers you,
should not be addressed without a great deal of thought IMHO, so it will
likely take some time to do correctly. I would hope that is of concern to
you --
because a botched implementation would make you a lot unhappier than
you are right now <s>.
Please don't shoot the messenger. And please realize that I am representing
*nobody* and asserting *nothing* about what body of programmers share my
opinions. This is just what I think.
>L<
"Kostas Bourikas" <KostasBourikas@.discussions.microsoft.com> wrote in
message news:00725C4D-A1E6-44DC-B5E7-8DA741918B96@.microsoft.com...
> Thank you but this still doesn't solve my problem...
> What is the difference between imputing textbox values into visual studio
> or
> the same into xml code (find/replace)... The effort is still remains the
> same.
> Microsoft should seriously consider an autofill (excel-like) option, in
> future updates.
> Actually surprises me that they haven't released a patch for this issue
> yet... Iâ'm representing a huge amount of programmers that share my
> opinion.
>
> "Lisa Slater Nicholls" wrote:
>> >>There are cases which the programmer spends enormous time over formulas
>> just to change textbox names into the same formulas.
>> View the RDL as code (XML) and do a Find and Replace?
>> >L<
>> "Kostas Bourikas" <KostasBourikas@.discussions.microsoft.com> wrote in
>> message news:0B5E6D85-36BF-4E2C-994B-B5C49487A44A@.microsoft.com...
>> > Hi, Is there any way to copy functions from one textbox to another with
>> > auto
>> > fill? There are cases which the programmer spends enormous time over
>> > formulas
>> > just to change textbox names into the same formulas.
>>
Sunday, February 19, 2012
Auto_Increment?
Is there a way to add a column in my sql server db that is ging to auto increment? I would like to start at 000001 and work right on up. I can't see to find a way to do this. Thanks.Yes. Set that column as the "Identity column" for the table.|||you're the man!!! without having access to my DB now, is there a way to set the mask so I can have the leading zeros I would like?|||The identity column has to be of type int, so there is no such thing as "leading zeros", you can only set the initial value and the step. You should format the number in your application the way you want, or if you really insist on doing it in the DB, then do a view.|||
smalltalk:
The identity column has to be of type int, so there is no such thing as "leading zeros", you can only set the initial value and the step. You should format the number in your application the way you want, or if you really insist on doing it in the DB, then do a view.
awesome. The reason for the leading zeros is just for formatting consistancey, It's a ui thing rather than a database thing so I can just slap some 0's on there after I get the value from the database
Auto_Increment?
Hi all.
I'm trying to transfer a mysql file to mssql. Is there a field type of auto increment in mssql.
If not is there anyway to make a field auto increment in the SQL Studio express?
Thanks in advance for help with this!
Ron
In Microsoft SQL, you set the column's "Identity Specification" equal to Yes
Auto URL Redirect
Hi,Does anyone know how to redirect the report to another URL automatically conditionally based on some checking, without user interaction with the report?I am trying to perform validity checking on user's input value and redirect user to a centralise error page if the input value is invalid.Thanks,J LimYou can't do this using the standard parameter toolbar. You will need to code your own textbox for accepting user input for the parameter value and pass that to the report if is valid and redirect to the error page if not valid.
Auto UpLoad
Is there a way to automate the upload of reports (.PDF's).
My DD Subscription produces .PDF files of the reports , I would like to
upload them automatically so the user can see the files via Report Manager.
Has anyone had to do thi yet?
Many thanks in advance
PaulIf I understand you correctly, (and I'm assuming some things), you have a
report(s) that take some time to generate and you want to generate them in
the background but enable the users to see them through the web-page without
the latency?
If this is correct, set up the history so that it creates the report at a
regular interval, then when someone requests the report, it will pull it from
the history.
"PaulQld" wrote:
> Is there a way to automate the upload of reports (.PDF's).
> My DD Subscription produces .PDF files of the reports , I would like to
> upload them automatically so the user can see the files via Report Manager.
> Has anyone had to do thi yet?
> Many thanks in advance
> Paul
My DD Subscription produces .PDF files of the reports , I would like to
upload them automatically so the user can see the files via Report Manager.
Has anyone had to do thi yet?
Many thanks in advance
PaulIf I understand you correctly, (and I'm assuming some things), you have a
report(s) that take some time to generate and you want to generate them in
the background but enable the users to see them through the web-page without
the latency?
If this is correct, set up the history so that it creates the report at a
regular interval, then when someone requests the report, it will pull it from
the history.
"PaulQld" wrote:
> Is there a way to automate the upload of reports (.PDF's).
> My DD Subscription produces .PDF files of the reports , I would like to
> upload them automatically so the user can see the files via Report Manager.
> Has anyone had to do thi yet?
> Many thanks in advance
> Paul
Auto Updates for SQL Server?
Our IT guy insists that he must be present anytime the SQL Server automatic updates from Microsoft are installed on the server or the server will crash. What has he been tinkering with to cause this to happen or is he just giving us a line?
Well, I wouldn′t apply all patches to SQL Server without any notice ot testing. Normally the updates or patches don′t break the functionality of SQL Server. To be sure, I would test the patch on a testing server. If you have a WUS, you can setup the server to confirm the patch first before applying the patch on the SQL Server.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||I guess I needed to be a little more specific. He is referring to the auto updates which come from Microsoft (just like on a personal PC). He evidently has the server set up so that the updates sit there until he decides to install them (like the option on a personal PC) which isn't really the problem but then he has turned around and is saying that when he does install the updates (rather than anyone with knowledge to do so) he absolutely has to be there or the server will crash. What could he have possibly done which would make the server crash if he doesn't personally oversee the update process?|||
hi,
this sounds kind "confusing"...
does he perform particular steps in order to avoid "system crashes"?
regards
|||I don't have any idea. We're assuming he's just feeding us a line but he has an unusual family history (which has us particularly concerned at the moment). Believe me this is not a joke. We are not a Dept. of Defense facility or anything like that but we are just a small business where the owner hired him because of his computer knowledge (but does know his background). Now the owner is concerned this is coming back to bite him if we can't figure out why he needs to be there when any updates are installed. We are very worried that he has planted some type of code to bring the business to its knees. Other than a disaster recovery plan which I am already looking into, what other ideas would anyone suggest. I don't want to get anymore specific than what I have since I don't know whether he reads these forums or not.|||I'm not sure this is a productive discussion for this forum. No one here could (or should) offer any suggestiona as to what your IT guy might or might not do to a computer to make it crash. If you have significant concerns about one of your employees, you need to take it up with your boss. If you feel it is a real possibility that your IT guy might do something maliceous to your server, you should have a qualified computer technician take a look at it.
Regards,
Mike Wachal
SQL Express team
FWIW: There are no patches for SQL Express available through Microsoft Update at this point, you have to download and apply them manually.
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
Subscribe to:
Posts (Atom)