SQL Anywhere 12.0.1.3895

I'm trying to use sa_table_page_usage() to see how many pages get freed up following a large delete from a single table. My workflow for this is:

  1. start up the database using dbeng12.exe
  2. 'SELECT * FROM sa_table_page_usage() ORDER BY tablename' and dump the results to a file
  3. DELETE some rows from a table and COMMIT
  4. repeat the SELECT from 2) and dump the results to a second file

I have found that although the results from 2) and 4) show that the pages used by my table have gone down and the 'Free pages' have gone up, the differences in the numbers involved are too small. If I then wait a bit (without doing anything else) and call sa_table_page_usage() again I see the much larger differences that I am expecting.

I tried calling 'CHECKPOINT' immediately after my DELETE but this did not change the above behaviour. I have also tried using the dbinfo.exe utility rather than calling sa_table_page_usage(), with similar results.

Is this delay in updating the results of sa_table_page_usage() expected and if so is there any way of forcing it to update the results before calling it?

[also, I tried to add 'dbinfo' and 'sa_table_page_usage' tags but do not have enough 'reputation points']

asked 12 Aug '13, 10:15

Luke's gravatar image

Luke
636112032
accept rate: 50%

edited 12 Aug '13, 10:23

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676


This behavior is most likely the result of the database cleaner.

For performance reasons, pages are not freed immediately when a large number of rows are deleted. Instead, the database server waits until an appropriate time (based on the number of pages that need to be freed, the amount of activity on the server, and various other factors) to perform these tasks.

The SQL statement SELECT DB_PROPERTY('CleanablePagesAdded')-DB_PROPERTY('CleanablePagesCleaned') will show the number of pages that need cleaning. You can use the statement call sa_clean_database() to manually invoke the cleaning.

permanent link

answered 12 Aug '13, 11:17

Mikel%20Rychliski's gravatar image

Mikel Rychliski
2.1k1641
accept rate: 30%

Thanks for the reply. I've just tried my test again but this time I made a call to sa_clean_database() after doing the DELETE. Sure enough my second call to sa_table_page_usage() now shows a much larger difference in 'Free Pages'.

I'm a little confused about the two DB properties you mentioned though. At the end of my process 'CleanablePagesCleaned' has a value of 13,661 yet the difference in 'Free Pages' from before/after the DELETE is 5,752,619. I think I was expecting these two values to be roughly the same.

(12 Aug '13, 13:14) Luke
Replies hidden

Hi Luke, how does SELECT DB_PROPERTY ( 'FreePages' ); change? sa_table_page_usage could contain a different statistic. CALL sa_db_properties(); might be the more appropriate function

(14 Aug '13, 08:39) Mikel Rychliski

Hi Mikel, just tried the process again, querying DB_PROPERTY ( 'FreePages' ) at each stage. The number of rows deleted would have gone up slightly this time due to the nature of the date-based DELETE

These are the values of DB_PROPERTY('FreePages'): Start (before DELETE): 6756 ; After DELETE: 35972 ; After sa_clean_database(): 5,837,723

I also ran sa_table_page_usage() at the end and it gave a value of 5,839,278 for 'Free Pages' so I would say that they appear to be looking at the same statistic.

At the end of the process 'CleanablePagesCleaned' gave a value of 12683

(19 Aug '13, 12:00) Luke
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:

×415
×8
×3
×2

question asked: 12 Aug '13, 10:15

question was seen: 828 times

last updated: 19 Aug '13, 12:02