I am wondering if I should check the "Reserve physical memory for SQL Server" (set working set size) option in my sql server. Here is my configuration:
1. Win2k3, SQL Server 2000 Enterprise
2. 8GB memory, configured for dynamic, 0 = min 6144 = max
3. boot.ini contains /PAE (no /3GB switch, we removed it for OS performance reasons)
4. disk is a NAS
5. paging file for OS is 10GB
6. server is dedicated to running SQL Server only
I am wondering what this option will give me. I understand this option is valid only when not using dynamic memory. I also understand that not checking the option will mean that if SQL Server has old memory pages, and some other process, including the OS, needs more memory pages to do something, SQL Server will give up those pages to the requeting service. The OS will swap those SQL Server pages to the paging file so the requesting service can use the memory pages, and then SQL Server will have to swap back in to memory later when those memory pages become available again; conversely, checking the option means that SQL Server will never give up those pages, forcing the requesting service to swap itself instead of SQL Server.