set working set size Option

Use the set working set size option to reserve physical memory space for Microsoft® SQL Server™ that is equal to the server memory setting. The server memory setting is configured automatically by SQL Server based on workload and available resources. It will vary dynamically between min server memory and max server memory. Setting set working set size means Microsoft® Windows NT® 4.0 or Windows® 2000 do not swap out SQL Server pages even if they can be used more readily by another process when SQL Server is idle.

Do not set set working set size if you are allowing SQL Server to use memory dynamically. Before setting set working set size to 1, set both min server memory and max server memory to the same value, the amount of memory you want SQL Server to use.

set working set size is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change set working set size only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.

To set the working set size option