SQL Server Management Studio – SSMS is a 32 bit application

SQL Server Management Studio – SSMS is a 32 bit application. Even when we install 64 bit SQL Server Database Engine, we will get 32 bit SSMS installed on the server.

Default SSMS install location

32 Bit Server	C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE
64 Bit Server	C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE

Missing performance counters X64 OS with SQL2000 and SQL 2005

If you’re using x64 Windows and x64 SQL Server you shouldn’t have any issues. System Monitor will by default have the correct counters loaded and available for you to view and the same applies to IA64 Windows and IA64 SQL Server deployments.

However, if you’re using SQL Server in a Windows-On-Windows (WOW) mode on x64 (which is to say that x64 Windows is emulating a 32-bit environment to facilitate you running an x86 instance of SQL Server), When you start System Monitor on the x64 host, you’ll find that none of the counters relating to the 32-bit SQL Server instance are available. This is be cause on an x64 host you’ll be running an x64 version of System Monitor by default.

In the logfile I overlooked Event ID 1021:

Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a 64-bit environment. Contact the file vendor to obtain a 64-bit version. Alternatively, you can open the 32-bit extensible counter DLL by using the 32-bit version of Performance Monitor. To use this tool, open the Windows folder, open the Syswow64 folder, and then start Perfmon.exe.

Resolution:
Start C:\WINDOWS\SysWOW64\Perfmon.exe, this is the 32-bit version, and all the counters are there.


Monitoring the 32-bit SQL Server instance in the scenario will require running a 32-bit instance of System Monitor, and this can be achieved by running the following

mmc /32perfmon.msc

Once the 32-bit MMC console has started with the Perfmon snap-in, the 32-bit SQL Server counters should be available for addition to your log.

SQL Server 2005 64 Bit Standard Edition Memory

1)Do we really need AWE to get best performance on SQL 2005 x64 bit Standard Edition ?

No. AWE is not required on 64 bit operating system.


2) Can we configure AWE on SQL 2005 x64 bit Standard Edition?

Yes, if the O.S is 32 bit and memory you have is more than 4 GB.

No, if the O.S is 64 bit O.S.

3) Before configuring AWE we need to enabled Lock Pages in Memory Policy,

Can we configure this Policy with SQL Server 2005 64 bit Standard Edition?

Yes, if the O.S is 32 bit and memory you have is more than 4 GB.

SQL Server 2005 Standard edition do not support "Lock Pages in Memory" privileges on the SQL Service account.

Check on the SQL Server ERRORLOG if you get the below message
"Using locked pages for buffer pool". It confirms SQL Server make uses the "Lock Pages in Memory" privileges.


Reference:

SQL Server only uses 2 GB of memory even though the AWE option is enabled

http://support.microsoft.com/kb/811891/en-us
http://support.microsoft.com/kb/918483






It applies only to Microsoft SQL Server 2005 Enterprise Edition