Today, we’re diving into a crucial feature of Microsoft SQL Server, Trace Flag (TF) 834, which could potentially speed up your database. As modern servers boast large amounts of memory, the idea of utilizing larger memory pages through this trace flag seems appealing. But should you really use it? Let’s explore.
What Does “Large Page” Mean?
A “Large Page” refers to using bigger blocks of memory all at once. This method is faster for the computer when it needs to allocate and use memory. Normally, Windows manages memory in 4Kb chunks on x64 systems, but with large pages, these chunks grow to at least 2Mb or even up to 16Mb. For a deeper dive, “Microsoft Windows Internals” by Mark Russinovich is a great resource.
The Benefits of Using Large Pages
One key advantage, as highlighted in the Temenos T24 – Trace Flag 834 and When to Use It – Microsoft Community Hub by the SQL Server Team, is that large pages can boost performance. They do this by making the translation look-aside buffer (TLB) in the CPU more efficient. In other words, TF 834 increases the efficiency of managing physical to virtual memory address translation that is performed by memory management hardware. If you’re looking to enable this feature, detailed instructions are available in the mentioned article.
The Downsides of Large Pages
Despite the seeming lack of drawbacks to using Trace Flag 834, caution is advised. Official guidance suggests avoiding it with columnstore indexes. Moreover, I’ve encountered scenarios where enabling large pages significantly increased kernel CPU usage, causing SQL Server to slow down dramatically. A server restart was the only workaround.
Real-Life Example
In one instance, kernel CPU usage skyrocketed from 2-3% to 30-35% upon enabling large pages after few hours of uptime, severely impacting SQL Server performance. The issue seems to extend beyond SQL Server itself, hinting at a deeper interaction with the OS kernel mode.
Part of the callstack to confirm that you have faced the very same issue:
...
|sqldk.dll!MemoryClerkInternal::FreePage
|sqldk.dll!MemoryClerkInternal::FreePageInline
|sqldk.dll!MemoryNode::FreePageInternal
|sqldk.dll!SOS_MemoryWorkSpace::FreePage
||-KernelBase.dll!VirtualFree
||-sqldk.dll!SOS_MemoryWorkSpace::DeCommitBlockWindows Performance Toolkit | Microsoft Learn can be used to capture a callstack.
The issue was solved by removing TF 834 from SQL Server startup parameters.
Conclusion
I’ve run into this problem with SQL Server 2019 and 2022, and it might be something others face too, no matter the version they’re using. This issue might look different on different servers, like those running Linux, because of how they manage memory. So, it’s really important to be careful when using Trace Flag 834 and to test it thoroughly.
Got any questions or want to share your own experience? Please leave a comment and let’s talk about it.
