Thursday, April 30, 2009

Another Reason To Use 4K Pages

Folks who don't (or aren't allowed to) use port 119 NNTP client software such as Forte Agent to browse the SQL Anywhere newsgroups at forums.sybase.com, and who also refuse (like I do) to use the execrable web interface to those newsgroups, are really missing out on some valuable content.

Such as the occasional reply by Ivan Bowman. Here's a recent example:


If the database page size is smaller than 4K (the O/S page size), then sequential scans will also allocate two 64K contiguous buffers to perform hinting (DiskReadHint / DiskReadHintPages). With page sizes larger than 2K, contiguous allocations are not needed on Win32 (scattered reads are used reading into non-contiguous pages in the cache). It is more efficient to use scatter reads because the data does not need to be moved from the contiguous buffer to the cache pages, and also larger hints can be performed (up to 16M instead of 64K). For this reason among others, I would try to use 4K pages or larger unless there is a compelling reason not to do so.

The problem could show up with query plans that use a number of sequential scans. Parallel execution plans could make this more likely to cause a problem because they could have up to 8 (in your case, 2xquad core) times as many sequential scans active.

Ivan T. Bowman
SQL Anywhere Research and Development
[Sybase iAnywhere]
If Ivan had a blog (he doesn't, as far as I know) it would be one of the best... he knows what he's talking about, AND he knows how to write to be understood, a killer combination in this age of twitterilliteracy.

If Ivan had a blog, it would surely be one of the Featured Few on the Sybase Blog Center web page... well, maybe, maybe not, who knows how those choices are made...

...but I can guarantee it would have a place of honor on right here, in the "FOCUSING ON SQL ANYWHERE..." list over to the right.

If Ivan had a blog, that is.

No comments: