SQL Server Performance

'set working set size' option with fixed memory

Discussion in 'Performance Tuning for DBAs' started by retroman, Feb 22, 2005.

  1. retroman New Member

    If a SQL Server is set with Fixed Memory (i.e Min Server Memory=Max Server Memory) must the 'set working set size' option set to 1. What if 'set working set size' is set 0?

    Don't quite understand the effect of enable/disable of 'set working set size' with fixed memory?

    Can someone enlighten me. Thank you.
  2. Luis Martin Moderator

    From BOL:

    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.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. retroman New Member

    I am using FIXED MEMORY (Min Server Memory=Max Sever Memory=1600MB), if ‘set working set size#%92 = 0 does that mean that physical memory space of 1600MB is not reserved for Microsoft® SQL Server?

    But, I can see from SQL Sever:Memory Manager Target Server Memory perf counter shown around 1600MB. My understanding, correct me if I am wrong, is this amount of memory (1600MB) is reserved for BPool usage upon SQL Server Startup. If it is, do I still need to set ‘set working set size#%92 = 1?
  4. Luis Martin Moderator

    What I understand is: Setting to 1 means, SQL don't swap pages even when SQL is idle. So I don't see any razon to set to 1.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  5. satya Moderator

    If you know the maximum amount of physical memory that SQL Server will use, you can specify this amount by setting 'min server memory' and 'max server memory' to the same value (equal to the maximum amount of physical memory that SQL Server will use) and set the 'set working set size' option to 1.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. retroman New Member

    Satya,

    Thanks for the reply.

    I understand you. What puzzle me is what happen if I dont set the 'set working set size' option to 1
    though I set the SQL Server to fixed memory.

    From some readings, it appeared to me that MS recommend set 'set working set size' option to 1 if you are using fixed memory. My real question is that a MUST (other than the reason of swap pages when SQL is idle) ?
  7. satya Moderator

    Currently if you're getting any performance hitch then you must test in considering using of SET WORKING SIZE, otherwise its ideal to leave the settings as is.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page