cancel
Showing results for 
Search instead for 
Did you mean: 

How to prevent wasted space on a table

Former Member
0 Kudos

I am running a SAP Solution Manager 7.1 on Sybase ASE 15.7.0.110. The InfoCube table /BI0/F0SMD_PE2H keeps on growing quickly. This wasn't the case on other RDBMS. The ATM job doesn't help. Also running the ATM job manually doesn't change the table size:

reorg compact [SMP.SAPSR3./BI0/F0SMD_PE2H]


Only if I use the rebuild option, the table size shrinks:

reorg rebuild [SMP.SAPSR3./BI0/F0SMD_PE2H]


This causes the InfoCube with 1 million rows to shrink from 8 GB to 400 MB. That was an awful lot of wasted space.

Is it possible to avoid this excessive growth? Looks like the daily data load is causing the table to grow quickly again.

Or should I try to perform this "reorg rebuild" via the weekly ATM job?


Regards,

Mark

Accepted Solutions (1)

Accepted Solutions (1)

jgleichmann
Active Contributor
0 Kudos

Hi Mark,

as this is a SAP cube on a SolMan have you checked if the indexes are deleted before the dataload and getting recreated after it?

Can you check the empty partitions with the following report (implement latest fixes for this report; note 1974523😞

SAP_DROP_EMPTY_FPARTITIONS


Have you already tried to collapse the cube frequently to reduce the size of the F-table and remove duplicates/REQ ID/null values?


You have a lot of index partitions which you should try to reduce.


Regards,

Jens

Former Member
0 Kudos

Hello Jens,

this is a standard data load for Solution Manager Diagnostics defined by SAP. The hourly data from InfoCube 0SMD_PE2H is condensed into InfoCube 0SMD_PE2D. If I use BW compression (for the Sybase ASE experts: this has nothing to do with Sybase ASE compression) then this will probably negatively affect the BW data loads into the 0SMD_PE2D InfoCube. Also I cannot easily change that SAP defined data load, e.g. to drop & recreated the indexes.

Your tip about SAP_DROP_EMPTY_FPARTITIONS sounds great, I have to implement the note first, but then I will run that report to see whether dropping empty partitions will significantly reduce the unused space.

Regards,

Mark

Former Member
0 Kudos

Jeff, I will try this SAP_DROP_EMPTY_FPARTITIONS report. Until then I have collected the data you suggested. Does this confirm that there is space wasted in empty partitions? I can only see quite few empty partitons for my BW fact table.

former_member182259
Contributor
0 Kudos

The empty partitions is not going to change things.....   Unfortunately, you don't specify when you took the snapshots using the SQL query - I guess one was *after* the load and *before* the reorg??? ...the last one was after the reorg....what I think we are missing is the one befoe the load.   Anyhow, what was interesting was looking at where the delta was:

index_nameindidpartition_namedata pagesused pagesreserved pages
/BI0/F0SMD_PE2H0/BI0/F0SMD_PE2H_0000000062-1-1-8
/BI0/F0SMD_PE2H0/BI0/F0SMD_PE2H_0000000149-3,603-3,603-29,008
/BI0/F0SMD_PE2H0/BI0/F0SMD_PE2H_0000000150-5,435-5,435-43,760
/BI0/F0SMD_PE2H0/BI0/F0SMD_PE2H_0000000151-2,257-2,257-18,176
/BI0/F0SMD_PE2H~P2/BI0/F0SMD_PE2H~P_1431333153-42-42-29
/BI0/F0SMD_PE2H~P2/BI0/F0SMD_PE2H~P_2131859651-19-19-12
/BI0/F0SMD_PE2H~P2/BI0/F0SMD_PE2H~P_762806769-30-30-23
/BI0/F0SMD_PE2H~P2/BI0/F0SMD_PE2H~P_823461660-12-12-8
/BI0/F0SMD_PE2H~013/BI0/F0SMD_PE2H~01_1447333210-5-5-1
/BI0/F0SMD_PE2H~013/BI0/F0SMD_PE2H~01_467984396-1-10
/BI0/F0SMD_PE2H~013/BI0/F0SMD_PE2H~01_778806826-3-30
/BI0/F0SMD_PE2H~044/BI0/F0SMD_PE2H~04_1971989754-1-10
/BI0/F0SMD_PE2H~055/BI0/F0SMD_PE2H~05_1424511806-1-11

(more rows clipped - this is just the first few)

....these are likely the latter partitions in the table...but we will come back to that in a minute.   Looking at the output of sp_helpdb, the normal DB options for SAP are enabled:

ddl in tran,

allow nulls by default,

abort tran on log full,

enforce dump tran sequence,

allow wide dol rows,

deferred table allocation,

page compression,

allow incremental dumps,

deallocate first text page,

full logging for all

..which means the minimally logged bulk copy that Bret was thinking of *may* not be a player.  However, you mention that this cube is created from another - which likely was done with ins_bulk hint - but not sure if the insert/select was then done in batches or not - and if so, then it still might be a factor.

...secondly, most of the pages reclaimed were from data (indid=0) vs. index (indid >0) so, Mark's possible theory about index leaf space also does not seem to be indicated.

If we look at the before/after columns along with the delta, we can see....

index_nameindidpartition_namebefore databefore usedbefore reservedafter dataafter usedafter reserveddelta datadelta useddelta reserved
/BI0/F0SMD_PE2H0/BI0/F0SMD_PE2H_00000000622316128-1-1-8
/BI0/F0SMD_PE2H0/BI0/F0SMD_PE2H_00000001493,6443,64529,056414248-3,603-3,603-29,008
/BI0/F0SMD_PE2H0/BI0/F0SMD_PE2H_00000001505,4765,47743,808414248-5,435-5,435-43,760
/BI0/F0SMD_PE2H0/BI0/F0SMD_PE2H_00000001512,3262,32718,6086970432-2,257-2,257-18,176
/BI0/F0SMD_PE2H~P2/BI0/F0SMD_PE2H~P_143133315399100101575872-42-42-29
/BI0/F0SMD_PE2H~P2/BI0/F0SMD_PE2H~P_2131859651454652262740-19-19-12
/BI0/F0SMD_PE2H~P2/BI0/F0SMD_PE2H~P_762806769878895575872-30-30-23
/BI0/F0SMD_PE2H~P2/BI0/F0SMD_PE2H~P_8234616601415242316-12-12-8
/BI0/F0SMD_PE2H~013/BI0/F0SMD_PE2H~01_14473332101213167815-5-5-1
/BI0/F0SMD_PE2H~013/BI0/F0SMD_PE2H~01_46798439634162316-1-10
/BI0/F0SMD_PE2H~013/BI0/F0SMD_PE2H~01_7788068261011167816-3-30
/BI0/F0SMD_PE2H~044/BI0/F0SMD_PE2H~04_197198975434162316-1-10
/BI0/F0SMD_PE2H~055/BI0/F0SMD_PE2H~05_142451180634152316-1-11
/BI0/F0SMD_PE2H~055/BI0/F0SMD_PE2H~05_14793333241213167816-5-50
/BI0/F0SMD_PE2H~055/BI0/F0SMD_PE2H~05_8108069401011167816-3-30

Let's just use data....we drop ~3600 pages from one data partition alone.   If it is the bulk load aspect, then we would possibly be allocating 32 extents (unless you reduced this per Mark's suggestion) or 256 pages total for each bulk batch...which would mean that the load used minimally 14 batches - which, I supposed is doable...e.g. since you said this was BW compression, it is likey an aggregation of daily to weekly values or similar and they might have used different queries for each dimension or something.   It would have been nice to have all 3 snapshots - before load, before reorg/after load, after reorg - to see where data was actually inserted to to find out (if for instance) the before load started at 40 pages, increased to 3600 pages after load and dropped back down to 40 pages after reorg - then we would likely have a good culprit to look into (ins_bulk and preallocated extents)....

Can you run this query - before load, after load and after reorg (I don't think we need any other data - just the output from the query):

select index_name(db_id(),id,indid) as object_name,

    indid, name as partition_name,

    data_pages(db_id(),id,indid,partitionid) as 'data pages',

    used_pages(db_id(),id,indid,partitionid) as 'used pages',

    reserved_pages(db_id(),id,indid,partitionid) as 'reserved pages'

from syspartitions

where object_name(id)='/BI0/F0SMD_PE2H'

order by indid, partitionid

....slightly modified from before......

....then as Mark suggests, use sp_configure to reduce the 'preallocated extents' to 4 and then for the next load, run the same queries as above - it will be interesting to see if we get the same order of magnitude of empty space

former_member182259
Contributor
0 Kudos

Actually, change the query to include row count info:

select index_name(db_id(),id,indid) as object_name,

    indid, name as partition_name,

    data_pages(db_id(),id,indid,partitionid) as 'data pages',

    used_pages(db_id(),id,indid,partitionid) as 'used pages',

    reserved_pages(db_id(),id,indid,partitionid) as 'reserved pages',

    row_count(db_id(),id,partitionid) as 'row count'

from syspartitions

where object_name(id)='/BI0/F0SMD_PE2H'

order by indid, partitionid

Thanks.

Former Member
0 Kudos

Jeff, I was out of the office the last week. So I could only perform the actions now:

1. run the script you provided (with the row count info) before dataload

2. run the script after dataload

3. run the script after a reorganization of the table

4. reduce the value for 'number of pre-allocated extent' from 32 to 4

Tomorrow I will post the result after the nightly data load.

Regards,

Mark

Former Member
0 Kudos

Now I have the output of the script after the data load. I cannot interpret these numbers. Did changing the 'number of pre-allocated extent' help?

former_member182259
Contributor
0 Kudos

Did you forget the attachment???   I don't see it......

Former Member
0 Kudos
22.04.201423.04.201424.04.201425.04.2014
Reserved Total
7.431.7927.666.7685.204.1064.115.720
Used Total971.7221.001.024695.234560.130
Unused Total6.040.0696.665.7444.508.8723.555.590

Jeff, this is strange. I attached the file as usual and I can see the link for file BI0_F0SMD_PE2H.txt.zip and download it. I attach the latest version from today to this post. I have now idea why, but the overall table size declined today, while the reog happened on 23.04.2014 in the afternoon.

former_member182259
Contributor
0 Kudos

My bad ......fairly new to SCN and found it doesn't always display attachments from certain areas (e.g. on Conversations tab).....however, what we need is that query run:

1 - before the load

2 - after the load/before the reorg

3 - after the reorg.

The attachment looked like it was just one exec.....sorry to be such a pain - but would like to see where space is going and then being recovered from.

jgleichmann
Active Contributor
0 Kudos

Hey Jeff,

just look at the attachment with 45k from

object_namepartition_namedata pagesused pagesreserved pagesrow count
before reorg
/BI0/F0SMD_PE2H/BI0/F0SMD_PE2H_0000000157947494757579228035
after reorg
/BI0/F0SMD_PE2H/BI0/F0SMD_PE2H_000000015772738028035

object_namepartition_namedata pagesused pagesreserved pagesrow count
after load / before reorg
/BI0/F0SMD_PE2H/BI0/F0SMD_PE2H_000000016423153
/BI0/F0SMD_PE2H/BI0/F0SMD_PE2H_000000016520692070165526060
after reorg
/BI0/F0SMD_PE2H/BI0/F0SMD_PE2H_00000001641283
/BI0/F0SMD_PE2H/BI0/F0SMD_PE2H_00000001651617246069

object_namepartition_namedata pagesused pagesreserved pagesrow count
/BI0/F0SMD_PE2H/BI0/F0SMD_PE2H_000000016623163
/BI0/F0SMD_PE2H/BI0/F0SMD_PE2H_000000016720062007160485875
after reorg?
/BI0/F0SMD_PE2H/BI0/F0SMD_PE2H_0000000166????
/BI0/F0SMD_PE2H/BI0/F0SMD_PE2H_0000000167????

former_member182259
Contributor
0 Kudos

Extremely unlikely that this is due to compression.....  Am wondering if the loads are using array inserts or even with slow bulk load if we aren't getting into the same situation as was earlier described by Bret.....especially with the ins_bulk hints, etc.  Is there a commit frequency on these loads??  (e.g. commit every 100 rows).....it might be way too low.

As far as compression, it is the HK that does the page compression - if you check monOpenObjectActivity, the worst you will see is that the pending compression requests have overrun the queue and pages are uncompressed.   Given a 30-40% compression on average - can't see that (uncompressed) data suddenly shrinking to 1000x less.  

Former Member
0 Kudos

Hello Jens,

yes your assumption about my files is correct. My 45kb file (

What puzzles me now is that it looks like the problem is solved! I have no idea what fixed this, maybe it was this

Date
Reserved Total
Used Total
Unused Total
22.04.20147.431.792971.7226.460.069
23.04.20147.666.7681.001.0246.665.744
24.04.20145.204.106695.2344.508.872
25.04.20144.115.720560.1303.555.590
28.04.20143.318.016461.5842.856.432

Answers (2)

Answers (2)

Former Member
0 Kudos

Hello Mark,

can you tell me how you have done this "reorg rebuild" in ATM?

I have the same problem with this table.

Reserved Total: 47,1GB

Used Total: 5,8GB

Unused Total: 41,2GB

I have already changed "

Best Regards

Patrick

Former Member
0 Kudos

Hello Patrick,

I am not familiar with ATM, so I was running the "reorg rebuild" manually via isql.

There was also a SAP call about this issue. It took some time until I got the solution:

- upgrade ASE 15.7 to SP122 or higher

- update the DBSL to kernel 720 patchlevel 618 or higher

Regards,

Mark

jgleichmann
Active Contributor
0 Kudos

Hi Patrick,

if you want to use the ATM with thresholds and not only for one reorg than please refer to this document .

If you only want to reorg the table once than:

logon via isql with <sid>adm or syb<sid>:


isql -S<SID> -Usapsa -X -w999

use <database> (should be same as SID)

go

setuser 'SAPSR3' (or other schema user, SAPSR3 or SAP<SID> is standard)

set quoted_identifier on

set chained on

go

reorg rebuild <table>

go

Regards,

Jens

Former Member
0 Kudos

Hello,

thanks for your help.

I have successfully done the reorg rebuild via isql.

I will then update the database / kernel as soon as possible to prevent this growth.

btw. the growth is about 500mb per day.

Best Regards

Patrick

0 Kudos

Dear Mr Foerster,

What ASE version are you on? Which DB option have been set ? And finally , can you paste the output of sp_help for  [SMP.SAPSR3./BI0/F0SMD_PE2H].

There is an issue with tables with LOB columns. Whne LOB columns get updated to a NULL value then ASE allocates an empty 16 K LOB page (for some compatibility reasons).
This happesn only when data base option 'deallocate first text page' is NOT set.

(this optioon is available as of 15.7 SP50 and higher if I am correctly informed.)

In ASE version <15.7 SP50 , please call: 

exec sp_chgattribute '<TABLENAME>', 'dealloc_first_txtpg' ,1

to set it for individual tables

(if that could be the issue).

Compare also SAP note

1780367 - SYB: Space allocation for table MES_DB_AGGREGATE is high

(for a different table though)

With kind regards

Tilman Model-Bosch

Former Member
0 Kudos

This is Sybase ASE 15.7.0.110. The mentioned table is a SAP InfoCube fact table. Fact tables never contain LOB data, they are composed of int and decimal columns. The SAP note 1780367 is about updated LOB columns. On the fact table, there are only inserts performed, no updates or deletes. Therefore I think the note doesn't help. I am just puzzled what causes Sybase ASE to allocate 7 times more space than required.

Name        Owner  Object_type Object_status                                                        

--------------- ------ ----------- ---------------------------------------------------------------------------

/BI0/F0SMD_PE2H SAPSR3 user table  deallocate first text page, page level compressed, contains compressed data            

(1 row affected)

Column_name                                         Type                    Length              Prec        Scale           Nulls           Not_compressed                                      Default_name                                                                            Rule_name                       Access_Rule_name                                            Computed_Column_object                                                              Identity

-------------------------------------------------------- ---------------------------- ------------------------ ---------------- -------------------- -------------------- -------------------------------------------------------- -------------------------------------------------------------------------------------------- ------------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------------------------------- ----------------------------------------

KEY_0SMD_PE2HP                                      int                          4              NULL         NULL               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
KEY_0SMD_PE2HT                                      int                          4              NULL         NULL               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
KEY_0SMD_PE2HU                                      int                          4              NULL         NULL               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
KEY_0SMD_PE2H1                                      int                          4              NULL         NULL               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
KEY_0SMD_PE2H2                                      int                          4              NULL         NULL               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
KEY_0SMD_PE2H3                                      int                          4              NULL         NULL               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
KEY_0SMD_PE2H4                                      int                          4              NULL         NULL               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
KEY_0SMD_PE2H5                                      int                          4              NULL         NULL               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
KEY_0SMD_PE2H6                                      int                          4              NULL         NULL               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
KEY_0SMD_PE2H7                                      int                          4              NULL         NULL               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
KEY_0SMD_PE2H8                                      int                          4              NULL         NULL               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
KEY_0SMD_PE2H9                                      int                          4              NULL         NULL               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
SMD_SUM                                             decimal                      9                17            3               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
SMD_MIN                                             decimal                      9                17            3               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
SMD_MAX                                             decimal                      9                17            3               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
SMD_COUN                                            decimal                      9                17            3               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
SMD_PERI                                            decimal                      9                17            3               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0
SMD_KEY                                             int                          4              NULL         NULL               0                        0                                      REPOLOAD_MACH_656002337                                                                 NULL                            NULL                                                        NULL                                                                                         0

Object has the following indexes

index_name                                                          index_keys                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  index_description                                               index_max_rows_per_page                                                                 index_fillfactor                                            index_reservepagegap               
index_created                                                           index_local

------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ---------------------------------------------------------------- --------------------------------------------------------------------------------

---------------------------------------------------------------------------- --------------------------------------------
/BI0/F0SMD_PE2H~P                                                    KEY_0SMD_PE2HT, KEY_0SMD_PE2H1, KEY_0SMD_PE2H2, KEY_0SMD_PE2H3, KEY_0SMD_PE2H4, KEY_0SMD_PE2H5, KEY_0SMD_PE2H6, KEY_0SMD_PE2H7, KEY_0SMD_PE2H8, KEY_0SMD_PE2H9, KEY_0SMD_PE2HU, KEY_0SMD_PE2HP                                                                                                                                                                                                                                                                                                                             nonclustered                                                                          0                                                                                0                                                               0               
Apr  7 2014  1:20PM                                                     Local Index
/BI0/F0SMD_PE2H~01                                                   KEY_0SMD_PE2HP                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             nonclustered                                                                          0                                                                                0                                                               0               
Apr  7 2014  1:20PM                                                     Local Index
/BI0/F0SMD_PE2H~04                                                   KEY_0SMD_PE2H1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             nonclustered                                                                          0                                                                                0                                                               0               
Apr  7 2014  1:20PM                                                     Local Index
/BI0/F0SMD_PE2H~05                                                   KEY_0SMD_PE2H2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             nonclustered                                                                          0                                                                                0                                                               0               
Apr  7 2014  1:20PM                                                     Local Index
/BI0/F0SMD_PE2H~06                                                   KEY_0SMD_PE2H3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             nonclustered                                                                          0                                                                                0                                                               0               
Apr  7 2014  1:20PM                                                     Local Index
/BI0/F0SMD_PE2H~07                                                   KEY_0SMD_PE2H4                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             nonclustered                                                                          0                                                                                0                                                               0               
Apr  7 2014  1:20PM                                                     Local Index
/BI0/F0SMD_PE2H~08                                                   KEY_0SMD_PE2H5                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             nonclustered                                                                          0                                                                                0                                                               0               
Apr  7 2014  1:20PM                                                     Local Index
/BI0/F0SMD_PE2H~09                                                   KEY_0SMD_PE2H6                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             nonclustered                                                                          0                                                                                0                                                               0               
Apr  7 2014  1:20PM                                                     Local Index
/BI0/F0SMD_PE2H~10                                                   KEY_0SMD_PE2H7                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             nonclustered                                                                          0                                                                                0                                                               0               
Apr  7 2014  1:20PM                                                     Local Index
/BI0/F0SMD_PE2H~11                                                   KEY_0SMD_PE2H8                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             nonclustered                                                                          0                                                                                0                                                               0               
Apr  7 2014  1:20PM                                                     Local Index
/BI0/F0SMD_PE2H~12                                                   KEY_0SMD_PE2H9                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             nonclustered                                                                          0                                                                                0                                                               0               
Apr  7 2014  1:20PM                                                     Local Index

(11 rows affected)

No defined keys for this object.

name                                                                                                type                                partition_type                                      partitions                          partition_keys

-------------------------------------------------------------------------------------------------------- ---------------------------------------- -------------------------------------------------------- ---------------------------------------- --------------------------------------------------------

SMP.SAPSR3./BI0/F0SMD_PE2H                                                                          base table                          range                                                      109                          KEY_0SMD_PE2HP

(1 row affected)

Avg_pages   Max_pages   Min_pages   Ratio(Max/Avg)         Ratio(Min/Avg)

----------- ----------- ----------- --------------------------- ---------------------------

68   4169      1              61.308824               0.014706

Table LOB compression level 100

Lock scheme Datarows

The 'ascinserts' attribute is not applicable to tables with datarow or datapage lock schemes.

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts

------------ -------------- ---------- ----------------- ------------ -----------

1         0     0            0       0      0

(1 row affected)

concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg

------------------------- --------------------- -------------------

15                0              1

(return status = 0)

former_member182259
Contributor
0 Kudos

Are you positive there are no deletes???   Can you post the results of the following:

select ObjectName, IndexID, RowsInserted, RowsDeleted, RowsUpdated, HkgcOverflows, HkgcOverflowsDcomp

  from master..monOpenObjectActivity

  where ObjectID=object_id('<SID>.SAPSR3./BI0/F0SMD_PE2H')

...substitute <SID> with your real database name

Reorg compact and reorg rebuild work a bit differently.   Reorg compact (as well as reorg reclaim_space) work within a single extent (8 physical pages - 128KB for SAP).   So, as the reorg command does garbage collection within each extent, it moves rows up to fill in unallocated or free'd space (due to deletes or updates) on that page - if the extent is pretty sparse - e.g. one row per page, then all the rows will likely be moved to the first page - BUT - since the extent still belongs to the object, it will still be counted against it as space - hence the difference between Reserved vs. Used in sp_spaceused.   End result is that after running reorg compact, is you could have oodles of space in which the first page or two of each extent has data, but the rest is blank.

Reorg rebuild copies the entire table - essentially row-by-row - ala a select/into.   As a result, it can eliminate the holes that reorg compact/reclaim_space leave behind within each segment.

Now, the obvious question is, how did the space get there???   Wellllll.....there are three ways:

1 - the space was preallocated as part of the original row creation

2 - the space was created by holes by deletions or updates (which shrank data values)

3 - the original inserts overran the HK ability to do page compression and was written uncompressed - and the reorg compressed the data.

First, let's deal with #3 - if you had a lot of character data, I could see it being a factor - especially if the shrinkage was in the 30-40% range.   However, you are talking 700%....and with mostly numeric data, I just don't see that.

#1 is a bit weird.....I could see it if someone did an sp_configure 'default exp_row_size percent', 100 before the load and then set it back afterwards....but that would be just too weird.   Otherwise, space is allocated based on the max of exp_row_size for the table and however much data is originally inserted - whether original inserts or reorg. And given that your table has a default of 1 for this, I don't see this causing any change.   Also, the index fill factor could be a consideration, but again, it would be consistent much like exp_row_size.

Sooooo....that leaves updates and deletes.   Remember, ASE will contiguously pack rows on a page with the start of one row right after the end of the other row (less exp_row_size preallocation).   A subsequent update setting a value to NULL or a subsequent delete will create a hole obviously.  An update that causes a forwarded row will also cause space to be made available on that page - but it should have been caught with reorg reclaim_space.....    One consideration is that all SAP tables are created without clustered indexes, which make them heap tables - so a huge delete/reinsert would in effect copy rows from scattered points to the end of the table, creating huge holes.  You might not see it based on row counts.....but......

Former Member
0 Kudos

Jeff, you are right. There were some deletes:

select ObjectName, IndexID, RowsInserted, RowsDeleted, RowsUpdated, HkgcOverflows, HkgcOverflowsDcomp

from master..monOpenObjectActivity

where ObjectID=object_id('SID.SAPSR3./BI0/F0SMD_PE2H')

ObjectName         IndexID RowsInserted RowsDeleted RowsUpdated HkgcOverflows HkgcOverflowsDcomp

------------------ ------- ------------ ----------- ----------- ------------- ------------------

/BI0/F0SMD_PE2H          0      1262461       14028           0             0                  0

/BI0/F0SMD_PE2H~01       3        35307       14028           0             0                  0

/BI0/F0SMD_PE2H~05       5        35307       14028           0             0                  0

/BI0/F0SMD_PE2H~09       9        35307       14028           0             0                  0

/BI0/F0SMD_PE2H~07       7        35307       14028           0             0                  0

/BI0/F0SMD_PE2H~P        2        35307       14028           0             0                  0

/BI0/F0SMD_PE2H~04       4        35307       14028           0             0                  0

/BI0/F0SMD_PE2H~11      11        35307       14028           0             0                  0

/BI0/F0SMD_PE2H~12      12        35307       14028           0             0                  0

/BI0/F0SMD_PE2H~10      10        35307       14028           0             0                  0

/BI0/F0SMD_PE2H~08       8        35307       14028           0             0                  0

/BI0/F0SMD_PE2H~06       6        35307       14028           0             0                  0

This night there was another data load. The situation after my "reorg rebuild" was this:

name        
   rowtotal reserved   data       index_size unused

--------------- -------- ---------- ---------- ---------- ----------

/BI0/F0SMD_PE2H  1227376  458112 KB   51424 KB  171248 KB  214896 KB

And now after the data load I see:

name           rowtotal reserved   data       index_size unused                  

--------------- -------- ---------- ---------- ---------- ----------

/BI0/F0SMD_PE2H  1244079 1812160 KB  218800 KB  176672 KB 1395568 KB

Space is wasted once again on a big scale. What can I try? Perform a "reorg compact" each night on this table?

former_member182259
Contributor
0 Kudos

I agree with Mark - however more than just what he asked for, could you post sp_help on the table again as an attachment - I could barely read the last one......  But what I think Mark & I both would like to see is three sets of data

sp_spaceused ..., 1 + systabstats + monOpenObjectActivity (add PagesWritten column)

....

data load

....

sp_spaceused ..., 1 + systabstats + monOpenObjectActivity (add PagesWritten column)

...

reorg rebuild ....with online

...

sp_spaceused ..., 1 + systabstats + monOpenObjectActivity (add PagesWritten column)

If it is empty index space as Mark suggests, then it likely is simpler to run a reorg on the index vs. the table.  The number of deletes you have seen are not nearly enough.   What I suspect is that due to the nature of index leaf pages, when you do the inserts, you are getting a lot of page splits on index leafs.....SAP is notorious for having a lot of low cardinality values (e.g. MANDT) - in every index (e.g. MANDT)....and inserting a ton of rows would cause overflow chains or page splits at the leaf....perhaps changing the index fillfactor might help if true.....but let's take a look at the data

former_member188958
Active Contributor
0 Kudos

Another possible factor is that the load process might be using minimally-logged bcp with a small batch size.  Each batch is allocated new extent(s) to insert data onto, if the batch is only large enough to fill one page that would leave 7 unused pages for each used page.

former_member182259
Contributor
0 Kudos

Don't remove the partitioning yet - BW uses that to more easily perform maintenance.   ....and to Mark's point, SAP on ASE always uses 16K pages.    I am wondering something - instead of monOpenObjectActivity, could you do the same before/after queries with monOpenPartitionActivity???     Also, could you run:

select index_name(db_id(),id,indid), indid, name as partition_name,

    data_pages(db_id(),id,indid,partitionid) as 'data pages',

    used_pages(db_id(),id,indid,partitionid) as 'used pages',

    reserved_pages(db_id(),id,indid,partitionid) as 'reserved pages'

from syspartitions

where object_name(id)='/BI0/F0SMD_PE2H'

order by indid

before/after load/reorg

...and could you also run sp_helpdb <SID>

There are deletes happening - but nowhere near the volume to cause that much space.   Reducing the number of preallocated extents as Mark suggest would be interesting as it might prove what Bret was saying by reducing the amount of space create/pre-allocated but never used.

AFAIK (and not a BW person - more ERP), BW loads into a single partition each time - if it has to redo a BW process, it truncates the partition and reloads it.  However, I am wondering if during the load, it isn't specifying the partition it is loading into - and then as Mark suggests, it is preallocating space on all the partitions when in fact it is only loading into one.....if so, like I said earlier, you could run the reorg on just the indexes vs. entire table - it would be a LOT faster.

lbreddemann
Active Contributor
0 Kudos

Hi Mark,

Sorry I've no idea on BW on ASE nor do I personally know anybody working on that.

As it happens, once the HANA blinkers are on, quite a bit can pass by unnoticed...

- Lars