Large Pages and SQL Server

July 21, 2009 · Posted in SQL Server 

Bob Ward posted a good article on Large Pages and SQL Server support. There are a couple of things I had to do further research on so I thought I would pass it one. First, I wanted to understand the internals. Secondly, I want to know when to use it.

The Internals

This feature is actually part of the processor architecture and is implemented on a lot of operating systems. For example, this is a common optimization when running Oracle on linux.

So here are the internals that I have translated for me to understand keeping SQL Server in mind.

300px-Page_table_actions.svg[1] Without large pages, Windows x64 has 4KB pages. This means that if your buffer pool is 16GB, you will have 4,194,304 page table entries (PTEs). The page table is used to translate mappings of virtual addresses to physical addresses. As you can imagine, there is overhead in maintaining PTEs and the overhead increases with the size of the buffer pool.

Picture Source: Wikipedia

When Large Pages is enabled for the buffer pool, windows uses 2MB pages so the same 16GB buffer pool will have 8192 PTEs. That means the overhead(CPU and memory) will be reduced. It also means that the translation lookup aside buffer is smaller and there is much less of a chance for a cache miss\page fault.

When to use it?

I am having a hard time thinking of cases when you don’t want large pages for the buffer pool if your configuration supports it and the server is dedicated for SQL Server. It is recommended by Microsoft for TPC-E and TPC-C. I know… Not all TPC settings are meant for production.

There may be times where it does not help like if you have a lot more processor time and cache than the workload needs. The slow startup side effect may also be more important than the performance gain encountered.

That said, you should definitely test before blindly applying this to production especially if you are doing it on this post. I am going to be testing and will post results. Plase let me know what your results are.

This content is published under the Attribution-Share Alike 3.0 Unported license.

Tags: , , , ,

Comments

  • I think you forgot to tell us where to find the article :-)
  • statisticsio
  • greglinwood
    You want to be 100% sure of your device drivers before using large pages b/c whilst it's a perf improvement option, it also increases the ability for device drivers to corrupt the o/s (larger pages are marked as read / write protection vs smaller pages enabling kernel code to be marked as read only protection exclusively).

    Hence, it's definitely good for benchmarking on very carefully controlled hardware / drivers, but not good for general population use "in the wild" on commodity hardware.
  • Name
    Assuming you have more than a couple of GB of memory, Windows will default to using large pages to map the kernel image, the PFN database and the initial nonpaged pool. Enabling large pages in SQL Server has no effect on this.
  • greglinwood
    Sure - but I'm just pointing out that I think it's not a great idea to leave write-access open to kernel memory, purely for the minor perf improvements large pages might bring. If system stability isn't enough of a reason, security should be.
blog comments powered by Disqus