There is an almost constant stream of posts on forums asking about configuration of SQL Server 2005 memory, especially for x64 editions. Alongside these are also a number of posts concerning performance or perceived performance issues with x64 SQL Server.
Now I don’t claim to have all the answers but what does concern me is the danger of the adoption of misinformation. I’m using these as pure illustrations, not saying that anything written or posted is wrong or otherwise, and I include myself here but, just because Tony or Simon post something doesn’t automatically make it gospel and on forums it’s far worse with many incorrect posts from people with strings of letters and fancy job titles after their name; it’s tempting to believe these posts have to be right, my worry is that the seed of conjecture and misinformation becomes truth, somewhat like an urban myth.
So what’s all this about then – well x64 config really, I recently read a post where a respected SQL person suggested you could allocate more memory to sql server on a x64 box because the o/s had a smaller footprint, e.g. maybe 62gb out of 64gb, so we’re talking serious hardware here.
For the last few months I’ve been attempting to understand in greater depth those aspects of the o/s and how they relate to SQL Server on x64 platform and exactly what does what including o/s patches which affect SQL Server.
I’m now of the opinion that there should be a dialog between the SQL teams and the Windows Server teams and that aspects of the o/s which may affect SQL Server should be brought to the fore. Yes I know we should all have the latest patches applied but I find many organisations unwilling to apply o/s patches for all manner of reasons.
So let’s consider memory first; x64 with 32gb ram, we can allocate 2 to 4 gb to the o/s and give the rest to SQL Server, or this is what we believe? Well not really. Let’s say this is a 4 way dual core box, windows will allocate 576 work threads, each of which requires 2mb of memory. In the illustration I viewed the calculations go thus:-
- Let's leave 2GB for the OS and other applications: 2GB
- And let's allocate 2GB for the MPA / Thread Stacks / Linked Servers etc: 2GB
- And finally, let's reserve 3GB for all the other applications on the server (AV, backup etc): 3GB
- So now our max server memory setting is: 32-2-2-3 = 25GB
- Remember - this is a baseline calculation
The average Enterprise SQL Server will likely have fibre channel HBAs, be SAN attached, probably have teamed / load balanced HBAs and NICs, will have a suite of hardware monitoring programs, will have an Antivirus, will be running a monitoring program such as Tivoli or MOM and will probably have other agents and services running as part of your standard build. You may well have IIS, reporting services, SSIS, old dts, full text. Do you really believe these can all run within 2gb – including the operating system?
Btw. Multi page allocations exist outside SQL Server memory, also known as memtoleave in x32, if you’ve never run into this I recommend “SQL Internals” by Ken Henderson – a marvelous read.
Did I mention System Cache, under advanced settings are a number of options, typically system cache will be just under 1gb if you select it or 512mb if you don’t ( x32 ); except on x64 it can be 1TB.
We know we should enable lock pages in memory, this stops help reduce sql server memory being paged out, except you can’t do this for x64 STD edition. Well my research states that essentially awe locks pages in memory, pages enabled with awe may not be paged out – so to ensure your sql server memory remains for SQL Server enable awe in standard edition.
However, it is also stated that awe is not enabled in x64, even if you select it, and my basic tests seem to confirm this. Here’s a direct quote:-“ SQL Server 2005 does respond to memory pressure both when lock pages in memory is enabled and disabled. However there is a caveat that you need to be aware of - when OS hits memory pressure, depending on the state of the machine and applications, it could be really slow - attempting to free up the resources. This means that SQL Server might not get enough CPU cycles to respond to memory pressure quickly and hence causing other applications and possibly OS to return OOM errors. In case when lock pages in memory is not enabled it is possible that OS will page out SQL Server. This situation might result in inappropriate SQL Server performance. “And another quote:-“The AWE functions exist on all editions of Windows and are usable regardless of how much physical memory a system has. However, AWE is most useful on systems with more than 2 GB of physical memory because it's the only way for a 32-bit process to directly use more than 2 GB of memory. Another use is for security purposes: because AWE memory is never paged out, the data in AWE memory could never have a copy in the paging file that someone could examine by rebooting into an alternate operating system.Finally, there are some restrictions on memory allocated and mapped by the AWE functions:· Pages can't be shared between processes.· The same physical page can't be mapped to more than one virtual address in the same process.· On older versions of Windows, page protection is limited to read/write. In Windows Server 2003 Service Pack 1 and later, no access and read-only are supported.”
How can you check your memory? Well not with task manager, it seems, available memory may include memory allocated within o/s pools so this figure doesn’t actually mean this memory is not being used. The perfmon counter available Mb should be the counter watched, this should never drop below 200Mb ( the actual phrase used was "a couple of hundred Mb" )
Just in case you’ve got yourself a new 4 way quad core with HT and are rejoicing at 32 procs, SQL Server will have allocated even more worker threads and used even more memory. Just as an aside to this it seems windows can only support 64 processor threads, I don’t fully follow this part but it seems 64 procs was considered more than adequate but with quad core and HT you can reach this very quickly and give yourself major issues – but that’s another matter altogether.
So I think it’s fair to say that as DBAs we should allocate more memory for “other” processes, especially with x64.
I did also discover that enabling the 3GB switch in x32 reduces available memory and will likely increase paging as the working set is reduced, talking of which there is an o/s bug which can reset memory working sets for applications ( SQL Server ) when you use RDP to connect to a server, which I imagine every DBA probably does, nice, and did I mention the bug which causes problems with file copies? Oh and there’s the o/s schedulers bug too. ( all listed below )
Allocation of Processor Resources, I'm sure everyone knows this one but it should be set for Background Services.This setting alters the balance of cpu prioritisation between foreground and background.When set for Programs the foreground applications get processor priority, it is better to allow equal priority for all applications and their processes.
Pagefile sizing etc. The tuning of the page file is an interesting topic, I will dedicate an entire blog post to this later, let’s just say that placing your page file on a disk ( partitioned or not ) that shares with the o/s is probably a very bad idea.
You should monitor process handles as a matter of course, handles take resource and a rule of thumb apparently is that over 4,000, for a single process, is a big danger sign. I’ll also look into this further.
I’ve obviously skipped and jumped about over a number of areas and not covered any in depth, I’m hoping to perhaps provoke some dialog on x64 architecture because it has some challenges and I’d like to have some definitive answers as I’m a strong supporter of the platform.
I’ll just end with a quick plug for www.grumpyolddba.co.uk which will also have this and other information.
Listed below, links and references to sources used;
Microsoft® Windows® Internals, Fourth Edition: Microsoft Windows Server™ 2003, Windows XP, and Windows 2000
Tuning Windows Server 2003 on IBM System x Servers. The sizes of the working sets of all the processes in a console session may be trimmed when you use Terminal Services to log on to or log off from a computer that is running Windows Server 2003max worker threads OptionHow to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005You may experience a decrease in overall system performance when you are copying files that are larger than approximately 500 MB in Windows Server 2003 SP1 or in Windows Server 2003 SP2About Cache ManagerDoes SQL Server always respond to memory pressure?The Guru's Guide to SQL Server Architecture and InternalsSQL Server and the Working SetDo I have to assign the Lock Pages in Memory privilege for Local System
Memory Management 101
SQL Server 2000 or SQL Server 2005 may temporarily stop responding on a Windows Server 2003 Service Pack 1-based computer