January 24, 2012 at 1:30 pm
According to Mark Russinovich's Windows Internals 5th Edition, Windows uses (small) page sizes of 4KB on both x86 and x64 architectures to manage memory, so intuitively I'd think SQL Server would use the same size for its own pages. Instead, it uses page sizes of 8KB.
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 8KB 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 disk storage, but I'm not sure. (NTFS has a default cluster size of 4KB)
Thank you.
January 24, 2012 at 2:07 pm
This is a good question. I'm not really sure but I'd be willing to bet the answer lies somewhere within the storage subsystem and not with the way windows manages memory. That's just a hunch though.
January 25, 2012 at 2:19 am
nice blog on this topic.
http://sqlblog.com/blogs/linchi_shea/archive/2008/03/03/is-the-8kb-page-obsolete-or-aging.aspx
January 25, 2012 at 3:51 am
Yup, I've read it some time ago. It's nice indeed.
It still does not answer some of my questions, though.
As far as I can see, there's not much information available on this topic. It's not really that much important in day-to-day SQL Server administration, so I can see why... Still, I'd love to know the answer to those questions out of curiosity.
Well, I'll keep looking 🙂
January 26, 2012 at 6:48 am
The page size is 8k, because that is the maximum size of a record in SQL Server (8096 I believe). However, when data is read, it is read in 64k chunks called extents (8 - 8k pages at a time).
It is my understanding that if you are doing a normal read/write database, it is better to do it in smaller block sizes, because the disks do better writing smaller amounts at a time. However, if you were doing a major read database, like a data warehouse, it is better to do block sizes closer to 64k, so that it reads in as much on a single pass as possible. Is much faster. Just do some more research on Windows and SQL Server default block size.
For checking individual disk systems for the optimum block sizes, you can run the Microsoft process called SQLIO at different block sizes, and the results will let you know which block size is the best. Then you can format the drives for that block size and then start using them. This assumes you have new disks to play with, of course.
January 26, 2012 at 7:36 am
vikingDBA (1/26/2012)
The page size is 8k, because that is the maximum size of a record in SQL Server (8096 I believe). However, when data is read, it is read in 64k chunks called extents (8 - 8k pages at a time).
I don't think so. I believe it's actually the opposite. The maximum size of a record in SQL Server is 8096 (actually less, since these pages have headers) because the page size is 8 KB. Should the page size increase, the maximum record size would increase as well (that's what happened when SQL Server moved from 2 KB pages to 8 KB pages between versions 6.5 and 7.0).
SQL Server 2000 I/O BasicsÂą
Microsoft SQL Server 2000 and Microsoft SQL Server 7.0 use 8-KB data pages and sector-aligned transaction log buffers. Earlier versions of SQL Server use 2-KB data and log pages.
According to MSDN:
SQL Server 2000 I/O BasicsÂą
The SQL Server database page size is 8 KB. Each page contains a header with fields such as Page Number, Object Id, LSN, Index Id, Torn bits, and Types. The actual row data is located on the remaining portion of the page. The internal database structures track the allocation state of the data pages in the database.
Understanding Pages and Extents²
In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.
Understanding Pages and Extents²
Rows cannot span pages, however portions of the row may be moved off the row's page so that the row can actually be very large. The maximum amount of data and overhead that is contained in a single row on a page is 8,060 bytes (8 KB). However, this does not include the data stored in the Text/Image page type. This restriction is relaxed for tables that contain varchar, nvarchar, varbinary, or sql_variant columns. When the total row size of all fixed and variable columns in a table exceeds the 8,060 byte limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit, starting with the column with the largest width. This is done whenever an insert or update operation increases the total size of the row beyond the 8060 byte limit. When a column is moved to a page in the ROW_OVERFLOW_DATA allocation unit, a 24-byte pointer on the original page in the IN_ROW_DATA allocation unit is maintained. If a subsequent operation reduces the row size, SQL Server dynamically moves the columns back to the original data page. For more information, see Row-Overflow Data Exceeding 8 KB.²
May 13, 2016 at 4:09 am
The value 8KB that MS SQL Server and many other databases chosen derived years of studies that concluded that 8KB or 16KB gives good overall performance balanced with efficient use of space. MS SQL mdf file always suffer from internal fragmentation and there is no way to defragment it.
To gain the best performance you should have filesystem cluster size 8/16KB, not less than the disk sector size as RAID system may present sector size as 4-64KB. You should align your disk partition to 8/16KB too. If you use iSCSI or LVM or Vault, you should make sure it align to that size too.
P/S: Made mistake in file extension previously and thanks for pointing it out.
May 13, 2016 at 4:21 am
Please note: 4 year old thread.
dbf files aren't SQL Server files either.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply