Auto Create Statistics

We have accidentally had the "Auto Create Statistics" turned set to true on
one of our production databases. When we told the group responsible for the
application for this database, they said they have been having performance
issues and are enquiring if having this setting turned on could be the cause
of that.
From BOL, with automatic statistics, the statistics are updated periodically
as the data in the table changes. A statistics update occurs whenever the
statistics in a query execution fail a test for the current statistics.
Statistical information is updated whenever approximately 20 percent of the
data rows have been changed.
Is it possible that this overhead could have an affect on database
performance?
Also, they asked if we could give then a report for displaying all the
statistics. I know the "DBBC SHOW_STATISTICS" command will display the
statistics if you supply the table and index parameters. The database is
quite large with many tables and indexes. I could probably write a script
using cursors but I am wondering if there is any other way for generating a
report for all of the statistics for the database.Hi Loren
This setting is ON by default on all databases, and it is usually a good
idea to leave it on. In addition, this setting is not the same as AUTO
UPDATE STATISTICS. Since this was most likely on since the time the db was
created, it's unlikely that it is the cause for any recent performance
problems.
AUTO CREATE STATISTICS tells SQL Server to create stats on unindexed
columns. AUTO UPDATE STATISTICS tells SQL Server to update index statistics
when they get stale.
Actually looking at statistics is rarely needed, and most people do it only
when troubleshooting one particular query that is misbehaving. Why do they
want a report for ALL the statistics when they are subject to frequent
change? By the time they got through looking at the whole report, the values
would probably have changed.
I would think the DBCC SHOW_STATISTICS would be good enough, when they need
to look at particular statistics for a particular table or index.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:e4gI9NSBIHA.1168@.TK2MSFTNGP02.phx.gbl...
> We have accidentally had the "Auto Create Statistics" turned set to true
> on one of our production databases. When we told the group responsible for
> the application for this database, they said they have been having
> performance issues and are enquiring if having this setting turned on
> could be the cause of that.
> From BOL, with automatic statistics, the statistics are updated
> periodically as the data in the table changes. A statistics update occurs
> whenever the statistics in a query execution fail a test for the current
> statistics. Statistical information is updated whenever approximately 20
> percent of the data rows have been changed.
> Is it possible that this overhead could have an affect on database
> performance?
> Also, they asked if we could give then a report for displaying all the
> statistics. I know the "DBBC SHOW_STATISTICS" command will display the
> statistics if you supply the table and index parameters. The database is
> quite large with many tables and indexes. I could probably write a script
> using cursors but I am wondering if there is any other way for generating
> a report for all of the statistics for the database.
>|||As far as I know, this post was asking about Auto CREATE Statistics, not
AUTO UPDATE.
Yes, AUTO UPDATE does have a cost, but in most cases, updating stats and
recompiling has far less of a cost than the costs of using a bad plan
because your statistics are out of date. SQL 2005 also introduced AUTO
UPDATE STATISTICS ASYNC, so that the stats will be automatically updated,
but it will not impact the query that triggered the update, i.e. that query
will not have to wait for the update and the subsequent recompile.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:4AF1D2DE-60B6-4C24-9F60-3BA208A222F1@.microsoft.com...
> Hello Kalen!
>
> As far as I know Auto Update Statistics cause changing Query Plans and
> causes SPs to be recompiled so this makes a performance problem. I know
> that if statistics would be ouf of date, then it's another problem. This
> seems kind of dilemma.
> I would be happy to hear your comments about this.
>
> --
> Ekrem Önsoy
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:ekdDiSSBIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi Loren
>> This setting is ON by default on all databases, and it is usually a good
>> idea to leave it on. In addition, this setting is not the same as AUTO
>> UPDATE STATISTICS. Since this was most likely on since the time the db
>> was created, it's unlikely that it is the cause for any recent
>> performance problems.
>> AUTO CREATE STATISTICS tells SQL Server to create stats on unindexed
>> columns. AUTO UPDATE STATISTICS tells SQL Server to update index
>> statistics when they get stale.
>> Actually looking at statistics is rarely needed, and most people do it
>> only when troubleshooting one particular query that is misbehaving. Why
>> do they want a report for ALL the statistics when they are subject to
>> frequent change? By the time they got through looking at the whole
>> report, the values would probably have changed.
>> I would think the DBCC SHOW_STATISTICS would be good enough, when they
>> need to look at particular statistics for a particular table or index.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
>> news:e4gI9NSBIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> We have accidentally had the "Auto Create Statistics" turned set to true
>> on one of our production databases. When we told the group responsible
>> for the application for this database, they said they have been having
>> performance issues and are enquiring if having this setting turned on
>> could be the cause of that.
>> From BOL, with automatic statistics, the statistics are updated
>> periodically as the data in the table changes. A statistics update
>> occurs whenever the statistics in a query execution fail a test for the
>> current statistics. Statistical information is updated whenever
>> approximately 20 percent of the data rows have been changed.
>> Is it possible that this overhead could have an affect on database
>> performance?
>> Also, they asked if we could give then a report for displaying all the
>> statistics. I know the "DBBC SHOW_STATISTICS" command will display the
>> statistics if you supply the table and index parameters. The database is
>> quite large with many tables and indexes. I could probably write a
>> script using cursors but I am wondering if there is any other way for
>> generating a report for all of the statistics for the database.
>>
>|||Hello Kalen!
As far as I know Auto Update Statistics cause changing Query Plans and
causes SPs to be recompiled so this makes a performance problem. I know that
if statistics would be ouf of date, then it's another problem. This seems
kind of dilemma.
I would be happy to hear your comments about this.
Ekrem Önsoy
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ekdDiSSBIHA.536@.TK2MSFTNGP06.phx.gbl...
> Hi Loren
> This setting is ON by default on all databases, and it is usually a good
> idea to leave it on. In addition, this setting is not the same as AUTO
> UPDATE STATISTICS. Since this was most likely on since the time the db was
> created, it's unlikely that it is the cause for any recent performance
> problems.
> AUTO CREATE STATISTICS tells SQL Server to create stats on unindexed
> columns. AUTO UPDATE STATISTICS tells SQL Server to update index
> statistics when they get stale.
> Actually looking at statistics is rarely needed, and most people do it
> only when troubleshooting one particular query that is misbehaving. Why do
> they want a report for ALL the statistics when they are subject to
> frequent change? By the time they got through looking at the whole report,
> the values would probably have changed.
> I would think the DBCC SHOW_STATISTICS would be good enough, when they
> need to look at particular statistics for a particular table or index.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:e4gI9NSBIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> We have accidentally had the "Auto Create Statistics" turned set to true
>> on one of our production databases. When we told the group responsible
>> for the application for this database, they said they have been having
>> performance issues and are enquiring if having this setting turned on
>> could be the cause of that.
>> From BOL, with automatic statistics, the statistics are updated
>> periodically as the data in the table changes. A statistics update occurs
>> whenever the statistics in a query execution fail a test for the current
>> statistics. Statistical information is updated whenever approximately 20
>> percent of the data rows have been changed.
>> Is it possible that this overhead could have an affect on database
>> performance?
>> Also, they asked if we could give then a report for displaying all the
>> statistics. I know the "DBBC SHOW_STATISTICS" command will display the
>> statistics if you supply the table and index parameters. The database is
>> quite large with many tables and indexes. I could probably write a script
>> using cursors but I am wondering if there is any other way for generating
>> a report for all of the statistics for the database.
>|||Yes, I know that post was about CREATE STATISTICS. However, when you
mentioned Update Statistics, I wanted to have your comments about this.
Thanks for sharing your thoughts about this.
--
Ekrem Önsoy
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O1gT8wSBIHA.4656@.TK2MSFTNGP04.phx.gbl...
> As far as I know, this post was asking about Auto CREATE Statistics, not
> AUTO UPDATE.
> Yes, AUTO UPDATE does have a cost, but in most cases, updating stats and
> recompiling has far less of a cost than the costs of using a bad plan
> because your statistics are out of date. SQL 2005 also introduced AUTO
> UPDATE STATISTICS ASYNC, so that the stats will be automatically updated,
> but it will not impact the query that triggered the update, i.e. that
> query will not have to wait for the update and the subsequent recompile.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:4AF1D2DE-60B6-4C24-9F60-3BA208A222F1@.microsoft.com...
>> Hello Kalen!
>>
>> As far as I know Auto Update Statistics cause changing Query Plans and
>> causes SPs to be recompiled so this makes a performance problem. I know
>> that if statistics would be ouf of date, then it's another problem. This
>> seems kind of dilemma.
>> I would be happy to hear your comments about this.
>>
>> --
>> Ekrem Önsoy
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:ekdDiSSBIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi Loren
>> This setting is ON by default on all databases, and it is usually a good
>> idea to leave it on. In addition, this setting is not the same as AUTO
>> UPDATE STATISTICS. Since this was most likely on since the time the db
>> was created, it's unlikely that it is the cause for any recent
>> performance problems.
>> AUTO CREATE STATISTICS tells SQL Server to create stats on unindexed
>> columns. AUTO UPDATE STATISTICS tells SQL Server to update index
>> statistics when they get stale.
>> Actually looking at statistics is rarely needed, and most people do it
>> only when troubleshooting one particular query that is misbehaving. Why
>> do they want a report for ALL the statistics when they are subject to
>> frequent change? By the time they got through looking at the whole
>> report, the values would probably have changed.
>> I would think the DBCC SHOW_STATISTICS would be good enough, when they
>> need to look at particular statistics for a particular table or index.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
>> news:e4gI9NSBIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> We have accidentally had the "Auto Create Statistics" turned set to
>> true on one of our production databases. When we told the group
>> responsible for the application for this database, they said they have
>> been having performance issues and are enquiring if having this setting
>> turned on could be the cause of that.
>> From BOL, with automatic statistics, the statistics are updated
>> periodically as the data in the table changes. A statistics update
>> occurs whenever the statistics in a query execution fail a test for the
>> current statistics. Statistical information is updated whenever
>> approximately 20 percent of the data rows have been changed.
>> Is it possible that this overhead could have an affect on database
>> performance?
>> Also, they asked if we could give then a report for displaying all the
>> statistics. I know the "DBBC SHOW_STATISTICS" command will display the
>> statistics if you supply the table and index parameters. The database
>> is quite large with many tables and indexes. I could probably write a
>> script using cursors but I am wondering if there is any other way for
>> generating a report for all of the statistics for the database.
>>
>>
>