March 8, 2010 at 8:39 am
We have a Dual Quad-Core Xeon server with 20 Gigs of RAM running Windows 2003 Server Standard x64 SP2 and SQL Server Standard x64 and nothing else. The pagefile system runs up to 19.2 Gigs with no one connected to the machine. Thinking that this might be a memory leak, we applied SQL Server SP3 and are still getting the same results.
Does anyone have any recommendations?
March 8, 2010 at 9:22 am
What does perfmon show for "PagingFile:%Usage" ?
What are the SQL Server memory settings ? You can get the setting by running:
exec sp_configure 'min server memory (MB)'
exec sp_configure 'max server memory (MB)'
SQL = Scarcely Qualifies as a Language
March 8, 2010 at 9:41 am
What is pagefile system? Do you mean the page file set on disk? what are the settings for that?
March 8, 2010 at 10:15 am
The Windows' pagefile.sys is basically 90% utilized, by having I the buffer pool swapped out to disk. There are 10 users connected to the DB and 20GB of RAM, running a 'dbcc memorystatus' reports the following:
VM Reserved 21210992
VM Committed 18849320
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use0
March 8, 2010 at 10:19 am
Additionally the output from
exec sp_configure 'min server memory (MB)'
min - 0
max - 2147483647
config - 128
runnig - 128
exec sp_configure 'max server memory (MB)'
min - 16
max - 2147483647
config - 2147483647
running - 2147483647
March 8, 2010 at 10:35 am
It look like you have no limits on SQL Server memory, which is OK if this is a dedicated SQL Server machine.
you might want to read this blog (http://sqlblog.com/blogs/lara_rubbelke/archive/2008/04/18/memory-pressure-on-64-bit-sql-server-2005.aspx), which has some good information on memory pressure. I wouldn't necessarily worry about pagefile usage unless it is affecting the server, and then I'd be sure that you really have memory pressure on SQL Server. Here's another one http://www.sqlservercentral.com/articles/Performance+Tuning/analyzingmemoryrequirementsforsqlserver/2498/
The number of users doesn't necessarily matter. One person can peg out the caches with data.He
March 8, 2010 at 11:04 am
Regarding the % of usage of the pagefile thru perfmon shows 5% while task manager shows like 95%. Don't understand why there would be a difference.
March 8, 2010 at 2:09 pm
Steve Jones - Editor (3/8/2010)
It look like you have no limits on SQL Server memory, which is OK if this is a dedicated SQL Server machine.you might want to read this blog (http://sqlblog.com/blogs/lara_rubbelke/archive/2008/04/18/memory-pressure-on-64-bit-sql-server-2005.aspx), which has some good information on memory pressure. I wouldn't necessarily worry about pagefile usage unless it is affecting the server, and then I'd be sure that you really have memory pressure on SQL Server. Here's another one http://www.sqlservercentral.com/articles/Performance+Tuning/analyzingmemoryrequirementsforsqlserver/2498/
The number of users doesn't necessarily matter. One person can peg out the caches with data.He
Steve, leaving the max memory setting to the default was okay on 32-bit installations because there would always be memory reserved for the OS. On 64-bit installations, you have to set the max memory setting or SQL Server could take all of the memory on the server and starve the OS.
Actually, let me clarify that a bit. If your 32-bit installation has 3GB or more memory then you could leave the max memory setting to the default. If you have less than 3GB of memory - then you need to set the max memory even on 32-bit installations. Remember, by default on 32-bit installations applications can only get 2GB of memory unless you set the /3GB switch. For memory above 4GB on 32-bit installations you would have to enable PAE and AWE to access that additional memory and the OS would still have 2GB (1GB if /3GB switch is set) available.
On 64-bit installations the above no longer applies and applications can take as much memory as is available to the system.
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
March 8, 2010 at 2:56 pm
Jeffrey,
Do you have a 64 bit reference? I haven't seen this.
Even on 32-bit machines, with 2GB of RAM, I haven't seen SQL Server starve the OS. OS priority on memory seems like it has been high enough to prevent SQL Server from killing the machine. SQL can kill the CPU, and perhaps that is a symptom of memory starvation, but I hadn't seen that before.
March 8, 2010 at 3:06 pm
How about experience. When we were setting up our 64-bit blade servers for SQL Server 2008 EE, I forgot to set the maximum memory on one of the servers. I got a call from the network services group saying that the OS on that server was getting starved. On 64-bit servers, it eally pays to set the maximum memory availabel to the SQL Server instance.
March 8, 2010 at 3:14 pm
Experience is nice, but it would be good to see some objective measurements on this. Not saying you're wrong, just would like it to be something other than a guess that it's SQL starving things. I might ping CSS on this.
March 8, 2010 at 3:58 pm
Steve Jones - Editor (3/8/2010)
Jeffrey,Do you have a 64 bit reference? I haven't seen this.
Even on 32-bit machines, with 2GB of RAM, I haven't seen SQL Server starve the OS. OS priority on memory seems like it has been high enough to prevent SQL Server from killing the machine. SQL can kill the CPU, and perhaps that is a symptom of memory starvation, but I hadn't seen that before.
I don't have any references, just practical experience.
I have seen x86 installations where there was only 2GB of memory available being starved by SQL Server. Limiting the server to a maximum memory and reserving enough for the OS eliminated the memory issues.
On the x64 side, I have seen SQL Server choke moderate to large systems when the max memory setting was not defined. Again, setting the max memory cleared the issues.
My recommendation is to always set max memory for SQL Server and always make sure you leave enough for the OS to manage the available memory.
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
March 8, 2010 at 4:00 pm
Please also check this blog about SQL Server being paged out due to the file system cache size and how to trim.
"A significant part of sql server process memory has been paged out. This may result in performance degradation"
SQL = Scarcely Qualifies as a Language
March 8, 2010 at 4:09 pm
regarding SQL Server Memory Usage on a 64-Bit Platform, I have had the same experience as Jeffrey and
Lynn. For reference:
The 64-bit platform allows SQL Server to use much larger amounts of memory than it can use on the 32-bit platform. As a result, memory configuration is very significant to ensuring that all SQL Server instances, as well as other applications running on the server, have enough memory to function properly. SQL Server will always attempt to leave at least 128 MB of physical memory available to ensure the proper functionality of Windows[/b}; however, there are some circumstances in which SQL Server may be unable to do so.
....
When tuning memory consumption in SQL Server on the 64-bit platform, keep the following in mind:
If multiple instances of SQL Server are running concurrently, explicitly set the max server memory option of each instance to ensure that enough physical memory will be available for other SQL Server instances. Make sure to also consider other applications running on the server, as well as the operating system. To avoid paging, there should ideally always be at least 128 MB of physical memory left on the system. Monitoring the Memory: Available Bytes and Memory: Pages/sec counters can help to determine how much physical memory is available on the system and whether or not the system is paging.
When using a single instance of SQL Server, it is recommended that you set the max server memory option so that there is adequate physical memory for other applications as well as for the operating system.
When using multiple instances of SQL Server, you should perform testing to determine the optimal settings for the memory options of each SQL Server instance. Monitoring total memory usage using SQL Server: Memory Manager counters as well as SQL Server: Buffer Manager counters can give you a detailed breakdown of the memory consumption of each instance. Memory usage before consolidation can be used to estimate appropriate levels for the max server memory option.
The min server memory setting can be used to guarantee a certain amount of memory for each SQL Server instance at process start. To ensure that there will always be at least this amount of physical memory available on the server, set the proper max server memory setting for each instance. Failure to do so could result in poor performance of the entire server due to an excessive use of virtual memory.
http://technet.microsoft.com/en-us/library/cc917532.aspx
SQL = Scarcely Qualifies as a Language
March 8, 2010 at 4:10 pm
Thanks, Carl. Good to have that handy.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply