After reading a lot of blogs about the issue of Paging Out in SQL Server 2005 Standard x64 under Windows Server 2003 Standard x64, I am wondering if it is needed in my server, which I will neeed to deploy in a couple of days. So until then, i really have no real world perf mon data yet. Contrary to every blog I read and from MS itself, SQL Server 2005 Standard Edition x64 can use Lock Pages in Memory feature, not being ignored as claimed. I was able to confirm this by finding it in SQL Event Log for 'using the lock pages in memory' and finding AWE being used when DBCC MEMORYSTATUS is ran. OS: Windows Server 2003 Standard Edition SP2 x64 SQL: SQL Server 2005 Standard Edition SP2 x64 build 9.00.3186 RAM: 4GB CPU: 2 Quad-Core Service Account SQL is running on: Local System (i know, it's not recommended but that's how the current policy is set up, until I have made my recommendation on Policy changes. 1. With the above specs, is there a need to be pro-active and follow MS recommendation to lock pages in memory? I am hesitant to apply this because SQL is running under the Local System account, which other services on the server are also running from. BTW, this is a dedicated SQL Server. and only the default instance. 2. If I Lock pages in memory for SYSTEM, will this not affect the overall performanc eof windows as other services running under SYSTEM, will have their pages locked also? 3. Is it really needed to set the maximum server memory, to say in my case, 3GB, if I set Lock Pages in Memory? I am hoping that setting Max Server Memory to default (dynamically configured), without locking pages in memory, will be a good decision at pre-deployment. Any thought will be highly appreciated.
NO as per this http://blogs.msdn.com/psssql/archiv...sign-the-lock-privilege-for-local-system.aspx blog.
I got excited about this blog, but when I double check my ErrorLog, I dont see lock pages being used. I have restarted my server, but still no log for it. It's probably the default when you are using EE, but to SE, you need to manually set it up. This leaves me to the original concern that, is there really a need for me to set it up when I only have 4GB of RAM on x64?