Memory Problems?
-
Info:
SQL 2005 x64
Server 2003 R2 SP2 x64
16GB of Ram
6 Databases largest one is 200GB (Dynamics AX)
Here is a shot of the memory usage on the server:
The amount of available memory seems to fluctuate between 1.5GB and 300MB
My first question is do I need more memory?
Second, if I don't need more memory, why is the page file so high, is it because SQL is not releasing old pages from memory?
Thanks,
-
Once SQL Server takes memory, it really doesn't give it back unless the OS tells it to. Windows won't tell it to unless Windows is being starved, which really shouldn't happen much on a dedicated database server.
Is the lack of available RAM causing a problem, or are you just curious about it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
I know that SQL takes memory and doesn't give it back to the OS. I have it set to use 14GB of the total 16GB.
I guess my biggest concern is the large page file. It appears that SQL is using the 14GB and then wanting more and Paging Out?
The problem is that the server is very slow. I see a lot of page faults so I assume the slow down is because the system has to go out to disk.
-
The "Page File Usage History" in Task Manager, you mean? That's total memory use, both RAM and "swap file"/"page file"/"virtual memory". So, no, you're not hitting the disk for extra memory per the screenshot you posted. If it goes over the amount of physical memory, right below the graph, then you're on disk. But you're not doing that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Any idea why the page file is so large? I was wondering if I needed more memory because of the size of the page file.
-
This is what I am assuming. For some unknown reason the server decided to page out all of the memory. After this happens queries that normally run quick run slow. This normally happens all at once, the PF will be small in size after a restart and after about a day it jumps to 15GB. When I was referring to going out to disk in my posts above I meant going to the PF which is stored on the disk.
Is this a problem with the dynamic memory management of SQL 2005 and server 2003 R2? Should I enable AWE and Locked Pages In Memory? My only reserve to doing that is I am really not solving the problem of why the server is constantly paging out. My other thought is that the 2GB I give to the OS is somehow not enough, even though it does nothing but run the database.
-
AWE is meaningless on a 64-bit system. Won't turn on, even if you tell it to, because it's just a way to get 32-bit systems to access more RAM.
If SQL Server is being pushed out of RAM and into the page file, I'd expect it's something else firing up and taking over the RAM. Any other services running on that machine that might do that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
It is a dedicated SQL box, so nothing but SQL is running on it. The only other thing I can think of is once in a while the server gets really slow and when I look at the task manager all the resources are going to a user (server admin) who is logged in just sitting on the desktop. Once I terminate that users session server goes back to normal. I have never checked to see if that is when the memory gets paged out but I would bet on it.
A lot of the articles I've read suggested setting the locked pages in memory permission and enabling awe on 2005 SQL Servers running on Server 2003 because of problems with SQLs dynamic memory management.
This is one of those articles: http://sqlserverperformance.wordpress.com/2011/02/14/sql-server-and-the-lock-pages-in-memory-right-in-windows-server/
-
This may sound stupid, however have you checked the page file settings? Someone may have changed the setting from "Let Windows manage..." to a fixed size....
Kay
-
tfendt (9/26/2011)
...A lot of the articles I've read suggested setting the locked pages in memory permission and enabling awe on 2005 SQL Servers running on Server 2003 because of problems with SQLs dynamic memory management.This is one of those articles: http://sqlserverperformance.wordpress.com/2011/02/14/sql-server-and-the-lock-pages-in-memory-right-in-windows-server/%5B/quote%5D
It won't work. You're on a 64-bit server, per your first post.
Check: http://msdn.microsoft.com/en-us/library/ms190730.aspx
Where it says:
The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE). This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not required on 64-bit operating systems.
(emphasis added)
Locking pages in memory is only required if you will use AWE. AWE is just a way to get 32-bit systems to address more than 4 Gig of RAM. 64-bit systems not only don't need AWE, they will ignore you if you tell them to turn it on. It's like pedaling faster in your car - on a bike (32-bit), it'll make you move faster. In a car (64-bit), it will just make you look odd to anyone who sees you doing it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Yes, it says it is not required, but it doesn't say that it wont help keep the memory from paging out. I am aware that the AWE allows 32 bit versions to access more memory and that it is not needed on x64.When I said enabling AWE I was referring to this quote from a Microsoft support page: "The use of AWE APIs on 64-bit editions of SQL Server for buffer pool memory allocation is known as Locked Pages."
Here they tell you to enable it: http://support.microsoft.com/kb/918483
-
Heres a random suggestion........ Do away with PF altogether! we dont use PF on our prod servers as they are unpredictable.
If your server is a dedicated db server all you really need to worry about is SQL and OS, BUT dont forget the CLR i saw nothing mentioned in posts so far, but if you use the CLR that is taken outside the MaxMemory limit. So if SQL is using 14GB AND you use CLR that is easily more then 14GB.
BUT: Windows will start paging when 80% RAM is used, so what is likely to be happening is Windows is getting staved, SQL is releashing RAM and then using PF to store the data from the released RAM.
What you should/could do is cap SQL at 12GB and use lock pages in memory. Thats should stop SQL paging out and prevent OS from starving.
Let me know. HTH
Adam Zacks-------------------------------------------Be Nice, Or Leave
-
More info:
http://www.bradmcgehee.com/2011/03/do-you-enable-lock-pages-in-memory/
Adam Zacks-------------------------------------------Be Nice, Or Leave
-
tfendt (9/27/2011)
Yes, it says it is not required, but it doesn't say that it wont help keep the memory from paging out. I am aware that the AWE allows 32 bit versions to access more memory and that it is not needed on x64.When I said enabling AWE I was referring to this quote from a Microsoft support page: "The use of AWE APIs on 64-bit editions of SQL Server for buffer pool memory allocation is known as Locked Pages."Here they tell you to enable it: http://support.microsoft.com/kb/918483
and here http://support.microsoft.com/kb/970070%5B/quote%5D
I hadn't read those. Good to know.
Have you tried all the things they say to do first, like the hotfixes, and checking for those specific error messages?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Schadenfreude-Mei (9/28/2011)
Heres a random suggestion........ Do away with PF altogether! we dont use PF on our prod servers as they are unpredictable.If your server is a dedicated db server all you really need to worry about is SQL and OS, BUT dont forget the CLR i saw nothing mentioned in posts so far, but if you use the CLR that is taken outside the MaxMemory limit. So if SQL is using 14GB AND you use CLR that is easily more then 14GB.
BUT: Windows will start paging when 80% RAM is used, so what is likely to be happening is Windows is getting staved, SQL is releashing RAM and then using PF to store the data from the released RAM.
What you should/could do is cap SQL at 12GB and use lock pages in memory. Thats should stop SQL paging out and prevent OS from starving.
Let me know. HTH
I was debating on removing the PF all together but some people suggest that is a bad idea. I reconfigured SQL to use 13GB of memory but as you noted windows starts paging when 80% RAM is used so I might limit the SQL server to 12GB leaving 800MB until I reach that 80% used mark.
Have you tried all the things they say to do first, like the hotfixes, and checking for those specific error messages?
In the support articles I posted, I am experiencing the errors noted in the articles and we are doing many of the things that the articles say cause this problem.
I think the first step will be to apply the hotfixes that Microsoft has and to enable locked pages. I might just keep the PF but limit the size of it (I am just too nervous about not having one and then needing one) I will update after I apply these changes.
I am hopeful that these issues will go away when we migrate to 2008 R2 🙂
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply