12

Recently I was performing an OS upgrade on one of our DB servers, moving from Server 2003 to Server 2008. The DBMS is SQL Server 2005. While reinstalling SQL on the new Windows installation, I went to another of our DB servers to verify a couple of settings.

Now, I always thought this second server was Server 2003 x64 + SQL 2005 x64 (from what I'd been told), but I now have my doubts about this. I now suspect that it is in fact only 32 bit SQL, however I'd like to verify this.

Here's some details:

The OS is definitely 64 bit.

xp_msver shows Platform as NT INTEL X86

SELECT @@VERSION shows Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)...

However sqlservr.exe is not shown with '* 32' in taskmgr, does anyone know why this is the case, if it is in fact 32 bit as claimed? Despite this, it does seem to be running out of the x86 program files folder.

If I do the same checks on a confirmed 64 bit installation, it does give back the expected 64 bit readings, which can only prove that this server in question is only running in 32 bit.

Now, that being the case, the question arises about how much memory this '32 bit' install can use. Task manager reports about 3.5GB memory usage for sqlservr.exe (The server has 16GB physical). I suspect that AWE has not been configured at all, and therefore the server will be significantly under-utilised (remembering that the OS is 64 bit) if SQL is simply using a 32bit address space.

Is this assumption correct?

I feel the server should have SQL reinstalled as 64 bit in order to fully utilise the hardware platform, however it is currently heavily in production; this will be no easy task. I suspect we may just have to configure AWE correctly and let it be for the time being (Unless this is a bad idea?).

I apologise that this question is a little vague/lost; I'm no SQL expert, just trying to get a handle on what's going on here.

ServerDB

Community
  • 1
CapBBeard
  • 948
  • 2
  • 9
  • 13

4 Answers4

15

This post lists two different ways to check (the first is the @@version, which shows you are running a 32-bit version of SQL Server), but to save clicking through,

select serverproperty('edition')

The result will look something like:

32-bit: Enterprise Edition

64-bit: Developer Edition (64-bit)

Sean Earp
  • 7,207
  • 3
  • 34
  • 38
4

You can also use 

USE master
SELECT @@Version

That will display something like - 

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
Feb 10 2012 19:39:15 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Mukus
  • 189
  • 7
1

In your install medium do you see an x64 or an x86 directory? If not I believe your medium will be 32-bit only.

This will explain why you only have a 32-bit version running on your 64-bit OS.

Is the disk a boxed purchase or from a MSDN or Technet download?

Wayne
  • 3,084
  • 1
  • 21
  • 16
  • I have no idea what install medium was used at the time, hence having to use other means of finding out the version. We have enterprise volume licensing so we have all sorts of discs here. – CapBBeard Jul 28 '09 at 04:39
1

I won't comment on whether or not you have 64bit or 32. You ask about AWE, so I'll answer that part as I have some experience here.

I've used AWE in similar situations and it has worked well for us temporarily.

In the end we did move to a fully 64 bit system of course, but AWE allowed us to use more RAM. Also look at the /3GB switch which goes in boot.ini if I recall. If you can test your install with AWE enabled before you swap that would obviously be beneficial. We asked our managed hosting provider to turn it on, and they had a DBA work with us who had some experience with that before. We scheduled the change over an early morning maintenance window, made the changes, rebooted, and started testing. It bought us quite a lot of performance actually too.

From what I recall, you could not easily see how much memory SQL Server used - the taskmgr.exe didn't tell the whole story. You have to run perfmon and actually drill in to the SQL server counters to see how much RAM SQL is actually getting access to.

I'd suggest you read up first, but it's a good route to go until you can resolve the situation more permanently.

http://blogs.msdn.com/chadboyd/archive/2007/03/24/pae-and-3gb-and-awe-oh-my.aspx http://msdn.microsoft.com/en-us/library/ms190673.aspx

Kyle
  • 1,849
  • 2
  • 17
  • 23