Autostatistic ON/OFF on separate tables?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GG - 01 Jul 2009 13:53 GMT

Hi

Is't possible to set Auto updatestatistic ON and OFF on separates tables or
is't only on database level?
Regards
GG

Reply to this Message

Russell Fields - 01 Jul 2009 14:12 GMT

GG,

AUTO_UPDATE_STATISTICS is a database setting, so you cannot set it per
table. If you want that fine a control, you could turn it off.

ALTER DATABASE xxx SET AUTO_UPDATE_STATISTICS OFF;

Then you can run UPDATE STATISTICS on the tables and views that you wish to
update.

RLF

> Hi
>
> Is't possible to set Auto updatestatistic ON and OFF on separates tables
> or is't only on database level?
> Regards
> GG

Reply to this Message

Tibor Karaszi - 01 Jul 2009 17:12 GMT

You can have it on at the database level and turn it off for selected
tables (UPDATE STATISTICS ... WITH NORECOMPUTE), sp_autostats etc.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Hi
>
> Is't possible to set Auto updatestatistic ON and OFF on separates
> tables or is't only on database level?
> Regards
> GG

Reply to this Message

GG - 02 Jul 2009 09:38 GMT

Thanks for your answer.

What I'm thinking of is that we have an database 300Gb and two of these
tables has about 60 milj records each.
To day the Autostat is turned off and a job is executing every night but
these two tables takes extremly long time.

So my thought is that turn on autostat for all except the two biggest and
only do update stat with the night job

Regards
GG

> You can have it on at the database level and turn it off for selected
> tables (UPDATE STATISTICS ... WITH NORECOMPUTE), sp_autostats etc.
[quoted text clipped - 5 lines]
>> Regards
>> GG

Reply to this Message

Tibor Karaszi - 02 Jul 2009 16:58 GMT

That is exactly what SQL Server let you achieve. Turn on autostats for
the database and turn it off for those two tables.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Thanks for your answer.
>
[quoted text clipped - 19 lines]
>>> Regards
>>> GG

Reply to this Message

Erland Sommarskog - 02 Jul 2009 23:12 GMT

> What I'm thinking of is that we have an database 300Gb and two of these
> tables has about 60 milj records each.
> To day the Autostat is turned off and a job is executing every night but
> these two tables takes extremly long time.

Does in run UPDATE STATISTICS WITH FULLSCAN? If you change it to
UPDATE STATISTICS WITH FULLSCAN, INDEX things go faster. Then again,
if you rebuild indexes, there is no reason to update index statistics
as well.

> So my thought is that turn on autostat for all except the two biggest and
> only do update stat with the night job

That is achievable, but whether it is the right action, I am less sure.
Autostats sets in on a table when 500 rows have been added, and then when
20% of the rows have been modified. (This is a somewhat loose description.)
This means that autostats do not set in very often on these tables.

In the system I work with, I've contemplating turning off autostats on
tables with a fairly low number of rows, but where rows get added and
deleted all the time, causing autostats to set in often with little
use, but causing recompiles.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

GG - 03 Jul 2009 07:37 GMT

Thank's for your tips Erland, I will look over this alternetiv

Regards
GG

>> What I'm thinking of is that we have an database 300Gb and two of these
>> tables has about 60 milj records each.
[quoted text clipped - 19 lines]
> deleted all the time, causing autostats to set in often with little
> use, but causing recompiles.

Reply to this Message

Erland Sommarskog - 01 Jul 2009 23:35 GMT

> Is't possible to set Auto updatestatistic ON and OFF on separates tables
> or is't only on database level?

Yes, look at sp_autostats in Books Online.

Signature

Reply to this Message