up vote 0 down vote favorite
share [g+] share [fb]

According to Mark Russinovich's Windows Internals 5th Edition, Windows uses (small) page sizes of 4 KB on both x86 and x64 architectures to manage memory. Even though I can't find a reason for this, intuitively I'd think SQL Server would use the same size for its own pages but instead it uses page sizes of 8 KB.

I've researched through the documentation and blog posts, but I was unable to find an explanation for this behavior. All I was able to find was that it is the way it is.

I'm definitely not arguing that this is good or bad (and I'm in no position to do so, anyway), I'm just curious and would like to learn the motivation that culminated in this decision.

What is the technical reason for choosing a page size of 8 KB for SQL Server? Why is it a good page size? Or maybe it's just an inheritance from its Sybase lineage?

How does this decision affect SQL Server's performance (meaning how does it interact with the system's memory and disk internally)?

My second thought would be something related to the storage subsystem, but I'm still not sure. (NTFS has a default cluster size of 4KB)

Thank you.

link|improve this question
I've edited the question to provide a bit more background as to why I'm asking this question and to better reflect what kind of answer I'm looking for. – ivanmp Jan 24 at 23:57
Why was this question downvoted? – ivanmp Feb 14 at 15:21
feedback

1 Answer

up vote 3 down vote

My first thought would be something related to disk storage, but I'm not sure since I think NTFS has a cluster size of 4KB also.

No, it has a cluster size that is configurable and any sql server DBA with a grain of salt has put it to 64kb for the database file partitions.

As far as I know, Windows itself uses (small) page sizes of 4KB on both x86 and x64 architectures

No, it HAD a 4kb size then moved to 8kb to reduce overhead. They also are managed in extends of 64kgb (8 pages of 8 kb) which are always read and written at the same time. This means that a 64kb NTFS clsuter size for SQL Server is perfect ;)

At the end it is about aligning with aprtitions AND reducing overhead and that is where the sweet spot was assumed to be.

link|improve this answer
Well, it says on Windows Internals 5th edition that Windows has a small page size of 4KB for both x86 and x64 architectures. Do you have any reference that says Windows moved from 4KB to 8KB? Also, yes, just after posting I realized I should have said "default cluster size of 4KB". – ivanmp Jan 24 at 11:02
WIndows Internals? 8kb move as done in sql server. Not in windows. SQL Server moved to 8kb pages some versions ago. – TomTom Jan 24 at 11:21
3  
Regarding SQL Server, it has never moved from 4KB to 8KB as you said. It was 2KB in SQL 6.5 and earlier versions, and moved to 8KB in SQL 7.0 and later versions. – ivanmp Jan 24 at 11:35
It's not Windows that has 4 KB memory pages - it is the x86 architecture which has this page size, although there have been extensions allowing for larger page sizes. – syneticon-dj Jan 24 at 23:34
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.