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:
- 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?
- we known that sql server is sufferring lack of memory, but why "buffer hit ratio“ is as high as 96?
Any advice is welcomed!
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/
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?