We've a SQL Server 2005 with SP4 running on Server 2003 R2 x64 Standard Edition with SP2. All Software is Up to Date. The server is Dell PowerEdge 1950 with 2 Processors (4 cores each) with 73GB of primary SAS and 500GB of NAS Storage Array (Connected via SCSI) and contains 16GB of RAM. The SQL Server 2005 is a 64-bit Standard edition.
The number of concurrent connections, users are same from past couple of years and nothing changed in terms of either software, hardware and users.
From past few weeks, the Memory usage is goes to 100% as per Performance monitor. I verified the pagefile size and it is set to 2GB. I heard that I need to set pagefile size to minimum of 1.5 times RAM.
I already specified maximum memory usage in SQL Server properties to 12GB a long time back. I even tried to increase and decrease it, but the result is same!
Since this server is running on Server 2003 Standard edition, it only supports 32GB of RAM. I already ordered another 16GB modules from Dell.
1.) [250 Points] What is the recommended pagefile size for this kind of environment? Do I need to specify 24GB (1.5 x 16GB RAM) of pagefile?
2.) [250 Points] How to determine the SQL Server Performance Degradation? i.e., RAM is sufficient or not?
Any further help would be greatly appreciated. Thank you.