Re: Autostatistic ON/OFF on separate tables?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


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

Regards
GG

"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message news:Xns9C3D21BB3CF8Yazorman@xxxxxxxxxxxx
GG (gg@xxxxxxxxxxxxx) writes:
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.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

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


.

Relevant Pages

  • Dynamic Query
    ... speciality1 = MIN, ... This is the standard way of running a pivot in SQL Server. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.programming)
  • RE: Linked server data length issue
    ... Here is the code I'm using: select * from Openquery (Linked Server, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 2000 SP4 fails to install
    ... core CPU x64 processors ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.setup)
  • Re: Filtering a voter?
    ... "Erland Sommarskog" wrote: ... >> I'm a complete novice with SQl, I'm completely self taught I have not ... you don't have to include the entire database. ... > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se ...
    (microsoft.public.sqlserver.programming)
  • Re: 15408 Revert Session Security Context"
    ... SqlBinary denyValue, SqlString userGroups, SqlBoolean mergeRights) ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.security)