我们如何更改SQL Server的页面大小？ [英] How do we change the page size of SQL Server?

问题描述

http://msdn.microsoft.com/en-us/library/ ms190969.aspx ：

在SQL Server中，页面大小为8 KB。这意味着SQL Server数据库
每兆字节有128个页面。

1）是由于硬件限制，选择为8 KB，还是因为8 KB的页面大小被认为是大多数用例的最佳性能？

2）我们如何更改页面大小，比如说4 KB或12 KB？

解决方案

简短的答案： / p>

由于寻址效率的原因（ TLB ）和IO的效率（请参阅阅读自或使用分散聚集方案写入文件），数据库页面大小必须是操作系统页面大小的一倍，这是由平台硬件体系结构驱动的。 x86架构的页面大小为4k（请参阅 x86维基文章上的侧边栏，或参阅< a href =http://www.intel.com/products/processor/manuals/ =noreferrer>英特尔软件开发人员手册），较新的架构提供大页面，SQL Server利用它们进行内存分配，请参见 SQL服务器和大页面解释。

由于数据库文件可移植性的原因，磁盘文件格式不能更改以适应现代CPU大页面，能够读取旧硬件上的数据库。经验表明，由于物理RAM碎片（处理器页面必须映射到连续的硬件页面），操作系统启动后，1MB页面很难分配。这样的大页面不能与WAL搭配使用（请参阅 ARIES ）。

http://msdn.microsoft.com/en-us/library/ms190969.aspx:

In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte.

1) is it due to a hardware restriction that page sizes are chosen to be 8 KB, or is it because a page size of 8 KB is deemed to be the best for the performance of a majority of use cases?

2) how do we change the page size, say to 4 KB or 12 KB?

解决方案

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).

这篇关于我们如何更改SQL Server的页面大小？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！