4

We have a "SQLServer 2005 SP3 32bit Enterprise Edition" running on a 32 bit Windows 2003 32bit Enterprise Edition 12GB RAM with AWE enabled using RAID5(5 pysical disks).

We tuned AWE to enabled and restart sqlserver this afternoon after work, hope the performance will be better than old time.

But there is something that we are very confused.

On working days, SQLServer has a very bad performance. When we are looking for reasons, we check Windows Performance counter.

Avg. Disk Read Queue Lenght > 140
Avg. Disk Write Queue Length < 1
SQL Server Buffer Cache Hit Ratio > 96%
%Processor Time < 30%
SQL Server Total Server Memory < 1.8G

Obviously, without AWE enabled, SQL Server can use only less than 2G memory. My Question is:

  1. why "SQL Server Total server Memory" is less than 2G?I think SQL Server will use all 2G process address space. Does this counter count anything out?
  2. we known that sql server is sufferring lack of memory, but why "buffer hit ratio“ is as high as 96?

Any advice is welcomed!

2 Answers 2

3

To take advantage of the extra RAM you need to enable PAE as well as AWE.
http://support.microsoft.com/?kbid=283037

Additionally, you should allow the account that is running your SQL services the "lock pages in memory" option.
http://msdn.microsoft.com/en-us/library/ms190730.aspx

The Buffer Cache Hit Ratio value isn't that far out of bounds. You generally want this number > 95%. However, this counter is hard to read as it depends a lot on how many different things are going on in how many DB's and how much I/O is queued up (all at once). Jonathan Kehayias over at Simple-Talk discusses this topic very well.

Great SQL Server Debates: Buffer Cache Hit Ratio by Jonathan Kehayias
http://www.simple-talk.com/sql/database-administration/great-sql-server-debates-buffer-cache-hit-ratio/

0
0

Maybe I'm missing something but this seems to be an issue of disk contention. What is on the raid 5 array? Temp, user DB's, transaction logs?

1
  • thanks.It's a server used for developers, So, TempDB, UserDB, Log are all on the same raid. And We know that there is a disk issue , or more persisely, memory issue. My question is , why performance counters are "not in the right position"?
    – chenwq
    Mar 31, 2012 at 7:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .