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.

undestanding optdiag forwarded row count

7 posts in Performance and Tuning Last posting was on 2007-09-22 13:27:01.0Z
Isabella Posted on 2007-09-19 17:44:02.0Z
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: undestanding optdiag forwarded row count
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: fw.hia.nrc.ca
X-Original-NNTP-Posting-Host: fw.hia.nrc.ca
Message-ID: <46f15fe2$1@forums-1-dub>
Date: 19 Sep 2007 10:44:02 -0700
X-Trace: forums-1-dub 1190223842 204.174.103.3 (19 Sep 2007 10:44:02 -0700)
X-Original-Trace: 19 Sep 2007 10:44:02 -0700, fw.hia.nrc.ca
Lines: 104
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10348
Article PK: 88982

I have a 55 mill rows DOL partition table , only with non-clustered
indexes, below see my ASE vers ( 12.5.3-Linux ) and optidag output.
I 'm trying to understand and fix the negative values for 'Forwarded row
count' , I run the 'reorg forwarded_rows...' cmd to fix the values but
getting same results.
Any explanation what's going on....
thank you,
Isabella

Adaptive Server Enterprise/12.5.3/EBF 12600 ESD#3/P/Linux Intel/Enterprise
Linux/ase1253/1911/32-bit/OPT/Fri Jul 8 02:32:50 2005




Statistics for table: "mfs_files"

Data page count: 3988988
Empty data page count: 10
Data row count: 55278585.0000000000000000
Forwarded row count: -4399.0000000000000000
Deleted row count: 914929.0000000000000000
Data page CR count: 553111.0000000000000000
OAM + allocation page count: 31835
First extent data pages: 107827
Data row size: 108.6713711563731977
Pages in largest partition: 399905

Derived statistics:
Data page cluster ratio: 0.9844711029049814
Space utilization: 0.7522206671347493
Large I/O efficiency: 0.9019553926614436

Statistics for index: "mfs_files_crc" (nonclustered)
Index column list: "volume_id", "fname"
Leaf count: 1311673
Empty leaf page count: 0
Data page CR count: 37073212.0000000000000000
Index page CR count: 175318.0000000000000000
Data row CR count: 38693048.0000000000000000
First extent leaf pages: 5726
Leaf row size: 43.0944983777653405
Index height: 4

Derived statistics:
Data page cluster ratio: 0.0424105963496908
Index page cluster ratio: 0.9901037977264350
Data row cluster ratio: 0.3233850054708498
Space utilization: 0.9071708682496034
Large I/O efficiency: 0.9352144971679872

Statistics for index: "mfs_files_file_id" (nonclustered)
Index column list: "file_id", "status"
Leaf count: 532871
Empty leaf page count: 0
Data page CR count: 46396107.0000000000000000
Index page CR count: 102314.0000000000000000
Data row CR count: 47353365.0000000000000000
First extent leaf pages: 2699
Leaf row size: 17.2629596542180330
Index height: 4

Derived statistics:
Data page cluster ratio: 0.0204304602779360
Index page cluster ratio: 0.9234228824137501
Data row cluster ratio: 0.1545260375384703
Space utilization: 0.8945116772477099
Large I/O efficiency: 0.6510247878726106

Statistics for index: "dataset_name" (nonclustered)
Index column list: "dataset_name"
Leaf count: 234956
Empty leaf page count: 0
Data page CR count: 22063109.0000000000000000
Index page CR count: 54751.0000000000000000
Data row CR count: 24134623.0000000000000000
First extent leaf pages: 3943
Leaf row size: 7.7901669375992064
Index height: 4

Derived statistics:
Data page cluster ratio: 0.0876434043082863
Index page cluster ratio: 0.8765431498253771
Data row cluster ratio: 0.6072453561047760
Space utilization: 0.9154888702953941
Large I/O efficiency: 0.5364237201013429

Statistics for index: "ingest_date" (nonclustered)
Index column list: "ingest_date"
Leaf count: 371750
Empty leaf page count: 0
Data page CR count: 25231774.0000000000000000
Index page CR count: 51506.0000000000000000
Data row CR count: 26398273.0000000000000000
First extent leaf pages: 1115
Leaf row size: 11.5901262307722117
Index height: 4

Derived statistics:
Data page cluster ratio: 0.0450396861173282
Index page cluster ratio: 0.9845149270938049
Data row cluster ratio: 0.5631086804195636
Space utilization: 0.8608550534985810
Large I/O efficiency: 0.9022050257946413


Sherlock, Kevin Posted on 2007-09-19 22:37:14.0Z
From: "Sherlock, Kevin" <ksherlock@saionline.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <46f15fe2$1@forums-1-dub>
Subject: Re: undestanding optdiag forwarded row count
Lines: 118
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.3138
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: nic2.saionline.com
X-Original-NNTP-Posting-Host: nic2.saionline.com
Message-ID: <46f1a49a$1@forums-1-dub>
Date: 19 Sep 2007 15:37:14 -0700
X-Trace: forums-1-dub 1190241434 63.163.175.14 (19 Sep 2007 15:37:14 -0700)
X-Original-Trace: 19 Sep 2007 15:37:14 -0700, nic2.saionline.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10349
Article PK: 88984

Isabella,
Try a couple of things first.

Update statistics mfs_files mfs_files
exec sp_flushstats

Then run optdiag and see what you get. If still negative:

dbcc tablealloc('mfs_files','fix')

"Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
news:46f15fe2$1@forums-1-dub...
>I have a 55 mill rows DOL partition table , only with non-clustered indexes,
>below see my ASE vers ( 12.5.3-Linux ) and optidag output.
> I 'm trying to understand and fix the negative values for 'Forwarded row
> count' , I run the 'reorg forwarded_rows...' cmd to fix the values but
> getting same results.
> Any explanation what's going on....
> thank you,
> Isabella
>
> Adaptive Server Enterprise/12.5.3/EBF 12600 ESD#3/P/Linux Intel/Enterprise
> Linux/ase1253/1911/32-bit/OPT/Fri Jul 8 02:32:50 2005
>
>
>
>
> Statistics for table: "mfs_files"
>
> Data page count: 3988988
> Empty data page count: 10
> Data row count: 55278585.0000000000000000
> Forwarded row count: -4399.0000000000000000
> Deleted row count: 914929.0000000000000000
> Data page CR count: 553111.0000000000000000
> OAM + allocation page count: 31835
> First extent data pages: 107827
> Data row size: 108.6713711563731977
> Pages in largest partition: 399905
>
> Derived statistics:
> Data page cluster ratio: 0.9844711029049814
> Space utilization: 0.7522206671347493
> Large I/O efficiency: 0.9019553926614436
>
> Statistics for index: "mfs_files_crc" (nonclustered)
> Index column list: "volume_id", "fname"
> Leaf count: 1311673
> Empty leaf page count: 0
> Data page CR count: 37073212.0000000000000000
> Index page CR count: 175318.0000000000000000
> Data row CR count: 38693048.0000000000000000
> First extent leaf pages: 5726
> Leaf row size: 43.0944983777653405
> Index height: 4
>
> Derived statistics:
> Data page cluster ratio: 0.0424105963496908
> Index page cluster ratio: 0.9901037977264350
> Data row cluster ratio: 0.3233850054708498
> Space utilization: 0.9071708682496034
> Large I/O efficiency: 0.9352144971679872
>
> Statistics for index: "mfs_files_file_id" (nonclustered)
> Index column list: "file_id", "status"
> Leaf count: 532871
> Empty leaf page count: 0
> Data page CR count: 46396107.0000000000000000
> Index page CR count: 102314.0000000000000000
> Data row CR count: 47353365.0000000000000000
> First extent leaf pages: 2699
> Leaf row size: 17.2629596542180330
> Index height: 4
>
> Derived statistics:
> Data page cluster ratio: 0.0204304602779360
> Index page cluster ratio: 0.9234228824137501
> Data row cluster ratio: 0.1545260375384703
> Space utilization: 0.8945116772477099
> Large I/O efficiency: 0.6510247878726106
>
> Statistics for index: "dataset_name" (nonclustered)
> Index column list: "dataset_name"
> Leaf count: 234956
> Empty leaf page count: 0
> Data page CR count: 22063109.0000000000000000
> Index page CR count: 54751.0000000000000000
> Data row CR count: 24134623.0000000000000000
> First extent leaf pages: 3943
> Leaf row size: 7.7901669375992064
> Index height: 4
>
> Derived statistics:
> Data page cluster ratio: 0.0876434043082863
> Index page cluster ratio: 0.8765431498253771
> Data row cluster ratio: 0.6072453561047760
> Space utilization: 0.9154888702953941
> Large I/O efficiency: 0.5364237201013429
>
> Statistics for index: "ingest_date" (nonclustered)
> Index column list: "ingest_date"
> Leaf count: 371750
> Empty leaf page count: 0
> Data page CR count: 25231774.0000000000000000
> Index page CR count: 51506.0000000000000000
> Data row CR count: 26398273.0000000000000000
> First extent leaf pages: 1115
> Leaf row size: 11.5901262307722117
> Index height: 4
>
> Derived statistics:
> Data page cluster ratio: 0.0450396861173282
> Index page cluster ratio: 0.9845149270938049
> Data row cluster ratio: 0.5631086804195636
> Space utilization: 0.8608550534985810
> Large I/O efficiency: 0.9022050257946413


Isabella Posted on 2007-09-20 15:35:59.0Z
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: undestanding optdiag forwarded row count
References: <46f15fe2$1@forums-1-dub> <46f1a49a$1@forums-1-dub>
In-Reply-To: <46f1a49a$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: fw.hia.nrc.ca
X-Original-NNTP-Posting-Host: fw.hia.nrc.ca
Message-ID: <46f2935f$1@forums-1-dub>
Date: 20 Sep 2007 08:35:59 -0700
X-Trace: forums-1-dub 1190302559 204.174.103.3 (20 Sep 2007 08:35:59 -0700)
X-Original-Trace: 20 Sep 2007 08:35:59 -0700, fw.hia.nrc.ca
Lines: 122
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10354
Article PK: 88991


Sherlock, Kevin wrote:
> Isabella,
> Try a couple of things first.
>
> Update statistics mfs_files mfs_files

do you mean update statistics mfs_files mfs_files_crc
correct?
Isabella
> exec sp_flushstats
>
> Then run optdiag and see what you get. If still negative:
>
> dbcc tablealloc('mfs_files','fix')
>
> "Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
> news:46f15fe2$1@forums-1-dub...
>> I have a 55 mill rows DOL partition table , only with non-clustered indexes,
>> below see my ASE vers ( 12.5.3-Linux ) and optidag output.
>> I 'm trying to understand and fix the negative values for 'Forwarded row
>> count' , I run the 'reorg forwarded_rows...' cmd to fix the values but
>> getting same results.
>> Any explanation what's going on....
>> thank you,
>> Isabella
>>
>> Adaptive Server Enterprise/12.5.3/EBF 12600 ESD#3/P/Linux Intel/Enterprise
>> Linux/ase1253/1911/32-bit/OPT/Fri Jul 8 02:32:50 2005
>>
>>
>>
>>
>> Statistics for table: "mfs_files"
>>
>> Data page count: 3988988
>> Empty data page count: 10
>> Data row count: 55278585.0000000000000000
>> Forwarded row count: -4399.0000000000000000
>> Deleted row count: 914929.0000000000000000
>> Data page CR count: 553111.0000000000000000
>> OAM + allocation page count: 31835
>> First extent data pages: 107827
>> Data row size: 108.6713711563731977
>> Pages in largest partition: 399905
>>
>> Derived statistics:
>> Data page cluster ratio: 0.9844711029049814
>> Space utilization: 0.7522206671347493
>> Large I/O efficiency: 0.9019553926614436
>>
>> Statistics for index: "mfs_files_crc" (nonclustered)
>> Index column list: "volume_id", "fname"
>> Leaf count: 1311673
>> Empty leaf page count: 0
>> Data page CR count: 37073212.0000000000000000
>> Index page CR count: 175318.0000000000000000
>> Data row CR count: 38693048.0000000000000000
>> First extent leaf pages: 5726
>> Leaf row size: 43.0944983777653405
>> Index height: 4
>>
>> Derived statistics:
>> Data page cluster ratio: 0.0424105963496908
>> Index page cluster ratio: 0.9901037977264350
>> Data row cluster ratio: 0.3233850054708498
>> Space utilization: 0.9071708682496034
>> Large I/O efficiency: 0.9352144971679872
>>
>> Statistics for index: "mfs_files_file_id" (nonclustered)
>> Index column list: "file_id", "status"
>> Leaf count: 532871
>> Empty leaf page count: 0
>> Data page CR count: 46396107.0000000000000000
>> Index page CR count: 102314.0000000000000000
>> Data row CR count: 47353365.0000000000000000
>> First extent leaf pages: 2699
>> Leaf row size: 17.2629596542180330
>> Index height: 4
>>
>> Derived statistics:
>> Data page cluster ratio: 0.0204304602779360
>> Index page cluster ratio: 0.9234228824137501
>> Data row cluster ratio: 0.1545260375384703
>> Space utilization: 0.8945116772477099
>> Large I/O efficiency: 0.6510247878726106
>>
>> Statistics for index: "dataset_name" (nonclustered)
>> Index column list: "dataset_name"
>> Leaf count: 234956
>> Empty leaf page count: 0
>> Data page CR count: 22063109.0000000000000000
>> Index page CR count: 54751.0000000000000000
>> Data row CR count: 24134623.0000000000000000
>> First extent leaf pages: 3943
>> Leaf row size: 7.7901669375992064
>> Index height: 4
>>
>> Derived statistics:
>> Data page cluster ratio: 0.0876434043082863
>> Index page cluster ratio: 0.8765431498253771
>> Data row cluster ratio: 0.6072453561047760
>> Space utilization: 0.9154888702953941
>> Large I/O efficiency: 0.5364237201013429
>>
>> Statistics for index: "ingest_date" (nonclustered)
>> Index column list: "ingest_date"
>> Leaf count: 371750
>> Empty leaf page count: 0
>> Data page CR count: 25231774.0000000000000000
>> Index page CR count: 51506.0000000000000000
>> Data row CR count: 26398273.0000000000000000
>> First extent leaf pages: 1115
>> Leaf row size: 11.5901262307722117
>> Index height: 4
>>
>> Derived statistics:
>> Data page cluster ratio: 0.0450396861173282
>> Index page cluster ratio: 0.9845149270938049
>> Data row cluster ratio: 0.5631086804195636
>> Space utilization: 0.8608550534985810
>> Large I/O efficiency: 0.9022050257946413
>
>


Sherlock, Kevin Posted on 2007-09-20 15:44:14.0Z
From: "Sherlock, Kevin" <ksherlock@saionline.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <46f15fe2$1@forums-1-dub> <46f1a49a$1@forums-1-dub> <46f2935f$1@forums-1-dub>
Subject: Re: undestanding optdiag forwarded row count
Lines: 129
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.3138
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: nic2.saionline.com
X-Original-NNTP-Posting-Host: nic2.saionline.com
Message-ID: <46f2954e$1@forums-1-dub>
Date: 20 Sep 2007 08:44:14 -0700
X-Trace: forums-1-dub 1190303054 63.163.175.14 (20 Sep 2007 08:44:14 -0700)
X-Original-Trace: 20 Sep 2007 08:44:14 -0700, nic2.saionline.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10355
Article PK: 88990

No, just update the statistics for the data pages themselves (which is where the
forwarded rows are).

update statistics <tablename> <tablename>

"Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
news:46f2935f$1@forums-1-dub...
> Sherlock, Kevin wrote:
>> Isabella,
>> Try a couple of things first.
>>
>> Update statistics mfs_files mfs_files
> do you mean update statistics mfs_files mfs_files_crc
> correct?
> Isabella
>> exec sp_flushstats
>>
>> Then run optdiag and see what you get. If still negative:
>>
>> dbcc tablealloc('mfs_files','fix')
>>
>> "Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
>> news:46f15fe2$1@forums-1-dub...
>>> I have a 55 mill rows DOL partition table , only with non-clustered
>>> indexes, below see my ASE vers ( 12.5.3-Linux ) and optidag output.
>>> I 'm trying to understand and fix the negative values for 'Forwarded row
>>> count' , I run the 'reorg forwarded_rows...' cmd to fix the values but
>>> getting same results.
>>> Any explanation what's going on....
>>> thank you,
>>> Isabella
>>>
>>> Adaptive Server Enterprise/12.5.3/EBF 12600 ESD#3/P/Linux Intel/Enterprise
>>> Linux/ase1253/1911/32-bit/OPT/Fri Jul 8 02:32:50 2005
>>>
>>>
>>>
>>>
>>> Statistics for table: "mfs_files"
>>>
>>> Data page count: 3988988
>>> Empty data page count: 10
>>> Data row count: 55278585.0000000000000000
>>> Forwarded row count: -4399.0000000000000000
>>> Deleted row count: 914929.0000000000000000
>>> Data page CR count: 553111.0000000000000000
>>> OAM + allocation page count: 31835
>>> First extent data pages: 107827
>>> Data row size: 108.6713711563731977
>>> Pages in largest partition: 399905
>>>
>>> Derived statistics:
>>> Data page cluster ratio: 0.9844711029049814
>>> Space utilization: 0.7522206671347493
>>> Large I/O efficiency: 0.9019553926614436
>>>
>>> Statistics for index: "mfs_files_crc" (nonclustered)
>>> Index column list: "volume_id", "fname"
>>> Leaf count: 1311673
>>> Empty leaf page count: 0
>>> Data page CR count: 37073212.0000000000000000
>>> Index page CR count: 175318.0000000000000000
>>> Data row CR count: 38693048.0000000000000000
>>> First extent leaf pages: 5726
>>> Leaf row size: 43.0944983777653405
>>> Index height: 4
>>>
>>> Derived statistics:
>>> Data page cluster ratio: 0.0424105963496908
>>> Index page cluster ratio: 0.9901037977264350
>>> Data row cluster ratio: 0.3233850054708498
>>> Space utilization: 0.9071708682496034
>>> Large I/O efficiency: 0.9352144971679872
>>>
>>> Statistics for index: "mfs_files_file_id" (nonclustered)
>>> Index column list: "file_id", "status"
>>> Leaf count: 532871
>>> Empty leaf page count: 0
>>> Data page CR count: 46396107.0000000000000000
>>> Index page CR count: 102314.0000000000000000
>>> Data row CR count: 47353365.0000000000000000
>>> First extent leaf pages: 2699
>>> Leaf row size: 17.2629596542180330
>>> Index height: 4
>>>
>>> Derived statistics:
>>> Data page cluster ratio: 0.0204304602779360
>>> Index page cluster ratio: 0.9234228824137501
>>> Data row cluster ratio: 0.1545260375384703
>>> Space utilization: 0.8945116772477099
>>> Large I/O efficiency: 0.6510247878726106
>>>
>>> Statistics for index: "dataset_name" (nonclustered)
>>> Index column list: "dataset_name"
>>> Leaf count: 234956
>>> Empty leaf page count: 0
>>> Data page CR count: 22063109.0000000000000000
>>> Index page CR count: 54751.0000000000000000
>>> Data row CR count: 24134623.0000000000000000
>>> First extent leaf pages: 3943
>>> Leaf row size: 7.7901669375992064
>>> Index height: 4
>>>
>>> Derived statistics:
>>> Data page cluster ratio: 0.0876434043082863
>>> Index page cluster ratio: 0.8765431498253771
>>> Data row cluster ratio: 0.6072453561047760
>>> Space utilization: 0.9154888702953941
>>> Large I/O efficiency: 0.5364237201013429
>>>
>>> Statistics for index: "ingest_date" (nonclustered)
>>> Index column list: "ingest_date"
>>> Leaf count: 371750
>>> Empty leaf page count: 0
>>> Data page CR count: 25231774.0000000000000000
>>> Index page CR count: 51506.0000000000000000
>>> Data row CR count: 26398273.0000000000000000
>>> First extent leaf pages: 1115
>>> Leaf row size: 11.5901262307722117
>>> Index height: 4
>>>
>>> Derived statistics:
>>> Data page cluster ratio: 0.0450396861173282
>>> Index page cluster ratio: 0.9845149270938049
>>> Data row cluster ratio: 0.5631086804195636
>>> Space utilization: 0.8608550534985810
>>> Large I/O efficiency: 0.9022050257946413
>>


Isabella Posted on 2007-09-20 19:18:33.0Z
Message-ID: <46F2C788.10805@nrc-cnrc.gc.ca>
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
To: "Sherlock, Kevin" <ksherlock@saionline.com>
Subject: Re: undestanding optdiag forwarded row count
References: <46f15fe2$1@forums-1-dub> <46f1a49a$1@forums-1-dub> <46f2935f$1@forums-1-dub> <46f2954e$1@forums-1-dub>
In-Reply-To: <46f2954e$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: fw.hia.nrc.ca
X-Original-NNTP-Posting-Host: fw.hia.nrc.ca
Date: 20 Sep 2007 12:18:33 -0700
X-Trace: forums-1-dub 1190315913 204.174.103.3 (20 Sep 2007 12:18:33 -0700)
X-Original-Trace: 20 Sep 2007 12:18:33 -0700, fw.hia.nrc.ca
Lines: 148
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10356
Article PK: 88993

Hello Kevin,

Here is an update :
I run update stats & flush stats as per your advise , and again
optdiag but still same negative values for Forwarded row count.
I can't run dbcc tablealloc for this table is a 'hot' table in this db
and will blocked the rest of the processes running.

This is a primary replicate db, so I was able to run dbbc tablealloc in
ws db for same table but the row forward negative values are still
there, took me over 2h for table alloc to complete , no errors had been
reported but but still negative values.

Any suggestions what should be next?

thank you,
Isabella

Sherlock, Kevin wrote:
> No, just update the statistics for the data pages themselves (which is where the
> forwarded rows are).
>
> update statistics <tablename> <tablename>
>
> "Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
> news:46f2935f$1@forums-1-dub...
>> Sherlock, Kevin wrote:
>>> Isabella,
>>> Try a couple of things first.
>>>
>>> Update statistics mfs_files mfs_files
>> do you mean update statistics mfs_files mfs_files_crc
>> correct?
>> Isabella
>>> exec sp_flushstats
>>>
>>> Then run optdiag and see what you get. If still negative:
>>>
>>> dbcc tablealloc('mfs_files','fix')
>>>
>>> "Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
>>> news:46f15fe2$1@forums-1-dub...
>>>> I have a 55 mill rows DOL partition table , only with non-clustered
>>>> indexes, below see my ASE vers ( 12.5.3-Linux ) and optidag output.
>>>> I 'm trying to understand and fix the negative values for 'Forwarded row
>>>> count' , I run the 'reorg forwarded_rows...' cmd to fix the values but
>>>> getting same results.
>>>> Any explanation what's going on....
>>>> thank you,
>>>> Isabella
>>>>
>>>> Adaptive Server Enterprise/12.5.3/EBF 12600 ESD#3/P/Linux Intel/Enterprise
>>>> Linux/ase1253/1911/32-bit/OPT/Fri Jul 8 02:32:50 2005
>>>>
>>>>
>>>>
>>>>
>>>> Statistics for table: "mfs_files"
>>>>
>>>> Data page count: 3988988
>>>> Empty data page count: 10
>>>> Data row count: 55278585.0000000000000000
>>>> Forwarded row count: -4399.0000000000000000
>>>> Deleted row count: 914929.0000000000000000
>>>> Data page CR count: 553111.0000000000000000
>>>> OAM + allocation page count: 31835
>>>> First extent data pages: 107827
>>>> Data row size: 108.6713711563731977
>>>> Pages in largest partition: 399905
>>>>
>>>> Derived statistics:
>>>> Data page cluster ratio: 0.9844711029049814
>>>> Space utilization: 0.7522206671347493
>>>> Large I/O efficiency: 0.9019553926614436
>>>>
>>>> Statistics for index: "mfs_files_crc" (nonclustered)
>>>> Index column list: "volume_id", "fname"
>>>> Leaf count: 1311673
>>>> Empty leaf page count: 0
>>>> Data page CR count: 37073212.0000000000000000
>>>> Index page CR count: 175318.0000000000000000
>>>> Data row CR count: 38693048.0000000000000000
>>>> First extent leaf pages: 5726
>>>> Leaf row size: 43.0944983777653405
>>>> Index height: 4
>>>>
>>>> Derived statistics:
>>>> Data page cluster ratio: 0.0424105963496908
>>>> Index page cluster ratio: 0.9901037977264350
>>>> Data row cluster ratio: 0.3233850054708498
>>>> Space utilization: 0.9071708682496034
>>>> Large I/O efficiency: 0.9352144971679872
>>>>
>>>> Statistics for index: "mfs_files_file_id" (nonclustered)
>>>> Index column list: "file_id", "status"
>>>> Leaf count: 532871
>>>> Empty leaf page count: 0
>>>> Data page CR count: 46396107.0000000000000000
>>>> Index page CR count: 102314.0000000000000000
>>>> Data row CR count: 47353365.0000000000000000
>>>> First extent leaf pages: 2699
>>>> Leaf row size: 17.2629596542180330
>>>> Index height: 4
>>>>
>>>> Derived statistics:
>>>> Data page cluster ratio: 0.0204304602779360
>>>> Index page cluster ratio: 0.9234228824137501
>>>> Data row cluster ratio: 0.1545260375384703
>>>> Space utilization: 0.8945116772477099
>>>> Large I/O efficiency: 0.6510247878726106
>>>>
>>>> Statistics for index: "dataset_name" (nonclustered)
>>>> Index column list: "dataset_name"
>>>> Leaf count: 234956
>>>> Empty leaf page count: 0
>>>> Data page CR count: 22063109.0000000000000000
>>>> Index page CR count: 54751.0000000000000000
>>>> Data row CR count: 24134623.0000000000000000
>>>> First extent leaf pages: 3943
>>>> Leaf row size: 7.7901669375992064
>>>> Index height: 4
>>>>
>>>> Derived statistics:
>>>> Data page cluster ratio: 0.0876434043082863
>>>> Index page cluster ratio: 0.8765431498253771
>>>> Data row cluster ratio: 0.6072453561047760
>>>> Space utilization: 0.9154888702953941
>>>> Large I/O efficiency: 0.5364237201013429
>>>>
>>>> Statistics for index: "ingest_date" (nonclustered)
>>>> Index column list: "ingest_date"
>>>> Leaf count: 371750
>>>> Empty leaf page count: 0
>>>> Data page CR count: 25231774.0000000000000000
>>>> Index page CR count: 51506.0000000000000000
>>>> Data row CR count: 26398273.0000000000000000
>>>> First extent leaf pages: 1115
>>>> Leaf row size: 11.5901262307722117
>>>> Index height: 4
>>>>
>>>> Derived statistics:
>>>> Data page cluster ratio: 0.0450396861173282
>>>> Index page cluster ratio: 0.9845149270938049
>>>> Data row cluster ratio: 0.5631086804195636
>>>> Space utilization: 0.8608550534985810
>>>> Large I/O efficiency: 0.9022050257946413
>


