THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Argenis Fernandez

The Myth Around 32-bit SQL Server Instances on 64-bit Operating Systems, and AWE

(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:

image

I installed a 32-bit SQL Server 2008 R2 SP2 instance on the VM. @@VERSION looked like this:

image

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.

image

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 
        (
            [id] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[testTable] ADD DEFAULT (replicate(N'A',(4000))) FOR [wideColumn]
GO

Next, I went ahead and inserted 1M rows on the table:

INSERT INTO [dbo].[testTable] DEFAULT VALUES
GO 1000000

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:

image

Cool! The table is large enough for the purpose of this test. But how does memory utilization look like after that 1M row insert?

image

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:

image

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:

image

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? Smile

Happy Holidays and Happy New Year!

-Argenis

P.S.: If you are running IA64 (Itanium) - then this doesn't apply to you. AWE does not work at all on IA64.

Published Sunday, December 30, 2012 4:36 PM by Argenis
Filed under: , , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Greg Low said:

Great (and clear) post Argenis. Thanks for sharing it.

December 30, 2012 6:56 PM
 

Ian Yates said:

You're right about the bad documentation - a lot of bloggers don't consider x86 SQL Server on x64 Windows and neither does much of the Microsoft documentation.

We have been taking advantage of this for a couple of years on several customer sites because we need to integrate SQL with some legacy DBs via linked servers that only have 32 bit drivers. Enabling AWE at least lets us take advantage some of that RAM. Unfortunately other issues still exist such as bad backup utilities and DB drivers depleting the "memtoleave" area put aside for their operation in the x86 address space.

Great post 😊

December 30, 2012 7:00 PM
 

Greg Linwood said:

Nice post Argenis & Happy New Year to you too.

I like the way you posted the remaining columns in the dm_os_process_memory output as they reveal a few other interesting points.

Note that the process still has a Virtual Address Space even when using AWE? This is because only a couple of memory managers use AWE in SQL 2008 whilst many of the others use regular Virtual Memory. This is something that has changed in SQL 2012 so it would be interesting to see the same output from SQL 2012 vs SQL 2008.

Also, there is more physical memory in use than locked allocations. "In use" isn't exactly the same as "allocated" but the difference between these two is roughly what physical memory has been allocated to back the virtual memory. The relationship between all of these can also be influenced by other settings such as -g startup switch, which is also something you need to consider when using AWE under x86 SQL systems..

December 31, 2012 1:15 AM
 

Joseph said:

Nice article, Argenis!

January 2, 2013 10:26 AM
 

Tx Argenis said:

Simple & Clear

June 21, 2013 1:14 AM
 

Simple User said:

Hello,

one quick question is it also possible for version:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)   ?

May 13, 2014 3:46 AM
 

Argenis said:

Hi Simple User. SQL Server 2008 R2 Express is capped to 1GB of RAM, so AWE obviously out of the picture. Both more importantly, you're running 64bit SQL on 64bit Windows, so AWE doesn't apply to you at all.

May 13, 2014 11:44 AM
 

Simple User said:

Thank you very much for your answer.

So to make it clear - when I run SQL Server Express R2 on 64bit machine SQL will only use 1 GB RAM and I can't do anything about it?

May 20, 2014 7:14 AM
 

Argenis said:

May 20, 2014 12:46 PM
 

Roy Ogborn said:

Awesome post! Thanks for putting in the time to explain this well. One simple check box makes all the difference in the world.

June 19, 2014 6:27 PM
 

Simple User said:

Thank you very much for your reply and clarification.

June 24, 2014 4:01 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement