February 23, 2004 at 10:52 am
I have a server with 2 1Ghz processors and 4GB memory, running 2 instances of SQL2K SP3 under Win2K SP4. I have both instances configured to use 1.7GB, leaving approx 640mb for the OS. The box does nothing except SQL.
My director is requesting that I give the OS more memory, because it 'seems like 640 is too low'. I'd appreciate any feedback on what memory the OS ought to have under these circumstances.
Thanks,
Al
February 23, 2004 at 11:42 am
I would let system to balance the memory usage between OS and SQL Server instances by configuring SQL Server memory allocation dynamically.
Run performance monitor to monitor counter Memory: Available Bytes to ensure there are more than 4 to 10MB available for OS consistanly.
February 24, 2004 at 8:23 am
Not sure I understand and have always wondered this: SQL Standard can use a max of 2G, if th box has 4G of memory, how does the other 2 get used???
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
February 24, 2004 at 9:07 am
Hi Curtis,
Al didn't mention the version of SQLS their company is using, so I suppose it is Enterprise Edition. Standard Edition wouldn't be able to use the memory above 2 GB, you're right at that... as far as I know, it makes no sense to increase memory above the limit on dedicated SQL servers.
Vladan
February 24, 2004 at 9:15 am
"I have a server with 2 1Ghz processors and 4GB memory, running 2 instances of SQL2K SP3 under Win2K SP4. "
He has two instances running.
February 24, 2004 at 9:45 am
Vladan thanks -- thanks for the quick response about the memory configuration. Please explain futher: I have 2 1G processors with 4G ram. What happens if i dynamically set SQL mem to 100%. Where does OS get its memory from? From the same 2G as SQL or from the "free" 2G
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
February 24, 2004 at 10:24 am
The NT/W2K architecture allows a 4GB virtual memory space for the server, 2GB for the OS and 2GB for any particular application. This can be altered with the 3GB switch which limits the OS to 1GB.
If you dynamically allow memory to be managed, then you are forcing SQL to request memory when it needs it (instance 2) and the other instance (instance 1) has to deallocate memory. While quick to us, this is a slow process for the OS and can make your SQL server appear to hang. You want to set the max memory for each.
The OS will take memory for free space as well as from each server in the same allocation/deallocation process and use paging file space as well to maintain it's 2GB virtual space.
Personally I think 640MB should be fine for the OS, but you'd have to watch paging to see. I'd set the minimum and max for each instance to the same value, maybe 1.5 to leave the OS a little more and prevent memory swapping.
February 24, 2004 at 12:02 pm
From BOL.
"When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free physical memory available. SQL Server grows or shrinks the buffer cache to keep free physical memory between 4 MB and 10 MB depending on server activity. This prevents Microsoft Windows NT 4.0 or Windows 2000 from paging. If there is less memory free, SQL Server releases memory to Windows NT 4.0 or Windows 2000 that usually goes on the free list. If there is more memory free, SQL Server recommits memory to the buffer cache. SQL Server adds memory to the buffer cache only when its workload requires more memory; a server at rest does not grow its buffer cache.
Windows NT 4.0 and Windows 2000 provide a 4-gigabyte (GB) virtual address space at any time, the lower 2 GB of which is private per process and available for application use. The upper 2 GB is reserved for system use. Windows NT Server, Enterprise Edition provides a 4-GB virtual address space for each Microsoft Win32 application, the lower 3 GB of which is private per process and available for application use. The upper 1 GB is reserved for system use.
The 4-GB address space is mapped to the available physical memory by Windows NT Virtual Memory Manager (VMM). The available physical memory can be up to 4 GB, depending on hardware platform support.
A Win32 application such as SQL Server perceives only virtual or logical addresses, not physical addresses. How much physical memory an application uses at a given time (the working set) is determined by available physical memory and the VMM. The application cannot control memory residency directly.
Configuring SQL Server manually for more virtual memory than there is physical memory can result in poor performance. Also, the Windows NT 4.0 or Windows 2000 operating system memory requirement must be considered (about 12 MB, with some variation depending on application overhead). System overhead requirements can grow as SQL Server parameters are configured upward and Windows NT 4.0 or Windows 2000 needs more resident memory to support additional threads, page tables, and so on. Allowing SQL Server to use memory dynamically helps to avoid memory-related performance problems."
March 31, 2004 at 10:27 am
How do I determine what is being allocated to SQL. We run SQL2K sp3 on Win2K sp4, non-advanced server, with 4GB ram. I want to allocate 3GB to SQL so I've modified the boot.ini file as follows:
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000 Server" /fastdetect /3GB
I set the maximum allowed on SQL properties to 3GB but it appears to never go beyond using 1.6GB (via Spotlight). Are there other settings that need to take place? The boot.ini change and the setting of a maximum amount of memory within SQL are the only changes I've made. Thanks for any help you can provide.
Terry
March 31, 2004 at 10:34 am
Modifying the boot.ini has no effect in Windows 2000 Server. To take advantage of the extra memory you would need to run Windows 2000 Advanced Server.
March 31, 2004 at 11:15 am
Thanks, after I posted, I found another thread to an article by Ken Henderson which spelled that out very clearly. I appreciate the response.
Terry
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply