What particular statistics should one look at to help decide the best database page size to use?

[justin_willey]

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?

Thanks

[Former Member · Accepted solution]

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.

[Breck_Carter · Accepted solution]

Doesn't sum(ext_page_count) get artificially skewed by blobs?

[justin_willey · Accepted solution]

very helpful - just the sort of thing I was looking for. Now I need to gt investigating! Thanks.

[former_SQLA_member1694893 · Accepted solution]

@Dan (or anyone which can answer) What is index fanout?

[VolkerBarth · Accepted solution]

Have a look here - note, it's called "fan-out" there...

[Former Member]

Yes! And you need to evaluate this:
- low page sizes, requires more I/O, but you need less RAM to cache data.
- high, uses less I/O, but requires more RAM to do cache.

By default, here I'm using 8k. But I have 1 database with 16k.

[justin_willey]

Thanks - Zote- was there anything in particular that made you choose 16k for that 1 database?

[Former Member]

@Justin just less IO since I have 10gb ram to that database cache.

[Breck_Carter]

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.

Breck

(1) First Answer Attempt Required Before iAnywhere Gives Actual Answer

[justin_willey]

"suck it and see" - Charming British expression referring to trying out something untested or kind of unknown. It's really a metaphor - the new thing is like a boiled sweet, the uknown consequences are the flavour, so you "suck it and see"! - www.urbandictionary.com