Tag: Windows

AWE (Address Windowing Extensions) in SQL Server

Memory is cheap these days; it’s not unusual to find laptops with 4GB or more (and yes, I know this post won’t date well!). This is a bit of a problem with some operating systems, though: 4GB is the maximum memory that many system are capable of handling. Yes, that’s right. Anything over 4GB is wasted on many of today’s operating systems, because of a limit imposed by 32 bit processors on how much memory they may address[1].

This becomes rather a problem on SQL Server installations, where having a lot of available memory is quite beneficial.

It’s worth mentioning at this point that 64-bit systems don’t suffer from the same memory limitations as 32-bit systems, so much of this article is irrelevant if your system is running on a 64-bit edition of Windows. The issue we’re talking about here is that 32 bit systems can ordinarily access only 4GB of RAM in total, which under default conditions means that your SQL Server instance can only access 2GB of useful RAM for its own needs, and putting one of these in your development laptop is quite pointless.

However, it is possible to grant access to more memory, but it does depend on a number of conditions.

Physical Address Extension

Since Windows 2000, Microsoft have supported Physical Address Extension (PAE) in a kernel, although the support requires using a non-default kernel. To use PAE, both the hardware and the operating system must support it.

Various 32 bit Windows servers support from 8-128GB, depending on edition. Windows 2000 Advanced and Datacenter Servers, Windows Server 2003 Enterprise and Datacenter Editions, and Windows Server 2008 Enterprise and Datacenter Edition support more than 4GB in 32 bit editions, by using PAE, with Windows 2003 Datacenter hitting the top score of 128GB.

Address Windowing Extensions

Address Windowing Extensions (AWE) is an API in Windows, used by SQL Server to support larger amounts of physical memory. It shouldn’t surprise you by now that AWE relies on PAE, which in turn has the other prerequisites as described above, so in order to enable AWE in SQL Server (using sp_configure), PAE must be supported by the underlying operating system.

The benefits are obvious; SQL Server will have access to more physical memory, which will make its job a lot easier. Your buffer cache will love you, as more and more data can be pulled out of memory rather than causing page hits on your oh-so busy hard disk. The drawbacks are slightly less obvious; memory granted to SQL Server using AWE is non-paged, which means it’s allocated until SQL Server is shut down. This will cause problems is SQL Server is sharing its host with other instances or other AWE-aware services. So keep your maximum memory settings tuned, and enjoy the headroom!

Footnote:
32 bits is 4 bytes (of 8 bits each). Just like a 3-digit number can store up to 1000 different values — 0-999 — an 8-bit number can store from 0-256. A 6-digit number can store 1000 x 1000, which is 1,000,000 different values, 0 – 999,999. Similarly, a 16-bit number can store 256 x 256, which is 65,536 different values. 32 bits can store 65,536 x 65,536 values, which is a number comfortably over 4 billion. This is where we get the 4GB upper limit for most 32 bit systems; they simply can’t point to the location of memory outside this range.

Advertisement

Running a command on a remote server with SQL Server

Another StackOverflow question: How do I run an OS command on a remote server, from within SQL Server? I answered as follows.

Something like rsh might do – have your local SQL instance invoke xp_cmdshell with an rsh command (or a batch file), connecting to the remote server and executing the command.

If you have SQL on the remote machine, it’d be far easier; call a stored procedure on that machine from the local machine, and let that stored procedure do the work (might need fiddling with proxy accounts and “execute as”).

A third option would be to create a .NET stored procedure, and do your socket programming or remote invocation from there – it runs in-process, so you get similar same performance and security benefits you’d get from writing it in T-SQL rather than hopping out to a cmdshell with all the mess that entails.

The rsh solution won the day.

Having said that, the rsh solution is not tied to SQL Server — rsh can be invoked from anything capable of running shell commands locally, even a batch file, or a Perl script.

Rsh is a feature Microsoft added to Windows a few versions ago, although a variety of it has been available in resource kits since NT 4.0. In simplest terms, it provides a remote command line, not unlike telnetting into a Unix server, only you get the Windows CMD prompt. Rsh allows the invoker to invoke a single command remotely, using command-line options, and without the need to write any further server-side code. This makes it suited to the quick-and-dirty route taken by the recipient of the above answer.