There appear to be two kinds of table data page splits:

  1. A non-blob page split is caused when the non-blob columns in a single row INSERT won't fit in a single table page, or an UPDATE of non-blob columns causes a single row to no longer fit in the original table page.

  2. A blob split is caused when the data for a single blob column must be split into a separate page according to the wild and wooly rules for blob splitting.

In both cases, the split data is stored in an extension page and is counted in SYSTAB.ext_page_count as opposed to SYSTAB.table_page_count.

There don't appear to be any statistics, properties or stored procedures that break SYSTAB.ext_page_count into non-blob-versus-blob splits for a table that is subject to both kinds of splits (non-blob columns that grow in size plus blob columns that can grow beyond the split limit). For example, the sa_table_fragmentation() procedure reports the total number of page splits, not the breakdown.

Having a breakdown would be very helpful because performance improvement techniques like larger free space and larger page size are more likely to help with non-blob page splits than blob splits, whereas improving blob performance is a whole different area of study.

Note: The Help is silent on non-blob page splits being counted in SYSTAB.ext_page_count; in fact, the phrases "page split" and "extension page" do not appear together.

asked 14 Jan '15, 09:14

Breck%20Carter's gravatar image

Breck Carter
27.0k447618889
accept rate: 21%


Just to cite from that other FAQ - wellknown to you, methinks - where I had asked Ivan:

As to row splits:

In case a row split is a necessity because a row is that large that it simply won't fit on a single page (say, during its insert it has more than 4K (minus overhead) data in a database with 4K pages), will it occupy two (or more) continuous pages (still one base page and further extension pages) and therefore will be stored in an optimal fashion?

If my assumption is correct: Is there any means to distinguish such necessary row splits from those that are caused by inserting "small" data and then later "fill the real values" by updates? - I guess sa_table_fragmentation() would simply return a higher segs_per_row value in both cases...

and he has answered:

You can use values in SYS.SYSTAB to try to distinguish these two cases, but these values are only describing the entire table and won't really help if you have a lot of nicely behaved data and a small amount of different data.

Consider:

    select table_name, "count", "count"/table_page_count as rows_per_table_page,
       table_page_count, ext_page_count
    from sys.systab 
    where table_name in ('TRowFragments','TLongRows');

Here we estimate the number of rows per base table page. If the rows were large when inserted (large with respect to page size), the rows per page will be low, perhaps as low as one if most of the table rows are large. On the other hand, with rows that grow after insertion, the rows per page will be higher (perhaps 30 or so). The sa_table_fragmentation() tells you how many row segments need to be read to completely read a row.

Resume:

So, basically I would conclude that you are correct: There's currently no reliable/unambiguous way to distinguish between both kinds of row splits.

permanent link

answered 14 Jan '15, 09:31

Volker%20Barth's gravatar image

Volker Barth
31.5k321462677
accept rate: 32%

edited 14 Jan '15, 09:39

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×249

question asked: 14 Jan '15, 09:14

question was seen: 595 times

last updated: 14 Jan '15, 09:39