SQL Server Performance Forum – Threads Archive

SQL Server 2005 Memory problem

Hi, I got a problem with the page file usage from SQL Server is getting bigger and bigger. We’re using Intel(R) Xeon(TM) CPU 3.4GHz and 8GB of RAM, Windows 2003 64bit and SQL Server 2005 64 bit. We are running this server since january 2007 and suddenly we saw the page file usage was getting bigger, the SQL Server stop running and the datbase was down. We found in the internet some additional configurations like: 1. Adding additional physical memory/tuning statements. The symptoms of this error message occurs when on 64-bit standard edition the buffer pool of SQL server grows to be larger than physical memory. The server has 8GB, we cannot extend the memory any more. 2. Set Max Server Memory. Setting max server memory to a value less than physical memory prevents the buffer pool from growing beyond the amount of physical memory and therefore prevents this error from occurring. We generally recommend leaving some physical memory for the OS since the OS handles all of SQL Server’s IO requests, etc.
We put the Max Server Memory = 6144 MB 3. Upgrade to Enterprise Edition of SQL Server 2005 and set "Lock Pages in memory Option." Enterprise Edition of SQL Server can take advantage of the system policy "lock pages in memory" which also alleviates this error. Unfortunately, SQL Server 2005 standard edition is not coded to take advantage of this setting and is a limitation of the software. This is why making this change on your system has had no effect since the server is running SQL Server 2005 standard edition. I’m not sure if this is the right think to do, because after doing all this the page file usage is still 6.68 GB. Before doing these configurations I was using dbcc freesystemcache(‘All’) every 5 min and the max page file uasge was 4 GB.
Should I change back the configurations I’ve done? Can anybody please help me with that. Thanks a lot
Entela

---

Can you please be a little more specific on what you mean by page file usage? How are you measuring it? What was it before the problem started, and what is it now after the problem? What other things have changed in the environment (number of connections, number of transactions, etc.)? ——————————–
Brad M. McGehee, SQL Server MVP
http://www.sqlbrad.com

---

Have you set a Min Server Memory? It is my recommendation that you do so if you have not already. I have had issues with SQL Server 2005 64-bit not taking as much RAM as it should.

---

HI, Thanks for the e-mail. I saw the page file usage on task manager at performance tab. Before the error occured this parameter was less than 3.5 GB, sometimes went down and sometime up but the limits were 2.88 min and 3.5 max.
Last friday for a moment the SQL Server went down and the error was
"SQL Server is terminating in response to a ‘stop’ request from Service Control Manager. This is an informational message only. No user action is required."
at that time we saw the PF was getting bigger for few minutes. I have set the minimum 2048MB. Thanks a lot
Entela

---

What was the Total server memory and Target server memory counter values at that time? Try to enable SQL Server to use locked pages in memory. http://blogs.msdn.com/slavao/archive/2005/08/31/458545.aspx
http://blogs.msdn.com/slavao/archive/2006/03/13/550594.aspx MohammedU.
Moderator
SQL-Server-Performance.com

---

Hi, Actually I have enabled the Locked pages in memory, giving this right to the administrator (Should it be any specific user account) but after this configuration the PF Usage is getting bigger, around 6.8 right now.
Before enabling the Locked Pages in Memory, when I was using DBCC FreeSystemCache the PF Usage was at max 3.5. I don;t know what is wrong? Thanks a lot
Entela

---

What was the Total server memory and Target server memory counter values at that time? If Paging File: %Usage Peak (or Peak Commit Charge) is high, check the System Event Log for events indicating page file growth or notifications of “running low on virtual memory”. You may need to increase the size of your page file(s). High Paging File: %Usage indicates a physical memory over commitment and should be considered together with external physical memory pressure (large consumers, adequate amount of RAM installed).
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx MohammedU.
Moderator
SQL-Server-Performance.com

---

Hi,
I didn’t mention the counter at that time, because the SQL Server went down, and I run to change the node, The system should be running 24/7. The server have 8GB RAM,
MIN Server Memory = 2048MB
MAX Server Memory = 6144MB
pagefile.sys at that time was around 4GB and actually is around 2GB. The error I got is this one: Application popup: Windows – Virtual Memory Minimum Too Low : Your system is low on virtual memory. Windows is increasing the size of your virtual memory paging file. During this process, memory requests for some applications may be denied. For more information, see Help. What do you recommend in this situation? Thanks
Entela

---

Is your server is dedicated sql server ? or is there any application also running? Increase PF file size, I don’t think it is the right size based on the memory you have. Run perfmon(sysmon) to capture memory usage and pf usage etc… MohammedU.
Moderator
SQL-Server-Performance.com

---

Hi Yes there is an application running. I saw the performance and I got the following:
Pages/sec
Average 0.00min 0.00max 0.00 Processor Time
Average 15.551min 7.03max 33.89 Avg.Disk Queue Length
Average 1.38min 0.023max 66.245

---

something is not right
there is no paging file info on the Task Manager Performance tab there is commit charge, which includes both physical and page usage
there is Kernel Paged

---

never mind, the graphs say PS usage & history
but is actually committed bytes usage, not page file note that performance shows no pages/sec run the VAS script in my other post in the DBA performance section anyways, the symptoms shown here point to VAS problems, not physical memory

---

Hi, Can you please send me again the VAS Scripts? Thanks a lot
Entela

---

I think it is in the following thread…
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19498
MohammedU.
Moderator
SQL-Server-Performance.com

---

HI, I run the VAS Scripts and I got these values: available memory in all free regions
3890148 KB get size of largest availble region
31168 KB What do you think about those? Thanks a lot
Entela

---

HI, We still are in trouble, the PF Usage is big 6.75 GB. this continues to remain the same for more than 4 days. the phisical memory is 8GB. Do you think somethink is going wrong? Entela

---

can you check the memory usage by the sql…
Check through perfmon TOTAL SERVER MEMORY and TARGET SERVER MEMROY…
MohammedU.
Moderator
SQL-Server-Performance.com

---

Hi,
This KB will surely help you for determining correct size of PF on 64-bit box.
http://support.microsoft.com/kb/889654 Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com

---

PF usage is "page file" usage, which is a variable amount of hard drive space that is dedicated to your total system memory. When your free RAM is low, the computer will start using this space to store RAM instructions. Because hard drive access is much slower than that of RAM, this can reduce performance – but reduces the risk of your computer locking up due to low amounts of free RAM. The page file is also referred to as ‘virtual memory’ or the ‘swap file’. The size of your page file can be changed by going to Start – Control Panel – System – Advanced tab – clicking ‘Settings’ under the ‘Performance’ section – Advanced tab – and clicking ‘Change’ under the ‘Virtual Memory’ section. However, because the page file is significantly slower than your RAM, it is best to keep it close to the start of your drive and make it a set size, not variable. Generally, a system total of 2.5GB should be good for recent applications. For example, if you have 1GB of physical RAM, setting your page file to 1024MB will give a total of 2GBs of available memory space. Increasing the size of the page file is not an alternative to adding more RAM.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

]]>