Sherlock, Kevin Posted on 2007-09-20 20:17:53.0Z
From: "Sherlock, Kevin" <ksherlock@saionline.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <46f15fe2$1@forums-1-dub> <46f1a49a$1@forums-1-dub> <46f2935f$1@forums-1-dub> <46f2954e$1@forums-1-dub> <46F2C788.10805@nrc-cnrc.gc.ca>
Subject: Re: undestanding optdiag forwarded row count
Lines: 157
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.3138
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: nic2.saionline.com
X-Original-NNTP-Posting-Host: nic2.saionline.com
Message-ID: <46f2d571$1@forums-1-dub>
Date: 20 Sep 2007 13:17:53 -0700
X-Trace: forums-1-dub 1190319473 63.163.175.14 (20 Sep 2007 13:17:53 -0700)
X-Original-Trace: 20 Sep 2007 13:17:53 -0700, nic2.saionline.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10357
Article PK: 88994

Darn.

I'm afraid all I can suggest now is "reorg rebuild <tablename>" which sounds
like some downtime for you.

"Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
news:46F2C788.10805@nrc-cnrc.gc.ca...
> Hello Kevin,
>
> Here is an update :
> I run update stats & flush stats as per your advise , and again optdiag but
> still same negative values for Forwarded row count.
> I can't run dbcc tablealloc for this table is a 'hot' table in this db and
> will blocked the rest of the processes running.
>
> This is a primary replicate db, so I was able to run dbbc tablealloc in ws db
> for same table but the row forward negative values are still there, took me
> over 2h for table alloc to complete , no errors had been reported but but
> still negative values.
>
> Any suggestions what should be next?
>
> thank you,
> Isabella
>
>
> Sherlock, Kevin wrote:
>> No, just update the statistics for the data pages themselves (which is where
>> the forwarded rows are).
>>
>> update statistics <tablename> <tablename>
>>
>> "Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
>> news:46f2935f$1@forums-1-dub...
>>> Sherlock, Kevin wrote:
>>>> Isabella,
>>>> Try a couple of things first.
>>>>
>>>> Update statistics mfs_files mfs_files
>>> do you mean update statistics mfs_files mfs_files_crc
>>> correct?
>>> Isabella
>>>> exec sp_flushstats
>>>>
>>>> Then run optdiag and see what you get. If still negative:
>>>>
>>>> dbcc tablealloc('mfs_files','fix')
>>>>
>>>> "Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
>>>> news:46f15fe2$1@forums-1-dub...
>>>>> I have a 55 mill rows DOL partition table , only with non-clustered
>>>>> indexes, below see my ASE vers ( 12.5.3-Linux ) and optidag output.
>>>>> I 'm trying to understand and fix the negative values for 'Forwarded row
>>>>> count' , I run the 'reorg forwarded_rows...' cmd to fix the values but
>>>>> getting same results.
>>>>> Any explanation what's going on....
>>>>> thank you,
>>>>> Isabella
>>>>>
>>>>> Adaptive Server Enterprise/12.5.3/EBF 12600 ESD#3/P/Linux Intel/Enterprise
>>>>> Linux/ase1253/1911/32-bit/OPT/Fri Jul 8 02:32:50 2005
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Statistics for table: "mfs_files"
>>>>>
>>>>> Data page count: 3988988
>>>>> Empty data page count: 10
>>>>> Data row count: 55278585.0000000000000000
>>>>> Forwarded row count: -4399.0000000000000000
>>>>> Deleted row count: 914929.0000000000000000
>>>>> Data page CR count: 553111.0000000000000000
>>>>> OAM + allocation page count: 31835
>>>>> First extent data pages: 107827
>>>>> Data row size: 108.6713711563731977
>>>>> Pages in largest partition: 399905
>>>>>
>>>>> Derived statistics:
>>>>> Data page cluster ratio: 0.9844711029049814
>>>>> Space utilization: 0.7522206671347493
>>>>> Large I/O efficiency: 0.9019553926614436
>>>>>
>>>>> Statistics for index: "mfs_files_crc" (nonclustered)
>>>>> Index column list: "volume_id", "fname"
>>>>> Leaf count: 1311673
>>>>> Empty leaf page count: 0
>>>>> Data page CR count: 37073212.0000000000000000
>>>>> Index page CR count: 175318.0000000000000000
>>>>> Data row CR count: 38693048.0000000000000000
>>>>> First extent leaf pages: 5726
>>>>> Leaf row size: 43.0944983777653405
>>>>> Index height: 4
>>>>>
>>>>> Derived statistics:
>>>>> Data page cluster ratio: 0.0424105963496908
>>>>> Index page cluster ratio: 0.9901037977264350
>>>>> Data row cluster ratio: 0.3233850054708498
>>>>> Space utilization: 0.9071708682496034
>>>>> Large I/O efficiency: 0.9352144971679872
>>>>>
>>>>> Statistics for index: "mfs_files_file_id" (nonclustered)
>>>>> Index column list: "file_id", "status"
>>>>> Leaf count: 532871
>>>>> Empty leaf page count: 0
>>>>> Data page CR count: 46396107.0000000000000000
>>>>> Index page CR count: 102314.0000000000000000
>>>>> Data row CR count: 47353365.0000000000000000
>>>>> First extent leaf pages: 2699
>>>>> Leaf row size: 17.2629596542180330
>>>>> Index height: 4
>>>>>
>>>>> Derived statistics:
>>>>> Data page cluster ratio: 0.0204304602779360
>>>>> Index page cluster ratio: 0.9234228824137501
>>>>> Data row cluster ratio: 0.1545260375384703
>>>>> Space utilization: 0.8945116772477099
>>>>> Large I/O efficiency: 0.6510247878726106
>>>>>
>>>>> Statistics for index: "dataset_name" (nonclustered)
>>>>> Index column list: "dataset_name"
>>>>> Leaf count: 234956
>>>>> Empty leaf page count: 0
>>>>> Data page CR count: 22063109.0000000000000000
>>>>> Index page CR count: 54751.0000000000000000
>>>>> Data row CR count: 24134623.0000000000000000
>>>>> First extent leaf pages: 3943
>>>>> Leaf row size: 7.7901669375992064
>>>>> Index height: 4
>>>>>
>>>>> Derived statistics:
>>>>> Data page cluster ratio: 0.0876434043082863
>>>>> Index page cluster ratio: 0.8765431498253771
>>>>> Data row cluster ratio: 0.6072453561047760
>>>>> Space utilization: 0.9154888702953941
>>>>> Large I/O efficiency: 0.5364237201013429
>>>>>
>>>>> Statistics for index: "ingest_date" (nonclustered)
>>>>> Index column list: "ingest_date"
>>>>> Leaf count: 371750
>>>>> Empty leaf page count: 0
>>>>> Data page CR count: 25231774.0000000000000000
>>>>> Index page CR count: 51506.0000000000000000
>>>>> Data row CR count: 26398273.0000000000000000
>>>>> First extent leaf pages: 1115
>>>>> Leaf row size: 11.5901262307722117
>>>>> Index height: 4
>>>>>
>>>>> Derived statistics:
>>>>> Data page cluster ratio: 0.0450396861173282
>>>>> Index page cluster ratio: 0.9845149270938049
>>>>> Data row cluster ratio: 0.5631086804195636
>>>>> Space utilization: 0.8608550534985810
>>>>> Large I/O efficiency: 0.9022050257946413
>>


Manish Negandhi Posted on 2007-09-22 13:27:01.0Z
From: "Manish Negandhi" <nospam_manish_negandhi@yahoo.com>
Newsgroups: sybase.public.ase.performance+tuning
Organization: 122.167.247.11
References: <46f15fe2$1@forums-1-dub> <46f1a49a$1@forums-1-dub> <46f2935f$1@forums-1-dub>
X-Newsreader: AspNNTP 1.50 (JodoHost)
Subject: Re: undestanding optdiag forwarded row count
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 64.187.108.200
X-Original-NNTP-Posting-Host: 64.187.108.200
Message-ID: <46f51825$1@forums-1-dub>
Date: 22 Sep 2007 06:27:01 -0700
X-Trace: forums-1-dub 1190467621 64.187.108.200 (22 Sep 2007 06:27:01 -0700)
X-Original-Trace: 22 Sep 2007 06:27:01 -0700, 64.187.108.200
Lines: 137
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10358
Article PK: 88992

Hi Isabella

Did you try 'reorg compact <table name>' ? . Reorg compact undoes all row
forwarding page by page unlike 'reorg forwarded rows'. If the bitmap allocation
is inaccurate, 'reorg forwarded' might not fix some of the forwarded rows
I remember resolving this problem using 'compact' instead of 'forwarded rows'

Thanks
Manish


On 20 Sep 2007 08:35:59 -0700,
in sybase.public.ase.performance+tuning

Isabella <isabella.ghiurea@nrc-cnrc.gc.ca> wrote:
>Sherlock, Kevin wrote:
>> Isabella,
>> Try a couple of things first.
>>
>> Update statistics mfs_files mfs_files
>do you mean update statistics mfs_files mfs_files_crc
>correct?
>Isabella
>> exec sp_flushstats
>>
>> Then run optdiag and see what you get. If still negative:
>>
>> dbcc tablealloc('mfs_files','fix')
>>
>> "Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
>> news:46f15fe2$1@forums-1-dub...
>>> I have a 55 mill rows DOL partition table , only with non-clustered
indexes,
>>> below see my ASE vers ( 12.5.3-Linux ) and optidag output.
>>> I 'm trying to understand and fix the negative values for 'Forwarded row
>>> count' , I run the 'reorg forwarded_rows...' cmd to fix the values but
>>> getting same results.
>>> Any explanation what's going on....
>>> thank you,
>>> Isabella
>>>
>>> Adaptive Server Enterprise/12.5.3/EBF 12600 ESD#3/P/Linux Intel/Enterprise
>>> Linux/ase1253/1911/32-bit/OPT/Fri Jul 8 02:32:50 2005
>>>
>>>
>>>
>>>
>>> Statistics for table: "mfs_files"
>>>
>>> Data page count: 3988988
>>> Empty data page count: 10
>>> Data row count: 55278585.0000000000000000
>>> Forwarded row count: -4399.0000000000000000
>>> Deleted row count: 914929.0000000000000000
>>> Data page CR count: 553111.0000000000000000
>>> OAM + allocation page count: 31835
>>> First extent data pages: 107827
>>> Data row size: 108.6713711563731977
>>> Pages in largest partition: 399905
>>>
>>> Derived statistics:
>>> Data page cluster ratio: 0.9844711029049814
>>> Space utilization: 0.7522206671347493
>>> Large I/O efficiency: 0.9019553926614436
>>>
>>> Statistics for index: "mfs_files_crc" (nonclustered)
>>> Index column list: "volume_id", "fname"
>>> Leaf count: 1311673
>>> Empty leaf page count: 0
>>> Data page CR count: 37073212.0000000000000000
>>> Index page CR count: 175318.0000000000000000
>>> Data row CR count: 38693048.0000000000000000
>>> First extent leaf pages: 5726
>>> Leaf row size: 43.0944983777653405
>>> Index height: 4
>>>
>>> Derived statistics:
>>> Data page cluster ratio: 0.0424105963496908
>>> Index page cluster ratio: 0.9901037977264350
>>> Data row cluster ratio: 0.3233850054708498
>>> Space utilization: 0.9071708682496034
>>> Large I/O efficiency: 0.9352144971679872
>>>
>>> Statistics for index: "mfs_files_file_id" (nonclustered)
>>> Index column list: "file_id", "status"
>>> Leaf count: 532871
>>> Empty leaf page count: 0
>>> Data page CR count: 46396107.0000000000000000
>>> Index page CR count: 102314.0000000000000000
>>> Data row CR count: 47353365.0000000000000000
>>> First extent leaf pages: 2699
>>> Leaf row size: 17.2629596542180330
>>> Index height: 4
>>>
>>> Derived statistics:
>>> Data page cluster ratio: 0.0204304602779360
>>> Index page cluster ratio: 0.9234228824137501
>>> Data row cluster ratio: 0.1545260375384703
>>> Space utilization: 0.8945116772477099
>>> Large I/O efficiency: 0.6510247878726106
>>>
>>> Statistics for index: "dataset_name" (nonclustered)
>>> Index column list: "dataset_name"
>>> Leaf count: 234956
>>> Empty leaf page count: 0
>>> Data page CR count: 22063109.0000000000000000
>>> Index page CR count: 54751.0000000000000000
>>> Data row CR count: 24134623.0000000000000000
>>> First extent leaf pages: 3943
>>> Leaf row size: 7.7901669375992064
>>> Index height: 4
>>>
>>> Derived statistics:
>>> Data page cluster ratio: 0.0876434043082863
>>> Index page cluster ratio: 0.8765431498253771
>>> Data row cluster ratio: 0.6072453561047760
>>> Space utilization: 0.9154888702953941
>>> Large I/O efficiency: 0.5364237201013429
>>>
>>> Statistics for index: "ingest_date" (nonclustered)
>>> Index column list: "ingest_date"
>>> Leaf count: 371750
>>> Empty leaf page count: 0
>>> Data page CR count: 25231774.0000000000000000
>>> Index page CR count: 51506.0000000000000000
>>> Data row CR count: 26398273.0000000000000000
>>> First extent leaf pages: 1115
>>> Leaf row size: 11.5901262307722117
>>> Index height: 4
>>>
>>> Derived statistics:
>>> Data page cluster ratio: 0.0450396861173282
>>> Index page cluster ratio: 0.9845149270938049
>>> Data row cluster ratio: 0.5631086804195636
>>> Space utilization: 0.8608550534985810
>>> Large I/O efficiency: 0.9022050257946413
>>
>>