SQL Anywhere 184.108.40.20695
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:
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']
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
answered 12 Aug '13, 11:17