on 04-07-2014 12:44 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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_name | indid | partition_name | data pages | used pages | reserved pages |
/BI0/F0SMD_PE2H | 0 | /BI0/F0SMD_PE2H_0000000062 | -1 | -1 | -8 |
/BI0/F0SMD_PE2H | 0 | /BI0/F0SMD_PE2H_0000000149 | -3,603 | -3,603 | -29,008 |
/BI0/F0SMD_PE2H | 0 | /BI0/F0SMD_PE2H_0000000150 | -5,435 | -5,435 | -43,760 |
/BI0/F0SMD_PE2H | 0 | /BI0/F0SMD_PE2H_0000000151 | -2,257 | -2,257 | -18,176 |
/BI0/F0SMD_PE2H~P | 2 | /BI0/F0SMD_PE2H~P_1431333153 | -42 | -42 | -29 |
/BI0/F0SMD_PE2H~P | 2 | /BI0/F0SMD_PE2H~P_2131859651 | -19 | -19 | -12 |
/BI0/F0SMD_PE2H~P | 2 | /BI0/F0SMD_PE2H~P_762806769 | -30 | -30 | -23 |
/BI0/F0SMD_PE2H~P | 2 | /BI0/F0SMD_PE2H~P_823461660 | -12 | -12 | -8 |
/BI0/F0SMD_PE2H~01 | 3 | /BI0/F0SMD_PE2H~01_1447333210 | -5 | -5 | -1 |
/BI0/F0SMD_PE2H~01 | 3 | /BI0/F0SMD_PE2H~01_467984396 | -1 | -1 | 0 |
/BI0/F0SMD_PE2H~01 | 3 | /BI0/F0SMD_PE2H~01_778806826 | -3 | -3 | 0 |
/BI0/F0SMD_PE2H~04 | 4 | /BI0/F0SMD_PE2H~04_1971989754 | -1 | -1 | 0 |
/BI0/F0SMD_PE2H~05 | 5 | /BI0/F0SMD_PE2H~05_1424511806 | -1 | -1 | 1 |
(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_name | indid | partition_name | before data | before used | before reserved | after data | after used | after reserved | delta data | delta used | delta reserved |
/BI0/F0SMD_PE2H | 0 | /BI0/F0SMD_PE2H_0000000062 | 2 | 3 | 16 | 1 | 2 | 8 | -1 | -1 | -8 |
/BI0/F0SMD_PE2H | 0 | /BI0/F0SMD_PE2H_0000000149 | 3,644 | 3,645 | 29,056 | 41 | 42 | 48 | -3,603 | -3,603 | -29,008 |
/BI0/F0SMD_PE2H | 0 | /BI0/F0SMD_PE2H_0000000150 | 5,476 | 5,477 | 43,808 | 41 | 42 | 48 | -5,435 | -5,435 | -43,760 |
/BI0/F0SMD_PE2H | 0 | /BI0/F0SMD_PE2H_0000000151 | 2,326 | 2,327 | 18,608 | 69 | 70 | 432 | -2,257 | -2,257 | -18,176 |
/BI0/F0SMD_PE2H~P | 2 | /BI0/F0SMD_PE2H~P_1431333153 | 99 | 100 | 101 | 57 | 58 | 72 | -42 | -42 | -29 |
/BI0/F0SMD_PE2H~P | 2 | /BI0/F0SMD_PE2H~P_2131859651 | 45 | 46 | 52 | 26 | 27 | 40 | -19 | -19 | -12 |
/BI0/F0SMD_PE2H~P | 2 | /BI0/F0SMD_PE2H~P_762806769 | 87 | 88 | 95 | 57 | 58 | 72 | -30 | -30 | -23 |
/BI0/F0SMD_PE2H~P | 2 | /BI0/F0SMD_PE2H~P_823461660 | 14 | 15 | 24 | 2 | 3 | 16 | -12 | -12 | -8 |
/BI0/F0SMD_PE2H~01 | 3 | /BI0/F0SMD_PE2H~01_1447333210 | 12 | 13 | 16 | 7 | 8 | 15 | -5 | -5 | -1 |
/BI0/F0SMD_PE2H~01 | 3 | /BI0/F0SMD_PE2H~01_467984396 | 3 | 4 | 16 | 2 | 3 | 16 | -1 | -1 | 0 |
/BI0/F0SMD_PE2H~01 | 3 | /BI0/F0SMD_PE2H~01_778806826 | 10 | 11 | 16 | 7 | 8 | 16 | -3 | -3 | 0 |
/BI0/F0SMD_PE2H~04 | 4 | /BI0/F0SMD_PE2H~04_1971989754 | 3 | 4 | 16 | 2 | 3 | 16 | -1 | -1 | 0 |
/BI0/F0SMD_PE2H~05 | 5 | /BI0/F0SMD_PE2H~05_1424511806 | 3 | 4 | 15 | 2 | 3 | 16 | -1 | -1 | 1 |
/BI0/F0SMD_PE2H~05 | 5 | /BI0/F0SMD_PE2H~05_1479333324 | 12 | 13 | 16 | 7 | 8 | 16 | -5 | -5 | 0 |
/BI0/F0SMD_PE2H~05 | 5 | /BI0/F0SMD_PE2H~05_810806940 | 10 | 11 | 16 | 7 | 8 | 16 | -3 | -3 | 0 |
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
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.
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
22.04.2014 | 23.04.2014 | 24.04.2014 | 25.04.2014 | ||
| 7.431.792 | 7.666.768 | 5.204.106 | 4.115.720 | |
Used Total | 971.722 | 1.001.024 | 695.234 | 560.130 | |
Unused Total | 6.040.069 | 6.665.744 | 4.508.872 | 3.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.
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.
Hey Jeff,
just look at the attachment with 45k from Apr 23, 2014 9:44 AM.
There is before load, after load and after reorg.
There were 2 new partitions/requests in the cube with bad usage of space. After the reorg this changed dramatically (factor 1000!). The indizes or the empty partitions are no topic on this issue. Just the new data partitions.
For example:
object_name | partition_name | data pages | used pages | reserved pages | row count |
---|---|---|---|---|---|
before reorg | |||||
/BI0/F0SMD_PE2H | /BI0/F0SMD_PE2H_0000000157 | 9474 | 9475 | 75792 | 28035 |
after reorg | |||||
/BI0/F0SMD_PE2H | /BI0/F0SMD_PE2H_0000000157 | 72 | 73 | 80 | 28035 |
Same as for the 2 new requests:
object_name | partition_name | data pages | used pages | reserved pages | row count |
---|---|---|---|---|---|
after load / before reorg | |||||
/BI0/F0SMD_PE2H | /BI0/F0SMD_PE2H_0000000164 | 2 | 3 | 15 | 3 |
/BI0/F0SMD_PE2H | /BI0/F0SMD_PE2H_0000000165 | 2069 | 2070 | 16552 | 6060 |
after reorg | |||||
/BI0/F0SMD_PE2H | /BI0/F0SMD_PE2H_0000000164 | 1 | 2 | 8 | 3 |
/BI0/F0SMD_PE2H | /BI0/F0SMD_PE2H_0000000165 | 16 | 17 | 24 | 6069 |
the new load with umber of pre-allocated extent', 4:
object_name | partition_name | data pages | used pages | reserved pages | row count |
---|---|---|---|---|---|
/BI0/F0SMD_PE2H | /BI0/F0SMD_PE2H_0000000166 | 2 | 3 | 16 | 3 |
/BI0/F0SMD_PE2H | /BI0/F0SMD_PE2H_0000000167 | 2006 | 2007 | 16048 | 5875 |
after reorg? | |||||
/BI0/F0SMD_PE2H | /BI0/F0SMD_PE2H_0000000166 | ? | ? | ? | ? |
/BI0/F0SMD_PE2H | /BI0/F0SMD_PE2H_0000000167 | ? | ? | ? | ? |
in my opinion the decrease of the allocated is not the right way to solve this issue.
The seems that the compression is not working correct or the order of the data in the affected pages are not optimal for these type of data.
A factor of 1000 is really hard and should not be.
I will search for this issue may be you hit a bug in this version.
Regards,
Jens
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.
Hello Jens,
yes your assumption about my files is correct. My 45kb file (Apr 23, 2014 9:44 AM) showes the state
1. before a dataload
2. after a new dataload
3. after the reorganiation
I have set the "number of pre-allocated" to 4, and the next file with 15 kb (Apr 25, 2014 2:15 PM) shows the state after the next nightly data load.
What puzzles me now is that it looks like the problem is solved! I have no idea what fixed this, maybe it was this setting "number of pre-allocated" to 4. Anyhow, the table size currently decreases from day to day:
Date | Reserved Total | Used Total | Unused Total |
---|---|---|---|
22.04.2014 | 7.431.792 | 971.722 | 6.460.069 |
23.04.2014 | 7.666.768 | 1.001.024 | 6.665.744 |
24.04.2014 | 5.204.106 | 695.234 | 4.508.872 |
25.04.2014 | 4.115.720 | 560.130 | 3.555.590 |
28.04.2014 | 3.318.016 | 461.584 | 2.856.432 |
Regards,
Mark
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 "number of pre-allocated" to 4 - but it seems this doesn't have any effect.
Best Regards
Patrick
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
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......
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?
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
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.
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.