SQL Server 2005 maximizing performance

Question

Our customer has a Microsoft SQL Server 2005 on Windows 2003 Server Enterprise, R2, SP2, 32bit. Running on Intel Xeon X5560 @2.80GHz, with 8 GB of RAM.

Occasionally, the users complained that the access to the server was slow. Although, that might be affected by other factors like workstation hardware, antivirus, etc.

But, I am thinking of maximizing the SQL Server itself.

I read about the "SQL AWE" option which could increase the memory allocation that can be used on the 32 bit OS.

Right now, the AWE is not enabled, I checked using "dbcc memorystatus", and I got result:

Memory Manager:
VM Reserved = 1694984 KB
VM Committed = 1691528 KB
AWE Allocated = 0
Reserved Memory = 1024
Reserved Memory In Use = 0

As I understood correctly, this 8GB of RAM is not being used optimally due to the 32bit OS.

Am I in the right direction here? I meant, if I enabled the SQL AWE, it will improve the SQL performance.

Note that I cannot change the hardware, and I cannot change the OS to 64 bits (not supported by our application).

Thanks.

Answer 1

Occasional end user complaints about "slow" isn't an indication of a SQL Server performance issue.

However, enabling this will improve caching of data but adds some small overhead of managing PAE. AWE requires PAE too.

Answer 2

And a good bit of slow performance in databases is due to bad indexing or badly performing queries. No amount of tweaking the server itself will fix those. Findout what they are doing when it gets slow. Don't just randomly fix things and hope that things improve. Know what is causing the issue and measure before and after to make sure you have improved.

Answer 3

My advice is to find the root cause. To confirm or eliminate sql server is the problem there is a tool that's easy to use and will give decisive insight in how SQL Server is performing. That tool is called "SQL Profiler" and comes installed with a default client installation.

The duration will tell you how long each and every query takes. If its as expected (performant) then you can rule out SQL Server but not the connection of the server to the client. It could be that there is a network issue. My assumption is that the duration column is independent of any network issues, maybe someone else can confirm this.

Answer 4

Did you also check other might-be performance issues like indexes, query tuning, etc? If yes, then this might help (from serverfault.com). Question is:

How much memory can SQL Server 2005 x86 use when installed on Windows Server 2008 x64?

https://serverfault.com/questions/95794/how-much-memory-can-sql-server-2005-x86-use-when-installed-on-windows-server-200

There are links in the answers that might give you solutions.