January 8, 2009 at 10:53 am
Hello,
I have seen this error message in the error log
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): 97848, committed (KB): 14700016, memory utilization: 0%.
How to diagnose which process is caused the paging. We using sql server 2005 enterprise edition 64 bit with sp2 and memory settings set to default and RAM is 16GB
January 8, 2009 at 11:28 am
enable lock pages in memory, check the paging file settings because on x64 the default is too small
January 8, 2009 at 11:29 am
We did not enable the lock pages in memory and the default pagefile size 2GB?
What are the settings do I need to change?
January 8, 2009 at 11:36 am
enable lock pages in memory for whatever account you run SQL under
there is a KB article on the paging file with a calculation, i like to use 2 - 4 times the RAM or 1-2 times the RAM if you don't have enough disk
there is an KB article somewhere about the paging file memory issue and there was a MSDN blog post a few years ago by one of the devs as well
January 8, 2009 at 2:24 pm
the most important factor with x64 is that you MUST set the max memory otherwise you'll have big problems. Lock pages in memory can be useful, 16Gb on x64 isn't much memory, I'd probably allocate 8Gb for the o/s alone.
There are also some windows bugs which can cause memory paging, at least one of these requires a registry fix so if you haven't done one of those you may suffer that. If you run anything else on the box, SSIS for example this can take all available memory and some, x64 is good but it's better with lots of memory.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 8, 2009 at 3:17 pm
Thanks collin,
We would set the max server memory to a fixed value rather than the default settings, But what about the page file size?we have 2GB page file size, Is the performance increases if we set it to 1.5 times the RAM size? How generally the DBA'S set this page file size?
January 9, 2009 at 4:19 pm
I run some x64 servers without a page file - if you have a page file it will be used, with enough memory my feeling is you can do without. There are lots of posts and blogs about improvements with XP with 4gb of memory and no page file.
Funnily enough I was running a test on an x64 server today, the data centre won't have the box without a page file despite my asking. The box has 64gb of ram with 14GB not allocated to SQL Server. My test which ran for some time showed constant paging in excess of 10k pages/sec for the entire duration despite there being 12gb of free physical memory available.
I'd set the page file to something nominal, the 1.5 times becomes pointless with say 128Gb of ram. Monitor your page file for usage and then set to a fixed size, typically most of my prod servers rarely use even close to 1GB.
Worry if you have excessive paging - I'd recommend process explorer rather than task manager to monitor - process explorer is available for free from microsoft ( sysinternals )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 10, 2009 at 1:16 am
Ensure you have enough RAM for SQL Server + OS + other apps / supporting services.
Enable the Lock Pages privilege for the service account running SQL Database Engine
Set Max RAM for your SQL Server instance.
If you set Lock Pages and don't set a SQL Server instance max memory you will run into problems where the OS requires RAM and SQL wont give it up.
As for whether or not you require a pagefile and if so, how much. This is a very complex discussion and not all of Microsoft's documentation is clear or consistent. Keep in mind that if you want a kernel dump of any kind, you must have a pagefile on your system drive.
--
Andrew Hatfield
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply