we have SQL Server installed on a Windows Server 2003 with 2GB of memory. Would it help if we enabled AWE? Or do we have to add more memory and then enable AWE?
As Concerned OfTunbridgeW says, SQL Server Standard won't use more than 2GB of memory even if you put in more memory and use the /3GB switch in boot.ini. Unless you have the (expensive!) Enterprise version there is no point in playing with AWE or the /3GB switch.
However note that if you have more than 2GB of physical memory SQL can use the max of 2GB and other apps still have access to the extra memory. That is, any one app (including SQL) can only use 2GB but all the apps combined can use up to 4GB or however much memory you have in the server. For example if you install a second copy of SQL as a named instance then both instances can use up to 2GB i.e. 4GB between them.
AWE only works on Windows 2003 Enterprise. Windows 2003 Server can only use 4GB of memory no matter how much is in your server.
I would agree that if you really need more than 2GB of memory then you should look at x64 Windows 2003 or 2008 and the x64 version of SQL Server.
To use more than 2GB of RAM on a SQL Server machine you have two options:
i. Rebuild the server with a 64 bit O/S and 64 bit SQL Server.
ii. Use AWE and configure the server to boot with the /3G switch. To do this you also need the enterprise edition of SQL Server. This will let you use up to 8GB of RAM on 2k3 Enterpise and possibly more on 2k3 Datacentre.