Menu
Per MSDN,
In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte.
How do we change the page size, e.g. to 4 KB or 12 KB etc?
Also, is it due to an "innate hardware restriction" that page sizes are chosen to be 8 KB?
Or is 8 KB simply an arbitrary good-enough number chosen based on heuristics for optimization of majority use cases?
416
As mentioned, 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.
Expand Databases, right-click the database to increase, and then click Properties. In Database Properties, select the Files page. To increase the size of an existing file, increase the value in the Initial Size (MB) column for the file. You must increase the size of the database by at least 1 megabyte.
DB pages can be between 512 bytes and 64K bytes in size. The size that you select must be a power of 2. You set your database's page size using DB->set_pagesize() . Note that a database's page size can only be selected at database creation time.
To do this simply hold down the Ctrl button on your keyboard and with your mouse scroll the mouse wheel up to increase the magnification and scroll down to decrease it.
Short answer: you cannot change it.
For reason of efficiency of addressing (TLB) and efficiency of IO (see Reading From or Writing To Files Using a Scatter-Gather Scheme) the database page size must be some multiple of the OS page size, which is driven by the platform hardware architecture. x86 architecture has a 4k page size (see the sidebar on x86 Wiki article, or refer to the Intel Software Developer's Manuals), newer architectures offer large pages and SQL Server leverages them for memory allocations, see SQL Server and Large Pages Explained.
For reasons of database file portability the on-disk file format cannot be changed to accommodate modern CPU larger pages, it would break the capability to read the database on an older hardware. And experience shows that the 1MB page is quite difficult to allocate soon after OS start up, due to physical RAM fragmentation (a processor page must map to a contiguous hardware page). And such a large page would not play well with WAL (See ARIES).
50
SQL Server does not allow the page size to be changed - it is a fixed 8k in size with a fixed size header / data section.
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
