Auto update statistics

We have 'auto update statistics' turned on for all our databases.
We also have a job that updates statistics with full scan every morning of
every day.
I am seeing some high cpu on one of our servers and in running Profiler I see
a number of "SELECT StatMan([SCO])..." statements.
From what I understand this could be due to procedures recompiling due to a
temp table, or auto update statistics.
If by the chance it is due to having 'auto update statistics' turned on, and
I am updating statistics daily, can I do away with one or the other? Is doing
both rather over kill?
--
Message posted via http://www.sqlmonster.comIt is 'probably' ok to turn off autoupdate in this case. One situation
where it isn't is tables that are heavily modified - you could have queries
developing bad query plans after sufficient modifications altered the value
distributions. If this is the case you could simply enable specific tables
using the sp_autostats sproc.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:798b0fc1296d4@.uwe...
> We have 'auto update statistics' turned on for all our databases.
> We also have a job that updates statistics with full scan every morning of
> every day.
> I am seeing some high cpu on one of our servers and in running Profiler I
> see
> a number of "SELECT StatMan([SCO])..." statements.
> From what I understand this could be due to procedures recompiling due to
> a
> temp table, or auto update statistics.
> If by the chance it is due to having 'auto update statistics' turned on,
> and
> I am updating statistics daily, can I do away with one or the other? Is
> doing
> both rather over kill?
> --
> Message posted via http://www.sqlmonster.com
>|||Hi there,
There's a new database SET option, AUTO_UPDATE_STATISTICS_ASYNC, that you
can enable to improve the predictability of query response times. When you
enable this option, out-of-date statistics are put on a queue for updating by
a background worker thread, and the query that initiated the statistics
update compiles immediately rather than waiting for the statistics to be
updated.
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
Register (Free):
http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
"TheSQLGuru" wrote:
> It is 'probably' ok to turn off autoupdate in this case. One situation
> where it isn't is tables that are heavily modified - you could have queries
> developing bad query plans after sufficient modifications altered the value
> distributions. If this is the case you could simply enable specific tables
> using the sp_autostats sproc.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
> news:798b0fc1296d4@.uwe...
> > We have 'auto update statistics' turned on for all our databases.
> >
> > We also have a job that updates statistics with full scan every morning of
> > every day.
> >
> > I am seeing some high cpu on one of our servers and in running Profiler I
> > see
> > a number of "SELECT StatMan([SCO])..." statements.
> >
> > From what I understand this could be due to procedures recompiling due to
> > a
> > temp table, or auto update statistics.
> >
> > If by the chance it is due to having 'auto update statistics' turned on,
> > and
> > I am updating statistics daily, can I do away with one or the other? Is
> > doing
> > both rather over kill?
> >
> > --
> > Message posted via http://www.sqlmonster.com
> >
>
>|||The auto update kicks in when a certain thresshold of changes has
occurred since the last time the statistics were determined. So if you
update statistics every morning, then the auto update statistics will
not activate unless it thinks it is necessary. This could be on heavily
used tables, or temp tables.
IOW, I would keep it turned on. It also prevents problems if you missed
a table in your daily job, or if (for some reason) the job hasn't run
(completely).
--
Gert-Jan
"cbrichards via SQLMonster.com" wrote:
> We have 'auto update statistics' turned on for all our databases.
> We also have a job that updates statistics with full scan every morning of
> every day.
> I am seeing some high cpu on one of our servers and in running Profiler I see
> a number of "SELECT StatMan([SCO])..." statements.
> From what I understand this could be due to procedures recompiling due to a
> temp table, or auto update statistics.
> If by the chance it is due to having 'auto update statistics' turned on, and
> I am updating statistics daily, can I do away with one or the other? Is doing
> both rather over kill?
> --
> Message posted via http://www.sqlmonster.com