2005 and Memory Management
-
I've read and noticed SQL 2005 handles memory differently then 2000. In 2000 if I told a server it had 6GB to use, it allocated the memory. In 2005 I have one 32-bit server with 6GB of memory and one 64-bit server with 32 GB. If Target Server Memory is the amount of memory SQL Server would like to have, how does that correspond to Maximum Server Memory? Also, how is Target Server Memory determined?
32-bit
Physical Memory = 8GB
Target Server Memory = 6GB (Willing to consume)
Total Server Memory = 690MB (Currently consuming)
Minimum Server Memory = 2GB
Maximum Server Memory = 6GB
For the 32-bit server the Target Server Memory matches Maximum Server Memory
64-bit
Physical Memory = 32GB
Target Server Memory = 28GB (Willing to consume)
Total Server Memory = 397MB (Currently consuming)
Minimum Server Memory = 4GB
Maximum Server Memory = 30GB
For the 64-bit server the Target Server Memory is less then the Maximum Server Memory
Lock Pages in Memory is set for the service account. Neither server above has yet to be released to production and only the 32-bit server has any users. In 2000 when SQL Server started I could count on it using about 1.72GB of memory immediately. Seeing the servers above consume only only 690MB and 397MB has me concerned. Is this just a case of SQL Server 2005 handling memory better then 2000?
Thanks, Dave
-
2005 handles memory allocation dynamically... This is taken from books online which should reduce your conserns...
The min server memory and max server memory configuration options establish upper and lower limits to the amount of memory used by the buffer pool of the Microsoft SQL Server Database Engine. The buffer pool does not immediately acquire the amount of memory specified in min server memory. The buffer pool starts with only the memory required to initialize
if you 64 bit server has no activity then it unlikely to need any further memory until some activity takes place. hope this helps
Gethyn Elliswww.gethynellis.com
-
Thanks. This does help. But why am I seeing some pagefile access if I have so much free physical memory. I thought the pagefile is only to be accessed if the OS determine not enough physical memory exists to service a request. I have three new servers yet to be released to production and each show some degree of pagefile access.
Dave
-
Hi Dave
memory allocation in 2005 is dynamaic by default, I assume you have not cahnged this and you are not using AWE?
some minimal paging is normal. Is the paging you are seeing excessive?
At start up SQL server aquires only the memory it needs at that point. As users connect and access data sql server allocates memory to the memory pool as it needs it to support the extra workload. From what you said no users are connecting and accessing data? Is this true, if so can you try adding a load to you your server?
Gethyn Elliswww.gethynellis.com
-
Hi Ellis,
I have AWE enabled in order to take advantage of the more then 4GB of memory. The paging is very small on my new 2005 servers, between 2.5% and 4.5%. I'm just curious why I see any at all, especially on my 64bit server which has 32GB of memory and only 2.56GB used. It's not as if the server doesn't have enought free physical memory to satisfy an OS request.
My new SQL 2000 server is using 12% of a 2GB pagefile, which under normal use I wouldn't even worry about. But since nobody is using the server I'm curious why so much paging is happening. That server has 14GB of memory with only 10.6GB being used.
All servers have PAE set in boot.ini and SQL is configured to use AWE.
Thanks, Dave
-
Dave, The page file is way undersized. Microsoft recommends a page file of at least 1.5 times the size of the physical memory. Your page file should be around 21GB not 2GB.
Also, AWE/PAE is not required for x64 servers.
-
I believe those recommendations apply to application and file servers, not database servers. With a database server if you see paging consistently appoaching 2GB you probably have some serious memory issues. Regardless of my pagefile size I still don't understand why it is being accessed with so much physical memory available.
My mistake about the PAE/AWE comment. I should have said I have PAE/AWE enabled on all new 32-bit servers, but as you stated not on the 64-bit server.
Thanks, Dave
-
I have read and heard from a few people that 1.5 times is recomended but I have also read up to 3 times if Analysis Services is installed too.
-
The spilling of memory over into the page file is handled by the operating system, not the SQL Server. Even though you have told SQL to reserve physical memory and you have specified an amount of memory that is small enough to be all physical memory, the operating system is able to swap this out to the page file in certain situations.
This happens and it is normal. You should expect, on a 32 bit SQL 2005 server without AS, to get between 1.5% and 10% (yup that high) page file usage depending on what else is happening on the SQL server. As to when and why the operating system will do this - I am sure you can find some documentation from MS on this. If you want 0 page file usage, you have to completely remove the page file (I would never recommend this though).
As far as page file size - if you have SQL configured correctly, and I think you do by the page file usage you are indicating, it is ok to reduce it from the standard 1.5x physical memory, but you should not see much of a performance difference by doing this. Also remember that page file usage is often not a good indicator of your memory settings being correct. You should be looking at hard page faults - it is a better indication. Last - remember that AWE memory does not act like the native memory. There are things that can be cached in memory that cannot be cached in AWE memory. At this point, if you find you have a database server that needs more than 3gb of memory, the jump to 64 bit is too easy and inexpensive to overlook.
-
Ahh yes. I've just been through all of this with Microsoft themselves... The word we got back from them was that the pagefile has less and less of an impact in the larger memory configurations in the 64 bit world, and in many cases is not even needed. Your specific situation may be different. Just remember, if you have enough physical RAM and no pagefile defined, in the 64-bit O/S world, the extra memory can actually be used to SIMULATE a pagefile for applications that absolutely need one.
In addition, the optimal performance we've been able to achieve is to set min and max server memory, thus disabling the dynamic memory allocation. I typically configure SQL Server to use 80% of the physical RAM, reguardless of the total amount of RAM on the box, but you may use more or less depending on your situation. I do reccomend leaving anywhere from 2 to 4 GB to the operating system, if your box is dedicated to SQL Server.
Make sure the SQL Server service account has the "lock pages in memory" privelage assigned in the group policy editor on the SQL Server itself. Then enable AWE.
Now, you may be asking "Why do I enable AWE in a 64-bit environment?" Well, the truth of the matter is just because SQL Server CAN lock the pages in RAM doesn't neccesarily mean it WILL lock the pages in RAM. With AWE enabled, the rules change.
The AWE option in SQL Server has two DISTINCT behaviors. First, on 32-bit platforms, it allows SQL Server to address the higher memory segments. Second, on BOTH 32 and 64 bit platforms, it instructs the SQL engine to ALWAYS lock the pages in memory. In our environment, we noticed very nice improvement in performance of our 64-bit environments with the AWE flag turned on.
Again, this is all dependant on certain situations and circumstances, always load test any config change to make sure you have desired results. This is just my .02
-
I thank you both. I find this a bit confusing since heavy use of the pagefile is a possible indication of a memory bottleneck. However I agree that hard page faults is what needs to be examined. I also agree with the 1.5% to 10%. With 32bit SQL 2000 I typically believe a memory bottleneck is present if the pagefile usage is consistently above 15%. Pagefile usage above 12% I keep a close eye on. I also run perfmon against Memory
ages/sec, Memoryage Reads, Process:Working Set:SQL Servr, SQL Server:Memory Manager: Total Server Memory and SQL Server:Memory Manager:Target Server Memory. SQL Server: Buffer Manager: Buffer Cache Hit Ratio is also a good counter. If I make my pagefile 48GB, which is 1.5 x 32GB of physical memory, that to me seams way too much. With a 48GB pagefile, 10% would be 4.8GB of pagefile use, which seams very high. I need to keep reading about this because I am a bit confused. I also read a comment on the Internet stating AWE memory is not pageable, but I've yet to confirm this.
Thanks, Dave
-
Your comment about AWE not being pageable seems to be accurate.
Just one more thing... I noticed the same thing on my SQL Servers until we turned AWE on. This prevented SQL Server memory from being paged, which in my opinion, is kinda a good thing...
-
I'll enable it on our 64bit server and run some tests to see if performance has improved. The link below appears to imply that AWE memory doesn't impact the pagefile if at all.
One more thing. When you have a large amount of physical memory do you still typically use the /3GB switch? I'm guessing the answer is "it depends", but I'm looking for a general rule of thumb. I'm thinking about setting the option, but that restricts the kernel memory to 1GB. Even though my database servers are "dedicated" to SQL Server, we have a lot of monitoring/reporting software running such as Altiris for software and dll inventory, CSA (Cisco Security Agent) to prevent unauthorized installations, TREND for anti-virus and Double Take for data replication used for Disaster Recovery.
Thanks, Dave
-
No. In the 64-bit O/S world, /3GB does nothing.
-
Here's an update. I spoke with Microsoft today regarding the /3GB switch and SQL 2000. I added the switch to a server yesterday and it would not load the OS. I was told by a Windows engineer and a SQL Server engineer to avoid using /PAE and /3GB together. While this gives 3GB of virtual address space to the user mode, removing 1GB from the kernel mode places too much of a burden on the OS. More time then not the combination of the two leads to problems.
I was also told AWE only comes into play with SQL Server indexes and data pages. Anything else, like stored procedure caching, impacts the buffer pool. To calculate the size of your buffer pool subtract (256MB + 128MB) from 2GB of VAS = 1.6GB.
Lastly, when dealing with the pagefile it is not necessary to follow Microsoft's standard recommendation of making the pagefile 1.5 to 3 times larger then physical memory. AWE memory is essentially non-pageable and SQL Server should not be using the pagefile heavily. If I want, I can keep the pagefile at 2GB, assuming a dedicated database server.
Hope this information helps someone.
Dave
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply