This is something that has come up from time to time on the newsgroups - ususlly with an answer along the lines of "suck it and see". I wondered if with the advent of far more detailed performance data obtainable easily through the tracing mechanism, guidelines could now be quantified in some way?
Presumably the options of much larger page sizes than even the newish default of 4k, were provided with a particular use in mind. Is it a matter of looking at the average row size of returned data sets or something like that?
asked 30 Nov '09, 16:57
The answer is still basically "suck it and see"... HOWEVER, there are some clues you can use to guess the flavour (performance) hidden inside:
There are two main performance-related properties of the database that depend on page size: index fanout and size of row that can be fit on a page.
Looking at the database file itself, the most important consideration is the depth of the indexes. The sa_index_levels() procedure will report on the depth of all indexes. If you have indexes of depth 3 or greater, and these indexes are on tables that are frequently used, you are likely to benefit from an increased page size.
To tell if the rows are too big to fit on the existing page size, use the following query: SELECT sum(table_page_count), sum(ext_page_count) from SYSTABLE;
If the table_page_count is not at least an order of magnitude higher than ext_page_count, you need to consider increasing your page size. If table_page_count is much greater than ext_page_count, and there are no important tables with indexes of depth 3 or greater, consider decreasing the page size.
You can find additional clues as to whether a page size change might be beneficial by looking at your database while it is running its typical workload. The sa_database_properties() procedure will give you the values you need (or alternatively, you can get them from the Windows Perfmon, or from a saved diagnostic trace).
Some of the clues available from these counters:
If CacheReadTable/DiskReadTable is less than about 20, and neither the index level nor extent page conditions hold, you may be thrashing the cache; consider decreasing your page size so that more important table pages have a better chance of staying in cache.
If the ratio of CacheReadIndInt/DiskReadIndInt is less than about 20, it indicates that the internal (i.e. non-leaf) pages of the indexes are not spending enough time in cache; you should consider decreasing the page size in this case.
Note that if you are able to declare a clustered index on your (big and/or important) tables, page size will become less important to overall performance.
answered 09 Dec '09, 23:11
Yes! And you need to evaluate this:
By default, here I'm using 8k. But I have 1 database with 16k.
I've never heard the phrase "suck it and see" but it expresses perfectly the advice I give out on this question. If there's another answer I'd love to see it too!
OK, now FAARBIGAA(1) has been satisfied... hint, hint.
(1) First Answer Attempt Required Before iAnywhere Gives Actual Answer
answered 04 Dec '09, 11:28