Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

update stats and cluster ratio

2 posts in General Discussion Last posting was on 2009-07-30 17:03:43.0Z
vtpcnk Posted on 2009-07-23 10:42:18.0Z
Sender: 4ce9.4a66e8f1.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: update stats and cluster ratio
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a683e8a.4f4a.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 23 Jul 2009 03:42:18 -0700
X-Trace: forums-3-dub.sybase.com 1248345738 10.22.241.188 (23 Jul 2009 03:42:18 -0700)
X-Original-Trace: 23 Jul 2009 03:42:18 -0700, forums-3-dub.sybase.com
Lines: 20
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28030
Article PK: 77276

where i work, update stats is run for tables whose cluster
ratio is poor. but is cluster ratio a sound basis for
determining whether a table needs update statistics to be
run for it?

cluster ratio is primarily about clustering of data pages
and index pages for better performance especially with large
i/o. update stats only updates information about cluster
ratio. but cannot really change the clustered ratio for a
table - for defragmenting a table and improving its cluster
ratio, you need to either bcp out/in or drop/recreate
cluster index or reorg rebuild on the table. and if you do
any of this and if it involves a clustered index anyway
update statistics will automatically be run for the table.
so a table with a poor clustered ratio is probably so
because its data is fragmented and so needs remedy which
update stats by itself cannot provide.

so is cluster ratio the right basis to decide whether a
table needs update stats to be run on it?


Sherlock, Kevin [TeamSybase] Posted on 2009-07-30 17:03:43.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4a683e8a.4f4a.1681692777@sybase.com>
Subject: Re: update stats and cluster ratio
Lines: 52
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a71d26f$1@forums-3-dub.sybase.com>
Date: 30 Jul 2009 10:03:43 -0700
X-Trace: forums-3-dub.sybase.com 1248973423 10.22.241.152 (30 Jul 2009 10:03:43 -0700)
X-Original-Trace: 30 Jul 2009 10:03:43 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28072
Article PK: 77321

To be exact, cluster ratios are a "derived" statistic. Some of the stats
that are involved in the derivation are kept up-to-date in memory and
periodically flushed to systabstats, and others are only updated when you
run update stats. So technically, cluster ratios change without running
update stats, but they are most accurate after a run of update stats.
That's because there are some parts of the formula to derive the cluster
ratio that are only updated during update stats (mostly, CR counts).

The standard answer to "when should I run update stats" is always: "when it
is necessary to improve performance".

Cluster ratios alone are NOT an indication about when to update statistics.
Skewing, inaccurate cluster ratios CAN/MIGHT cause bad performance (less
efficient query plans), but again, are NOT the only indicator that you
should be considering. Mostly, because over time they become innacurate,
and secondly, there are many other "statistics" that go into determining
efficient query plans other than just cluster ratios.

Depending on your version of ASE, you may want to look into using
"datachange()" function to determine when (or when NOT) to run update
statistics. There has been much conversation about this on the ISUG forums
as well. If you are a member, you may want to look at those threads for
more information.

Usually, it's been my experience that it's easiest to just settle on a
"regular maintenance" cycle approach to update stats. If you have a
sufficient maintenance window, include update [index] stats as part of your
regularly scheduled tasks.

<vtpcnk> wrote in message news:4a683e8a.4f4a.1681692777@sybase.com...
> where i work, update stats is run for tables whose cluster
> ratio is poor. but is cluster ratio a sound basis for
> determining whether a table needs update statistics to be
> run for it?
>
> cluster ratio is primarily about clustering of data pages
> and index pages for better performance especially with large
> i/o. update stats only updates information about cluster
> ratio. but cannot really change the clustered ratio for a
> table - for defragmenting a table and improving its cluster
> ratio, you need to either bcp out/in or drop/recreate
> cluster index or reorg rebuild on the table. and if you do
> any of this and if it involves a clustered index anyway
> update statistics will automatically be run for the table.
> so a table with a poor clustered ratio is probably so
> because its data is fragmented and so needs remedy which
> update stats by itself cannot provide.
>
> so is cluster ratio the right basis to decide whether a
> table needs update stats to be run on it?