THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Is the 8KB page obsolete or aging?

 

In one of their year 2000 papers—Rules of Thumb in Data Engineering, Jim Gray and Prashant Shenoy stated that, “Over the last decade, disk pages have grown from 2KB to 8KB and poised to grow again. In ten years, the typical small transfer unit will probably be 64KB, and large transfer units will be a megabyte or more.”

 

Should we expect the page size of SQL Server to grow? Or is the 8KB page size still optimal for SQL Server?  I remember discussing this very question with someone in the know about a year ago, and was told that the suggestion of using a larger page size in SQL Server was more BS than substance. The argument was essentially that SQL Server rarely incurs 8KB I/Os, and it always tries to ‘batch up’ I/Os into large transfers when it does such operations as read-ahead reads, checkpoints, lazy writes, and so on.

 

I’m not here to argue for or against using a single fixed 8KB page size in SQL Server. I’m simply not in a position to do justice to the topic. I’m sure Microsoft has been experimenting with different page sizes. At least, I sure hope so.

 

I just want to highlight some observations related to this topic.

 

First, note that SQL Server’s choice of a single fixed 8KB page is different from Oracle and DB2, which allow multiple page/block sizes. A single fixed page size can obviously lead to simplified code, but at the same time has the potential to introduce this fixed page size assumption throughout the code, making it difficult to accommodate a different page size should that becomes necessary.

 

Although the fact that it uses 8KB pages doesn’t necessarily mean that SQL Server would perform a lot of 8KB I/Os, it is not that rare to see SQL Server does 8KB I/Os in real world applications. This is especially true when there is fragmentation within a database.

 

Conceptually, it does seem that the larger the page size, the more row slots you can have on a page, and therefore fewer levels you may have for your indexes. Potentially, this can lead to fewer page splits and fewer physical I/Os. On the flip side, a larger page size may have a higher chance for running into unwanted concurrency issues, though this shouldn’t really be a problem with row-level locking. The other argument against a large page size is that if your workload typically requests a small chunk of data, getting a 32KB or even 64KB page into the buffer pool only to use a few short rows could be wasteful.

 

The introduction of database compression in SQL Server 2008—its dictionary compression in particular—seems to give some legitimacy to the question of whether the 8KB page starts to show signs of aging. For instance, with dictionary compression, it may work better with larger pages. Intuitively, the larger the page size, the more likely you may see repeating data patterns and the less overhead space the compression information structure may take. Oracle uses a similar block-level compression scheme and research and experience on the Oracle compression has shown that generally larger blocks tend to produce better compression ratios.

 

Now if you check the Oracle literature, you would find some rather heated debate on whether there is any value in using multiple block sizes in a real world app. Most agree that using a larger block size in data warehousing applications tend to result in better performance. But some have argued that the performance gain may not be worth the added hassle in managing multiple block sizes, and people may be better off just staying with the default block size, which happens to be 8KB.

 

So this is not a settled topic. It remains interesting to see whether Microsoft will change the page size or introduce multiple page sizes post SQL Server 2008.

 

Published Monday, March 03, 2008 8:07 PM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Scott R. said:

Linchi,

I agree with you that it is worth revisiting the database page size issue, and how it might be implemented in a given database instance and database product version.

To date, the only page size option in SQL Server was to use the non-configurable page size built into the product (2 KB in SQL 6.5 and earlier versions, and 8 KB in SQL 7.0 and later versions).  As you mentioned, other DBMS products have configurable database page sizes, with some allowing multiple database page sizes within a single database instance.  Having worked with such a database product in the pre-relational days, I can relate to both the flexibility benefits of a configurable page size and the potential disadvantages of managing multiple buffer pools with differing page sizes.

I see three likely product implementation paths to consider for SQL Server:

1.  Keep a single, non-configurable page size for all database instances (same as has been done to date), and wait for Microsoft to consider it worthy to change the non-configurable page size (as was done in the SQL 6.5 to SQL 7.0 transition) in a future database product version.  Easiest for Microsoft, but least flexible for the customer and differing database workloads.

2.  Change SQL Server to allow a single configurable page size for a given database instance, to be used by all databases hosted in that database instance (with a default page size of 8 KB for simplified compatibility).  This option allows customers to decide when and how to progress to different page sizes (larger or smaller) without having to wait for, coordinate with, or agree with Microsoft (future product releases, product maturity cycle, Microsoft’s preference for page size, etc.).  It also allows separate domain-specific database instances to use a page size appropriate for the domain workload being processed (for example: larger page sizes for data warehouse workloads and smaller page sizes for OLTP workloads).  This option may limit backup / restore and attach / detach flexibility across database instances, and possibly other common actions – the source and target database instances must be page-size compatible.

3.  Change SQL Server to allow a configurable page size at a database, database file, or database instance level (with a default page size of 8 KB for simplified compatibility).  This option offers the most flexibility, should multiple workloads with differing page size requirements need to be hosted by a single database instance (OLTP versus data warehouse, large tables or indexes within a given database, etc.).  It also introduces the issue of managing multiple buffer pools of differing page sizes, as you mentioned in your post.  I can’t speak for Oracle or DB2, but the challenges I faced with this buffer pool management issue were related to the manual allocation / reallocation of buffer pools and the ongoing management and optimization of limited memory for the various buffer pools (one buffer pool is heavily used, while another buffer pool is hardly used but is taking up lots of memory).  While there may be value in selective manual buffer pool management (on demand or by schedule based on known workload patterns – batch versus online time windows, etc.), the key success factor with this option is having strong automated buffer pool management, based on historical usage, heuristics, etc.

Since option 3 is a superset of the option 2, it is possible to use option 3 at just the database instance level (as with option 2) and not at the database or database file levels, to simplify management where appropriate.

My preference would be to eventually have option 3 available in SQL Server, with option 2 being an interim vendor implementation “stepping stone” until option 3 is possible (assuming that a configurable page size at a database instance level – without the automated management of multiple buffer pools with differing page sizes – may be relatively easier to implement than the full flexibility of option 3 and all that it entails – multiple differing sized buffer pools, automated buffer pool management, etc.).  If option 3 is possible in a single (not protracted) product version cycle, that would be best.

Both options 2 and 3 can allow customers to stay at the current 8 KB page size and migrate to other page sizes at their convenience, without the added dependency of migrating to a new database product version and the requisite product maturity waiting period (SP1, etc.) at the same time as the page size change.

The onset of allowing configurable page sizes (option 2 or 3) may also foster the need for richer database migration / reorganization tools to minimize the impact of changing the page size for existing databases (page expansion utilities – either in-place or direct copy old to new – as an alternative to traditional database unload / reload processes).  Page contraction (reducing the page size) for a given database / file may not be as easy to accomplish without traditional database migration / reorganization tools (unload / reload data, rebuild indexes, etc.).

I also assume that the changes required for options 2 and 3 may be on the level-of-effort order as the SQL Server database engine changes required for SQL 7.0 (fairly complex and involved), which has been the basis for all SQL Server product versions since then.  Given that the database compression features in SQL 2008 were implemented in a single product release cycle with minimal impacts to other database engine components (see http://blogs.mssqltips.com/blogs/chadboyd/archive/2007/10/15/katmai-sql-2008-data-compression-including-backup-compression.aspx), maybe option 3 is do-able in a single product version cycle!

Other thoughts on configurable database page sizes for SQL Server?

Scott R.

March 4, 2008 2:43 PM
 

DonRWatters said:

Since most of us use a 64KB file block size for I/O transfer, and the extent manager is basically dealing with this size anyway, doesn't that seem like the next logical step?

March 6, 2008 3:45 PM
 

David Lean said:

Implicit in your discussions above is an assumption that a) adding this feature would make performance better & b) Microsoft hasn't considered it. The SQL Server Team have a large performance testing group. It is reasonable to assume that they look at the benefits / overheads of such an option. (After all it is not new).

A big goal of SQL Server is to reduce TCO: eg SQL heavily  auto-tunes itself, adjusts buffer sizes, reorders disk I/O's to reduce head movement & dynamically adjusts memory etc. As you mention above, this can introduce a lot of complexity & potential for mis-configuration.

It is possible that adding this feature may just be one more option DBA’s can use to kill off performance. (Most Oracle sites I've visited seem to leave it alone. It is not uncommon to see people who've changed it, make perf worse & then burn time attempting to tweak memory & other params, trying to get the balance right).

Lets face it <5% of DB Sites; manage to configure their disks for optimum perf. Typically the Partition_Offset ÷ Stripe_Unit_Size (should be an integer for good perf). The Stripe_Unit_Size ÷ File_Allocation_Unit_Size (also evenly divisible)

Getting this right gives a perf difference from 15% - 250%. I'd tweak that before looking at block size.

In Short

I have no doubt that Microsoft could increase the page size without much effort. I'd expect they could also change the code to make it user configurable.

I'm less confident that it would benefit most SQL server customers & I suspect that if they put that same dev effort somewhere else we might see greater gains across the board. (ie Data Compression, Star Join Optimisation etc)

  I guess time will tell.

March 7, 2008 6:47 AM
 

Paul Z said:

Hey Linchi -- first you've got a great blog. I follow it a lot and your technically sound arguments and commentary are great to follow.

As for 8 KB. It isn't if it is obsolete or aging, it's more, what's are you after. I'm a DB2 expert and it supports 4,8,16, and 32 KB pages, and can integrate with large page support on various O/Ss as well.

In the release specificially designed for SAP, DB2 introduced a fixed page length option of 8 KB. When I talk to clients, sure the old addage works: 4 KB is better for OLTP and 32 KB for DWing. But you know what? These workloads are merging. Indeed I can get better performance (and better I/O for DWing which is important) with specilized pages, but that also adds complexity to the environment (for some databases which I won't mention here).

I've found SMB-type clients want a fixed page, that a trade-off that isn't to steep: in that case 8KB isn't that bad. Personally I also recommend clients use no more than 2 pages sizes for optimum results.

Now for your comment "...I can't spak for Oracle or DB2..."

One great feature in DB2 9 is called Self Tuning Memory Manager; this thing auto-tunes the shared memory heap and it's components (lock list, buffer pools, that kind of stuff). This thing solves that very issue you raise -- it's really cool. It can take 4 8 KB pages and move them to a starving 32 KB buffer pool to help perhaps spilling sorts, then it can move back that memory to help during TXal period and allocating more memory to the locklist. I love it...all this is transparent. What's even better is STMM actually understands memory requirements across separate instances or database on the same server! You know, those DBAs that are on a shared server that tell their instance they have 90% of the server's memory -- only 5 DBAs, each with an instance, say the same things. STMM is great for consolidation because of this.

Anyway -- not trying to plug DB2, just thought I'd boast about a terrific feature, but at the same time tell you that in DB2 land, there is a fork in the road to have a fixed page size. I guess we're all politicians. "What page size should I use?" ... It depends! :)

March 30, 2008 1:51 PM
 

Linchi Shea said:

David L;

Just want to set the record straight on point b) in "Implicit in your discussions above is an assumption that a) adding this feature would make performance better & b) Microsoft hasn't considered it."

As I stated in the post, I’m sure Microsoft has been experimenting with different page sizes.

May 5, 2008 10:14 AM
 

Linchi Shea said:

Paul Z;

"But you know what? These workloads are merging." That is increasingly true. Even though it's always easier to talk about the differences between OLTP and DW/reporting, for the past 10 years or so, I've found it has become difficult to classify many real-world workloads using this dichotomy. That said, if you are pushing the limit of a system, you probably don't want to deal with a mixed workload (if you have a choice).

May 5, 2008 10:22 AM
 

Denis Gobo said:

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog

December 31, 2008 10:37 AM
 

Flo said:

Any idea, why they came up with this 8kb thingy?

Why not 16kb?

Why not 4kb?

Or is it because on early 32-bit systems, 1st-level-cache of most CPUs was 8kb?

Regards

Flo

Lead-Developer / http://www.lelala.de

May 4, 2013 12:40 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement