Some of us might have been in a situation of having SQL Server Memory paged out. This can lead to extreme performance problems. You could find yourself in a situation where users complain that around an application's performance becomes extremely slow. When you investigate further you find that around that same time frame SQL Server Error log contains errors like

date time spid1s A significant part of SQL Server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1086400, committed (KB): 2160928, memory utilization: 10%.

In a nutshell, this happens due to the fact that OS is not left with much memory to operate, as other programs have taken most of it (SQL Server generally having the biggest chunk). At this point OS starts to page out memory taken by these programs and starts to page it out to the page file. SQL Server becomes a victim and its memory is paged out by the OS creating all sorts of performance problems.

Microsoft recommends that you set the Lock Pages in Memory property. You have to set Lock Pages in Memory in the group policy editor of the OS, and you have to assign an Active Directory account that would have the ability to Lock Pages. You could do that by going to start > run > gpedit.msc and add your service account or group

In essence - what this does is that next time when the OS runs into an issue of not enough memory, and starts to page out memory taken by running programs - it would exclude the services/programs being run by the account you specified in the local policy. So if you set your SQL Server Service account to Lock Pages, then SQL Server hums along fine, but the other processes start to get their memory paged out. This is not a good thing, but it is still better than making SQL Server a victim.

Lock Pages in Memory is basically an OS feature, so you could set it up on any kind of server (file server, print server, database server e.t.c.), but for SQL Server to actually use it, it turns out to be an Enterprise Edition Only feature. So if you are stuck with a Standard Edition, and don't have the money to upgrade to Enterprise, you are stuck in poor man's land. All you could do was try to find out the root cause of this runaway memory use. You must start dissecting each byte of memory being used by whom and why, etc. All in all, it became a very tedious process. It could be an old buggy driver; it could be SQL Server itself, or something else.

This was an issue faced by many and I was one of them. I read somewhere that in April of 2009 there was a strong request made by an European SQL user group and some bloggers there to allow this feature to be present in Standard Edition. Microsoft listened and introduced this silently in SQL Server 2005 SP3 Cumulative Update 4. The poor man was happy now 🙂

To use this feature you would have to first install SQL 2005 SP3 CU4 and then manually enable a trace flag 845 in startup parameters of SQL Server and restart the service. It does not come enabled after the patch install.

This is pretty straight-forward in a standalone box. You go to the Service Configuration Manager and add it in startup parameters of the advanced tab there. But it gets a little tricky, when you are doing it on a cluster(check the article given below). To ensure that you have successfully enabled the trace flag, you would see an entry for it in SQL Log when the instance is starting up. It would show the -T845 trace flag enabled and would say "Using locked pages for buffer pool". Once you have that, you are done.

All the best and hopefully you would not find SQL Server's memory paged out.

Useful Microsoft Links for scope of this article.

Article ID: 970279 - Last Review: August 24, 2009 - Revision: 2.0
Cumulative update package 4 for SQL Server 2005 Service Pack 3
http://support.microsoft.com/kb/970279

Article ID: 970070 - Last Review: June 16, 2009 - Revision: 2.1
Support for Locked Pages on SQL Server 2005 Standard Edition 64-bit systems and on SQL Server 2008 Standard Edition 64-bit systems
http://support.microsoft.com/kb/970070/

Article ID: 953504 - Last Review: October 21, 2008 - Revision: 1.0
How to change SQL Server parameters in a clustered environment when SQL Server is not online
http://support.microsoft.com/kb/953504

Article ID: 918483 - Last Review: August 20, 2009 - Revision: 11.0
How to reduce paging of buffer pool memory in the 64-bit version of SQL Server
http://support.microsoft.com/kb/918483