In the earlier post regarding the Event ID 333 Errors and how to troubleshoot them. At the end of that post, we mentioned the "Lock Pages in Memory" user right. One of the things we mentioned was that if you have an x64 system, you should not enable this right. Over the last few months, we have seen a number of cases where x64 SQL servers have been exhibiting performance issues - either within the Operating System or within SQL. It's not always the Event ID 333 messages that we've been seeing either, so today we're going to give you the lowdown on Lock Pages in Memory - and whether or not you really need it. So, let's get started ...
The scenarios listed below are some typical scenarios that we work on with customers on a daily basis. For each of these scenarios, we are going to use a system with 8 processors, 32GB of RAM installed, and a 64-bit OS with 64-bit SQL installed:
- Scenario 1: SQL Maximum Memory is set to 30GB. The symptoms are that SQL Working Set is getting trimmed during large file copies and also during the backup process.
- Scenario 2: SQL Maximum Memory is set to 28GB. SQL is getting paged out during copy operations and when compression software is running its processes
- Scenario 3: SQL Maximum Memory is set to 30GB. This is on a SQL Enterprise Edition, and the "Lock Pages in Memory" right is configured. The Event Logs are filling up with Event ID 333.
Before we start digging into the troubleshooting, let's quickly review some important points regarding these scenarios. In the first two instances, the most likely cause of the problem is incorrect scaling or configuration. Moving on, the Event ID 333 indicates that we are unable to flush the registry, or perform read / write operations to the registry. As we mentioned in our Event ID 333 post, there are several possible causes. One common cause is memory pressure - you may also see Event ID 2019's or 2020's indicating a resource depletion or you may have a condition with low PTE's. However, this is not always the case, and we'll get into this a bit later on in the post. Something interesting to note is that when you are in a situation where you are experiencing Event ID 333, a memory dump may not be of any use - because we can't see what caused the Event 333. The only way to clear the event and restore operations is to reboot the server.
- Microsoft KB 918483 (Updated): How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005
- Slava Oks's Weblog: SQL Memory Manager and SQL Server Buffer Pool
Windows, Page Memory, Architecture, Memory Management, Performance, Windows Server 2008, Troubleshooting