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

asked 30 Nov '09, 16:57

Justin%20Willey's gravatar image

Justin Willey
6.5k106135203
accept rate: 21%


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.

permanent link

answered 09 Dec '09, 23:11

Dan%20Farrar's gravatar image

Dan Farrar
43143
accept rate: 80%

1

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

(10 Dec '09, 12:27) Breck Carter

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

(10 Dec '09, 13:37) Justin Willey

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

(11 Jan '12, 14:06) MarcosCunhaLima
Replies hidden

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

(11 Jan '12, 16:25) Volker Barth

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.

permanent link

answered 04 Dec '09, 18:18

Zote's gravatar image

Zote
1.7k364050
accept rate: 43%

edited 04 Dec '09, 18:55

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

(10 Dec '09, 13:38) Justin Willey

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

(10 Dec '09, 13:46) Zote

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

permanent link

answered 04 Dec '09, 11:28

Breck%20Carter's gravatar image

Breck Carter
27.1k424582831
accept rate: 21%

"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

(04 Dec '09, 17:46) Justin Willey
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:

×241
×8

question asked: 30 Nov '09, 16:57

question was seen: 7,717 times

last updated: 11 Jan '12, 16:27