I would like to clarify my doubts related to memory.
The server is question is Windows 2003 32 bit enterprise Service Pack 2 (physical memory is: 64 GB) and has sql server 2000 SP4 (8.0.2039) 32 bit Enterprise.
The AWE and /PAE switch are enabled and the maximum server memory is set to 61440 MB.
When I look the Target server memory counter(SQl Server: Memory Manager) in perfmon, I am seeing 33287752 KB (maximum value). I was thinking the maximum value in the perfmon counter referenced here (Target server memory ) should be equivalent to maximum server memory set in server properties as Target server memory is the memory sql server can use. Is that not the case? Can
someone please clarify? Thanks in advance.
Let me preface this with I have not worked much with SQL Server 2000 so I'm going on KB articles. I did, however, find a kb article about settings needed for SQL Server 2000 to use AWE which obviously you have already done correctly or you wouldn't be using 32 GB of memory. I did notice though that under the heading of "Windows server 2000 advanced server / datacenter / server 2003" the maximum specified value for memory was 32GB. It may be an actual limitation of SQL Server 2000, I can't say for sure, but it's what I found.
Target server memory is not a fixed number, it's what SQL Server determines to be a good number based on what's available on the server and what else is going on.
In your case, do you have other workloads running on the same server?
No great genius has ever existed without some touch of madness. - Aristotle