SQL Server x64 - Pagefile size is maxing out.

Question

SQL Server x64 - Pagefile size is maxing out.

Viewing 7 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 1

Again - this is just one persons personal observations, but it is a person whose opinion I respect (a lot) 🙂

http://blogs.msdn.com/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

Found a long info knowledge based article titled "How to reduce paging of buffer pool memory in the 64-bit version of SQL Server" that appears to apply. http://support.microsoft.com/kb/918483

The article has separate sections for Windows Server 2003 and Windows Server 2008 that describe different solutions.

SQL = Scarcely Qualifies as a Language

bret.quackenbush 86227 SSC Veteran Points: 284 More actions · Answer 3

Well after applying SP3 again, we made some changes to our setup.

1) Created a Domain User to run SQL Server and Agent services under, and changed the account from the System Account to the Domain User.

2) Added the Domain User to the Local Admin group on the server and granted the user right to 'Lock Pages in Memory'.

3) Reduced the amount of RAM SQL Server was configured for from 20GB to 10GB of RAM.

After rebooting the server and watching the pagefile.sys for the last 4 hours, I am happy to say that the pagefile has not gone over 2.5GB. Apparently, SQL server was grabbing the MAX end of Memory leaving the OS with no memory resources other than the pagefile.

Many thanks for all who help, hope this information might benefit someone else.

Regards, Bret.

bret.quackenbush 86227 SSC Veteran Points: 284 More actions · Answer 4

One other note, if you a running SQL Server Std x64, in order to get the 'Lock Pages in Memory' feature to work you must be on SP3.

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 5

bret.quackenbush 86227 (3/9/2010)
One other note, if you a running SQL Server Std x64, in order to get the 'Lock Pages in Memory' feature to work you must be on SP3.

And CU4 - the lock pages in memory feature was added in the cumulative update.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs