|
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:
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 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
(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
|
