Skip to Content

Table level statistics inaccuracy question

"Some statistics, particularly cluster ratios, may be slightly inaccurate because not all page allocations and deallocations are recorded during changes made by data modification queries. Run update statistics or create index to correct any inconsistencies."

I am curious in what condition table level statistics like DPCR became slightly inaccurate and why the document is mentioning 'not all page allocations and deallocations are recorded during changes made by data modification queries.' so i did some research from Note,google,old document.. and also raised to SAP support but I can only get partial answers without expaination, anyway below is my understanding/summary for Table level statistics inaccuracy based on my shallow knowledge, please correct me/share me some light on it:

For Table level statsitcs(pagecnt/SPUT/DPCR):

1. Flush happens periodically by Housekeeper process.

2. Flush can also be triggered before scavenge process, but it's not guaranteed as deadlock can be happened.

If deadlock happened and after scavenge, and if DES is gone from cache, the new pagecnt can't be flushed into systabstats to reflect new value.

3. If a spin-lock on DES and at same time, first oam page for table is exclusively latched, pagecnt will not be flushed to systabstats this time, but can be flushed next time.

4. For performance reasons, the Tally Manager works in a non-transactional fashion, which can allow for some inaccuracy for some stats like pagecnt/dpcr.

-->I guess some page split happens during inserting but pagecnt should be recorded. But during roll-back, for perfromance purpose, those deleted pages is recorded with estimate values.

5. DES not in cache because of crashes: the new pagecnt can't be flushed to systabstats to reflect new value.

6. DES not in cache because of roll-backs: the new pagecnt can't be flushed to systabstats to reflect new value.

But I can't imagine how DES can be lost during roll-back..

7. SPUT "Space utilization uses the average row size and number of rows to compute the expected minimum number of data pages, and compares it to the current number of pages."

datarowsize is populated with an estimate which may be inaccurate, particularly if have variable width columns (for instance NULLable columns).

8. reorg rebuild will update accurate pagecnt, but does not update datarowsize in systabstats.

9. Updating the table statistics improves the datarowsize accuracy.

10. Spinlock contention on DES can slow down ASE but it's not the cause of inaccuracy.

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on May 11, 2015 at 02:04 PM

    It sounds like it is referring to minimally logged operations like select into / (fast) bulk copy.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 12, 2015 at 02:07 PM

    This all depends on locking scheme, but I would say probably the greatest reason for Cluster Ratio inaccuracies over time come from the fact that dpagecrcnt/ipagecrcnt/drowcrcnt numbers are used in the calculation. Remember that cluster ratios are "derived" statistics. That is, they are derived at runtime from the other statistics in systabstats (which come from memory as you state). The "*crcnt" values are measures of how often an "extent jump" is done while scanning a chain of pages. I don't believe these are "in-memory" values that are kept up-to-date as DML occurs.

    Add to this that pagecnt/leafcnt/rowcnt values can also have some accuracy issues (although seems like in only special circumstances) as you describe above, and the logical conclusion is that if you want THE most accurate cluster ratio calculations, you need to update statistics for the table/index (without sampling) you are interested in (or rebuild/recreate indexes). I don't know of any other way to get *crcnt values updated than that.

    Would be nice to have an option, if for instance, a DML operation that scanned the table/index would update *crcnt (in memory to be flushed later) as part of the scan.

    Also, "rowsize" as you mention above is probably not maintained in memory either. Would only be updated with update stats/index creation.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 13, 2015 at 04:49 AM

    Thanks Jeff,Jason and Kevin!😊

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.