ASE Database forum - Improving data row / data page cluster ratio for an index on a DOL table

Author

Improving data row / data page cluster ratio for an index on a DOL table

Jesus M. Salvo Jr.

2005-10-27, 8:21 am

Hi,

Adaptive Server Enterprise/12.5.3/EBF 12330 ESD#1

I have the following output from optdiag statistics.

1) What is interesting is that the derived statistics for the table itself is high
( DPCR = 0.99, SPU = 0.73, LIO = 0.99 ).

But one of its non-clustered index have a low DRCR / DPCR, despite having a high IPCR.
So I was wondering what I can do to improve the DRCR and DPCR for the index ?

I have done "reorg rebuild table index", but that does not really improve the DRCR or DPCR of the index,
but usually improves the IPCR, LGIO, and SPUT for the index. In the case of the index below, since the IPCR
is already 0.99, no improvements were measured.

I cannot really do a "reorg rebuild table", because that requires a 'select into/bulkcopy'

2) Also, what does it mean if the data page CR count on the table itself ( 22028 )
is lower than the data page CR count on the index ( 14464584 )

Thanks.

Statistics for table: "player_properties"

Data page count: 299808
Empty data page count: 0
Data row count: 18504011.0000000000000000
Forwarded row count: -5.0000000000000000
Deleted row count: -2927.0000000000000000
Data page CR count: 22028.0000000000000000
OAM + allocation page count: 15894
First extent data pages: 6328
Data row size: 48.5234551723086440

Derived statistics:
Data page cluster ratio: 0.9999900000000000
Space utilization: 0.7394679494188264
Large I/O efficiency: 0.9999300048996574

Statistics for index: " player_properties_id
x1" (nonclustered)
Index column list: "game_type_id", "property_name", "property_value", "player_id"
Leaf count: 258658
Empty leaf page count: 505
Data page CR count: 14464584.0000000000000000
Index page CR count: 25914.0000000000000000
Data row CR count: 17032484.0000000000000000
First extent leaf pages: 1425
Leaf row size: 41.9836194801693594
Index height: 4

Derived statistics:
Data page cluster ratio: 0.1510973193479709
Index page cluster ratio: 0.9999900000000000
Data row cluster ratio: 0.0808344733174502
Space utilization: 0.7415916251879651
Large I/O efficiency: 0.9999300048996574

Sherlock, Kevin

2005-10-27, 8:21 am

I'll attempt to explain, but others here can probably do better.

First off, you have a DOL table here, so the "table" is a heap. Regardless
if it's a heap or not, the DPCR for a "table" (or clustered APL index) is
simply an indication of how many "extent jumps" are involved in scanning the
leaf level of the table. If a table has 12 extents, and the DPCR is 1.0,
then you should see a CR count of 12 for that table. IE, 12 extent "jumps"
are needed to scan the table in order.

For a non-clustered index (or a clustered DOL index), the IPCR is the same
concept as above. That is, it's a measure of how many extent jumps are
necessary to scan the LEAF level of the index (not the table). For these
indexes the DPCR factors in the extents of the base table. So, if the leaf
level of the index is scanned, and then you follow the index to the table,
this is an indication of how extents of the table are read via this index.
The DRCR is similar, except that it indicates how well ordered the base
table is in relationship to this index. CR counts follow suit here. DPCR
and DRCR CR counts include those extent jumps of the table via scanning
this index. That is why they are typically much higher than the CR counts
of the base table.

So, let's jump to your case. If you have a DOL table with no placement
index (no clustered index) the base table is essentially a heap. The order
of rows isn't managed, it's a heap. Your index is by "game_type_id", so
unless you insert records into the table sequentially by "game_type_id" your
cluster ratios for the index will be low. If the inserts of rows into the
table are essentially random with respect to "game_type_id" (which I would
expect), then you'll see what you observe.

If you have a datetime column on the table which records when the row was
inserted, you could create an index on that column and notice a nice
DPCR/DRCR for that index because it is ordered in a very similar way to the
table (since a heap orders chronologically). Obviously, if you change the
index below to a DOL clustered index, you'll notice that such "placement"
indexes typically will have high DPCR and DRCR after a rebuild.

You could also create a DOL clustered index on the table with the same key
values of the non-clustered index, and then drop it. That will re-order
your heap (and rebuild the non-clustered index), and therefore increase your
DPCR/DRCR for the non-clustered index, but won't do anything for future rows
inserts.

"Jesus M. Salvo Jr." <noone@noone.org> wrote in message
news:434b1166@forums
-1-dub...
> Hi,
>
> Adaptive Server Enterprise/12.5.3/EBF 12330 ESD#1
>
> I have the following output from optdiag statistics.
>
> 1) What is interesting is that the derived statistics for the table itself
is high
> ( DPCR = 0.99, SPU = 0.73, LIO = 0.99 ).
>
> But one of its non-clustered index have a low DRCR / DPCR, despite having
a high IPCR.
> So I was wondering what I can do to improve the DRCR and DPCR for the
index ?
>
> I have done "reorg rebuild table index", but that does not really improve
the DRCR or DPCR of the index,
> but usually improves the IPCR, LGIO, and SPUT for the index. In the case
of the index below, since the IPCR
> is already 0.99, no improvements were measured.
>
> I cannot really do a "reorg rebuild table", because that requires a
'select into/ bulkcopy'
>
>
> 2) Also, what does it mean if the data page CR count on the table itself
( 22028 )
> is lower than the data page CR count on the index ( 14464584 )
>
> Thanks.
>
>
>
>
>
> Statistics for table: "player_properties"
>
> Data page count: 299808
> Empty data page count: 0
> Data row count: 18504011.0000000000000000
> Forwarded row count: -5.0000000000000000
> Deleted row count: -2927.0000000000000000
> Data page CR count: 22028.0000000000000000
> OAM + allocation page count: 15894
> First extent data pages: 6328
> Data row size: 48.5234551723086440
>
> Derived statistics:
> Data page cluster ratio: 0.9999900000000000
> Space utilization: 0.7394679494188264
> Large I/O efficiency: 0.9999300048996574
>
> Statistics for index: " player_properties_id
x1"
(nonclustered)[color
=darkred]
> Index column list: "game_type_id", "property_name",[/color]
"property_value", "player_id"
> Leaf count: 258658
> Empty leaf page count: 505
> Data page CR count: 14464584.0000000000000000
> Index page CR count: 25914.0000000000000000
> Data row CR count: 17032484.0000000000000000
> First extent leaf pages: 1425
> Leaf row size: 41.9836194801693594
> Index height: 4
>
> Derived statistics:
> Data page cluster ratio: 0.1510973193479709
> Index page cluster ratio: 0.9999900000000000
> Data row cluster ratio: 0.0808344733174502
> Space utilization: 0.7415916251879651
> Large I/O efficiency: 0.9999300048996574
>

Jesus M. Salvo Jr.

2005-10-27, 8:21 am

Sherlock, Kevin wrote:

>
> So, let's jump to your case. If you have a DOL table with no placement
> index (no clustered index) the base table is essentially a heap. The
> order
> of rows isn't managed, it's a heap. Your index is by "game_type_id", so
> unless you insert records into the table sequentially by "game_type_id"
> your
> cluster ratios for the index will be low. If the inserts of rows into the
> table are essentially random with respect to "game_type_id" (which I would
> expect), then you'll see what you observe.
>

Thanks Kevin for the explanation ... I understand now that I cannot really
do anything about it, apart from creating a clustered index similar to the
non-clustered index, where the CR will be high but will eventually be low
again as time goes on.

John

Sherlock, Kevin

2005-10-27, 8:21 am

I should back-up here and mention that the definition for DPCR for DOL tables
differs slightly from APL tables. For DOL tables/heaps, DPCR (and IPCR for
indexes) indicates how "full" (ie, average percentag of non-empty pages) are in
each extent. Since table/index scans on DOL tables are done via the OAM pages,
extents are scanned in whole, and don't depend on the classic APL linked list of
page pointers. So the below definition of "extent jumps" doesn't really apply
to DOL tables.

"Sherlock, Kevin" <ksherlock@saionline.com> wrote in message
news:434b2eea$1@foru
ms-1-dub...
> I'll attempt to explain, but others here can probably do better.
>
> First off, you have a DOL table here, so the "table" is a heap. Regardless
> if it's a heap or not, the DPCR for a "table" (or clustered APL index) is
> simply an indication of how many "extent jumps" are involved in scanning the
> leaf level of the table. If a table has 12 extents, and the DPCR is 1.0,
> then you should see a CR count of 12 for that table. IE, 12 extent "jumps"
> are needed to scan the table in order.
>
> For a non-clustered index (or a clustered DOL index), the IPCR is the same
> concept as above. That is, it's a measure of how many extent jumps are
> necessary to scan the LEAF level of the index (not the table). For these
> indexes the DPCR factors in the extents of the base table. So, if the leaf
> level of the index is scanned, and then you follow the index to the table,
> this is an indication of how extents of the table are read via this index.
> The DRCR is similar, except that it indicates how well ordered the base
> table is in relationship to this index. CR counts follow suit here. DPCR
> and DRCR CR counts include those extent jumps of the table via scanning
> this index. That is why they are typically much higher than the CR counts
> of the base table.
>
> So, let's jump to your case. If you have a DOL table with no placement
> index (no clustered index) the base table is essentially a heap. The order
> of rows isn't managed, it's a heap. Your index is by "game_type_id", so
> unless you insert records into the table sequentially by "game_type_id" your
> cluster ratios for the index will be low. If the inserts of rows into the
> table are essentially random with respect to "game_type_id" (which I would
> expect), then you'll see what you observe.
>
> If you have a datetime column on the table which records when the row was
> inserted, you could create an index on that column and notice a nice
> DPCR/DRCR for that index because it is ordered in a very similar way to the
> table (since a heap orders chronologically). Obviously, if you change the
> index below to a DOL clustered index, you'll notice that such "placement"
> indexes typically will have high DPCR and DRCR after a rebuild.
>
> You could also create a DOL clustered index on the table with the same key
> values of the non-clustered index, and then drop it. That will re-order
> your heap (and rebuild the non-clustered index), and therefore increase your
> DPCR/DRCR for the non-clustered index, but won't do anything for future rows
> inserts.
>
>
> "Jesus M. Salvo Jr." <noone@noone.org> wrote in message
> news:434b1166@forums
-1-dub...
> is high
> a high IPCR.
> index ?
> the DRCR or DPCR of the index,
> of the index below, since the IPCR
> 'select into/bulkcopy'
> ( 22028 )
> (nonclustered)
> "property_value", "player_id"
>
>

Sponsored Links