HOW TO: Determine If You Are Running A 32-bit Version Of SQL Server On A 64-bit O/S

Last week a question came up regarding the ability for a SQL Server 32-bit instance to address more than 4Gb of RAM when running on top of a 64-bit operating system (O/S). My friend Argenis Fernandez (blog | @DBArgenis) has already blogged the answer to this question so I won’t bother repeating those details here.

However, I do think there is something additional that needs to be mentioned. I still see many customers and clients that have servers with memory configuration issues. I walk them through some of the basic questions to start with. I ask for information on the version and edition of SQL, version and edition of the O/S, the amount of physical RAM, what other applications are using the server, etc. But there is one question that I was not thinking about previously, and it was the post by Argenis that spurred this on.

How many folks out there are running (either on purpose or by accident) a 32-bit version of SQL Server on a 64-bit O/S? And for those that are running such a setup, do you know if you have AWE enabled? Not having AWE enabled is not, by itself, an issue necessarily. But I have seen clients and customers not take this extra necessary step when managing their instance of SQL Server. They add server memory and then assume that SQL Server will use what is available.

So I decided to put together some queries to help you figure this out. I’m sure someone could do this in a handful of lines with Powershell and the IsWow64Process function but my goal was to give some straight T-SQL in case Powershell is not an option for you.

My idea is simple enough:

1. Determine if I have a 32-bit version of SQL Server
2. Determine if that 32-bit version is running on a 64-bit O/S
3. Determine if that 32-bit version running on a 64-bit O/S has AWE enabled

The first script is very easy to decipher:

SELECT SERVERPROPERTY('Edition')

If my version of SQL Server is 64-bit, then it will display ‘(64-bit)’ after the name of the edition. That seems simple enough for step so let’s move on to step 2. We’ll need to do some extra work here. I want to use straight T-SQL, no shelling out to the O/S, no extended procedures either. Just T-SQL that anyone can run. That means I need to do some parsing of a string:

SELECT RIGHT(SUBSTRING(@@VERSION, CHARINDEX('<', @@VERSION), 4), 2)

That query should return either a 64 (for 64-bit versions of the O/S) or an 86 (for 32-bit versions). That seems simple enough for us as well, so let's move on to step 3. Now we just need to determine if AWE has been enabled for the instance:

SELECT value_in_use 
FROM sys.configurations
WHERE name = 'awe enabled'

You should note that if you are running SQL Server 2012 you won't find the 'awe enabled' option listed in sys.configurations since SQL 2012 doesn't support AWE and that configuration option has been removed.

Let's put this all together then:

DISCLAIMER: Do not run code you find on the internet in your production environment without testing it first. Do not use this code if your vision becomes blurred. Seek medical attention if this code runs longer than four hours. On rare occasions this code has been known to cause one or more of the following: nausea, headaches, high blood pressure, popcorn cravings, and the impulse to reformat tabs into spaces. If this code causes your servers to smoke, seek shelter. Do not taunt this code.

/*=============================================
  File: SQL_Server_without_AWE_WOW64_check.sql

  Author: Thomas LaRock, https://thomaslarock.com/contact-me/
  

HOW TO: Determine If You Are Running A 32-bit Version Of SQL Server On A 64-bit O/S

  Summary: This script will check to see if you have an instance of SQL
        Server 32-bit running on a 64-bit O/S without AWE enabled. 

  Date: January 9th, 2013

  SQL Server Versions: SQL2005, SQL2008, SQL2008R2, SQL2012

  You may alter this code for your own purposes. You may republish
  altered code as long as you give due credit. 

  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
  OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
  LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
  FITNESS FOR A PARTICULAR PURPOSE.

=============================================*/

WITH Bit_Version (os_bit_ver, sql_bit_ver, name, value_in_use)
AS
(
 SELECT RIGHT(SUBSTRING(@@VERSION, CHARINDEX('<', @@VERSION), 4), 2), 
 RIGHT(SUBSTRING(CONVERT(varchar,SERVERPROPERTY('Edition'))
      , CHARINDEX('(', CONVERT(varchar,SERVERPROPERTY('Edition'))), 3), 2),
 name, value_in_use
 FROM sys.configurations
 WHERE name = 'awe enabled' 
)
SELECT CASE
 WHEN os_bit_ver = '64' AND sql_bit_ver <> '64' AND value_in_use = 0 THEN 'BAD!'
 ELSE 'GOOD!'
 END AS [IsThisABadThing]
FROM Bit_Version

If you are running a 32-bit version of SQL Server on a 64-bit O/S and you do not have AWE enabled then you should see the result of 'BAD!' returned. I thought about simply having this return a 'BAD!' regardless of AWE configuration, but you are free to modify as you see fit. And remember that you won't get any rows returned for instances of SQL 2012 (since there is no 'awe enabled' option).

Enjoy!