(I’m taking a break from my DBA Best Practices blog series to talk about this today – the series continues after the Holidays!)
I love to be proved wrong. Really, I do.
Recently we had a discussion in an distribution list where somebody asked whether a SQL Server 32-bit instance could address more than 4Gb of RAM when running on top of a 64-bit OS. One of the really smart guys in Microsoft SQL Server Support, Karthick P.K. [Blog|Blog|Twitter] replied “sure, just add more RAM and enable AWE – SQL will use that memory”. I was much convinced that this was incorrect, so I jumped in and said that AWE does nothing under those circumstances. After all, that is what I had read in the past in many different articles. I even had some SQL Server MVPs who have been around the block forever agreeing with me.
But the good news is that I was wrong.
AWE in fact does nothing on 64-bit instances of SQL Server. But on WOW64 - Windows on Windows 64-bit, the subsystem of Windows x64 that allows you to run 32-bit processes and instances of SQL Server, AWE does allow you to address memory above 4Gb. Let me show you.
Side note: AWE is now deprecated - removed in SQL Server 2012. The last version of SQL Server that supports AWE is SQL Server 2008 R2. Because of this, a SQL Server 2012 x86 instance won’t be able to enjoy over 4Gb of memory – even if running on WOW64.
I setup a vanilla VM in my lab with a 64-bit OS with 8Gb of memory. MSINFO32 looks like this on the VM:
I installed a 32-bit SQL Server 2008 R2 SP2 instance on the VM. @@VERSION looked like this:
I did not enable AWE. I did grant the “Perform Volume Maintenance Tasks” and “Lock Pages in Memory” privileges using secpol.msc (not pictured!). I wanted to see what memory consumption would look like without AWE enabled.
This screenshot shows the permissions granted to the SQL Server Service Account (NETWORK SERVICE, in this case) – used “whoami /priv”, a quick and dirty way to get the privileges of the service account using xp_cmdshell. Ugh, xp_cmdshell. It’s okay, it’s just a test.
I went ahead and created a test database with just one big table – big enough to use all the buffer pool memory on the instance when a size-of-data operation is executed on it. I did set Max Server Memory to 6Gb.
Here’s the CREATE table script:
CREATE TABLE [dbo].[testTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[wideColumn] [nvarchar](4000) NULL,
PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[testTable] ADD DEFAULT (replicate(N'A',(4000))) FOR [wideColumn]
Next, I went ahead and inserted 1M rows on the table:
INSERT INTO [dbo].[testTable] DEFAULT VALUES
If you do the math very quickly, you will agree with me that this not so little table will be more than 6Gb in size. Let’s check anyway:
Cool! The table is large enough for the purpose of this test. But how does memory utilization look like after that 1M row insert?
That’s less than 4Gb. Next I turned on AWE, and restarted the instance. Then I queried the ERRORLOG to confirm that AWE was enabled:
Okay, since I had just restarted the instance my buffer pool was gone – needed to force a size of data operation on the large table. Rebuilding the clustered index did just fine (I know, that’s probably overkill). Then I checked sys.dm_os_process_memory again:
And there we go! A lot more than 4Gb being used now that AWE is in play. I had set Max Server Memory to 6Gb, as I detailed earlier.
It looks like there’s quite a lot of incorrect documentation regarding this, so I wanted to clear out any confusion – and learn something new along the way. Don’t you love SQL Server?
Happy Holidays and Happy New Year!
P.S.: If you are running IA64 (Itanium) - then this doesn't apply to you. AWE does not work at all on IA64.