none
Contact SP_Configure "Max Server Memory"

    Question



  • 1. It is dedicated to SQL Server.

    2. OS: Windows 2008 R2 Enterprise (64bit) SP1


    3. System: CPU (8 cores), memory (32GB)

    4. SQL version: SQL Server 2008 Standard (32bit) SP3 (10.0.5500.0)

    5. Change Max Sever Memory

    Sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    Sp_configure 'max server memory (MB)', 29000;
    GO
    RECONFIGURE WITH OVERRIDE
    GO
    I tried to change the above.

    However, the configuration value is changed but the execution value is not changed as shown below.

    Name: Max Server Memory (MB)
    Minimum: 16

    Maximum: 2147 ~~~
    Config_value: 29000
    Run_Value: 12288


    3. RECONFIGURE;
        
    The configuration option 'max server memory (MB)' has been changed from 29000 to 29000. Install it by running the RECONFIGURE statement.
    Msg 5593, Level 16, State 1, Line 1
    The FILESTREAM feature is not supported in WoW64. The feature is disabled.


    ================================================== ======

    What else do you need to check?

    Help.

    The key is that the run_value will not change (even after rebooting).

    At present, I am assuming that Windows (64bit) uses SQLServer (32bit) and Wow64 because it uses Windows.

    I can not find the above case and solution even if I look at the data.

    ================================================== ======

    Friday, March 17, 2017 3:18 AM

All replies

  • You have 32 bit SQL Server instance on 64 bit system in such case SQL Server cannot use more memory than 4 G in normal circumstances so their is NO point in giving 29G. You can enable AWE so that data and index pages can use RAM > 4 G but again this is limited to data and index pages not to other caches. My suggestion change to 64 bit and then SQL Server can use lot of memory. 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, March 17, 2017 5:02 AM
    Moderator
  • ===========================================

    It is in the current state.

    name                                              config_value            run_value

    awe enabled                                           1                            1

    max server memory (MB)                   29000                    12288

    ============================================

    Restart SQL Server -> Same as above

    H / W server reboot -> Same as above

    I am looking for a way to work around a 32-bit SQL instance.
    It is currently hard to replace with a 64-bit SQL instance.

    Friday, March 17, 2017 6:25 AM
  • ===========================================

    It is in the current state.

    name                                              config_value            run_value

    awe enabled                                           1                            1

    max server memory (MB)                   29000                    12288

    ============================================

    Restart SQL Server -> Same as above

    H / W server reboot -> Same as above

    I am looking for a way to work around a 32-bit SQL instance.
    It is currently hard to replace with a 64-bit SQL instance.

    T Now what I was pointing is with AWE enabled SQL Server can use memory more than 4 G (the default limit) but only data and index pages would use this memory not other caches like plan ,procedure etc. 

    To be precise SQL Server can use 64 GB with AWE enabled but like I said with above limitations, this will make things a bit faster for you but not as compared to 64 bit SQL Server

    There is NO workaround for this, i am sorry. You would hardly see people using 32 bit SQL Server


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Friday, March 17, 2017 6:36 AM
    Moderator
  • Hello podongpodong,

    Would you be able to run the below and paste the output if possible?

    ->

    EXEC sys.sp_configure N'max server memory (MB)', N'11264'
    GO
    EXEC sys.sp_configure N'max server memory (MB)'
    GO
    RECONFIGURE WITH OVERRIDE
    GO
    EXEC sys.sp_configure N'max server memory (MB)'
    GO
    EXEC sys.sp_configure N'max server memory (MB)', N'13312'
    GO
    EXEC sys.sp_configure N'max server memory (MB)'
    GO
    RECONFIGURE WITH OVERRIDE
    GO
    EXEC sys.sp_configure N'max server memory (MB)'
    GO

    Friday, March 17, 2017 6:55 AM
  • Sorry I missed the thing where you have  different value for config and run value. This should set the value as 29000

    sp_configure 'max server memory (mb)', 29000
    go
    reconfgure
    go


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, March 17, 2017 7:11 AM
    Moderator
  • This is the result of the execution.

    The configuration option 'max server memory (MB)' has been changed from 13312 to 11264. Install it by running the RECONFIGURE statement.
    Name minimum maximum config_value run_value
    ----------------------------------- ----------- ---- ------- ------------ -----------
    Max server memory (MB) 16 2147483647 11264 12288

    Msg 5593, Level 16, State 1, Line 1
    The FILESTREAM feature is not supported in WoW64. The feature is disabled.
    Name minimum maximum config_value run_value
    ----------------------------------- ----------- ---- ------- ------------ -----------
    Max server memory (MB) 16 2147483647 11264 12288

    The configuration option 'max server memory (MB)' has been changed from 11264 to 13312. Install it by running the RECONFIGURE statement.
    Name minimum maximum config_value run_value
    ----------------------------------- ----------- ---- ------- ------------ -----------
    Max server memory (MB) 16 2147483647 13312 12288

    Msg 5593, Level 16, State 1, Line 1
    The FILESTREAM feature is not supported in WoW64. The feature is disabled.
    Name minimum maximum config_value run_value
    ----------------------------------- ----------- ---- ------- ------------ -----------
    Max server memory (MB) 16 2147483647 13312 12288



    And change the run_value does not change
    I restarted the server and the SQL service
    The results do not change.
    Friday, March 17, 2017 7:24 AM
  • And I am Korean, so even if the context is difficult, please understand.
    Friday, March 17, 2017 7:25 AM
  • And I am Korean, so even if the context is difficult, please understand.
    Friday, March 17, 2017 7:25 AM
  • -> It seems like you are getting the below error when running the "reconfigure with override" which seems strange...

    Msg 5593, Level 16, State 1, Line 1
    The FILESTREAM feature is not supported in WoW64. The feature is disabled.

    -> Are you getting the same error when running just "reconfigure" instead of "reconfigure with override". try this and give me the output..

    EXEC sys.sp_configure N'max server memory (MB)', N'11264'
    GO
    EXEC sys.sp_configure N'max server memory (MB)'
    GO
    RECONFIGURE
    GO
    EXEC sys.sp_configure N'max server memory (MB)'
    GO

    -> Are you getting the filestream related error for other setting also when set using sp_configure?

    -> Run the below code with "CTRL + T" and send the output,

    sp_configure 'show advanced options',1
    go
    reconfigure
    go
    sp_configure

    -> Can you try changing the max server memory from GUI. RIGHT click SQL server instance from SSMS and click properties -> Click on "Memory" -> Change "Maximum server memory (in MB):" on the right side to 29000 and click ok.

    Now execute , EXEC sys.sp_configure N'max server memory (MB)'
    GO


    Regards;
    -------------
    Vivek Janakiraman, https://jbswiki.com/

    Friday, March 17, 2017 8:06 AM
  • EXEC sys.sp_configure N'max server memory (MB)', N'11264'

    GO

    EXEC sys.sp_configure N'max server memory (MB)'

    GO

    RECONFIGURE

    GO

    EXEC sys.sp_configure N'max server memory (MB)'

    GO

    ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

    The configuration option 'max server memory (MB)' has been changed from 11264 to 11264. Install it by running the RECONFIGURE statement. Name minimum maximum config_value run_value ----------------------------------- ----------- ---- ------- ------------ ----------- Max server memory (MB) 16 2147483647 11264 12288 Msg 5593, Level 16, State 1, Line 1 The FILESTREAM feature is not supported in WoW64. The feature is disabled. Name minimum maximum config_value run_value ----------------------------------- ----------- ---- ------- ------------ ----------- Max server memory (MB) 16 2147483647 11264 12288

     

     

     

    sp_configure 'show advanced options',1

    go

    reconfigure

    go

    sp_configure

    ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

    Configuration option "show advanced options" changed from 1 to 1. Install it by running the RECONFIGURE statement. Msg 5593, Level 16, State 1, Line 1 The FILESTREAM feature is not supported in WoW64. The feature is disabled.

    name                                minimum     maximum     config_value run_value

    ----------------------------------- ----------- ----------- ------------ -----------

    access check cache bucket count     0           16384       0            0

    access check cache quota            0           2147483647  0            0

    Ad Hoc Distributed Queries          0           1           0            0

    affinity I/O mask                   -2147483648 2147483647  0            0

    affinity mask                       -2147483648 2147483647  0            0

    Agent XPs                           0           1           0            1

    allow updates                       0           1           1            0

    awe enabled                         0           1           1            1

    blocked process threshold (s)       0           86400       0            0

    c2 audit mode                       0           1           0            0

    clr enabled                         0           1           1            1

    cost threshold for parallelism      0           32767       5            5

    cross db ownership chaining         0           1           0            0

    cursor threshold                    -1          2147483647  -1           -1

    Database Mail XPs                   0           1           0            0

    default full-text language          0           2147483647  1042         1042

    default language                    0           9999        29           29

    default trace enabled               0           1           1            1

    disallow results from triggers      0           1           0            0

    filestream access level             0           2           2            0

    fill factor (%)                     0           100         0            0

    ft crawl bandwidth (max)            0           32767       100          100

    ft crawl bandwidth (min)            0           32767       0            0

    ft notify bandwidth (max)           0           32767       100          100

    ft notify bandwidth (min)           0           32767       0            0

    index create memory (KB)            704         2147483647  0            0

    in-doubt xact resolution            0           2           0            0

    lightweight pooling                 0           1           0            0

    locks                               5000        2147483647  0            0

    max degree of parallelism           0           64          0            0

    max full-text crawl range           0           256         4            4

    max server memory (MB)              16          2147483647  11264        12288

    max text repl size (B)              -1          2147483647  65536        65536

    max worker threads                  128         32767       0            0

    media retention                     0           365         0            0

    min memory per query (KB)           512         2147483647  1024         1024

    min server memory (MB)              0           2147483647  0            8

    nested triggers                     0           1           1            1

    network packet size (B)             512         32767       4096         4096

    Ole Automation Procedures           0           1           0            0

    open objects                        0           2147483647  0            0

    optimize for ad hoc workloads       0           1           0            0

    PH timeout (s)                      1           3600        60           60

    precompute rank                     0           1           0            0

    priority boost                      0           1           0            0

    query governor cost limit           0           2147483647  0            0

    query wait (s)                      -1          2147483647  -1           -1

    recovery interval (min)             0           32767       0            0

    remote access                       0           1           1            1

    remote admin connections            0           1           0            0

    remote login timeout (s)            0           2147483647  20           20

    remote proc trans                   0           1           0            0

    remote query timeout (s)            0           2147483647  0            0

    Replication XPs                     0           1           0            0

    scan for startup procs              0           1           1            1

    server trigger recursion            0           1           1            1

    set working set size                0           1           0            0

    show advanced options               0           1           1            1

    SMO and DMO XPs                     0           1           1            1

    SQL Mail XPs                        0           1           0            0

    transform noise words               0           1           0            0

    two digit year cutoff               1753        9999        2049         2049

    user connections                    0           32767       0            0

    user options                        0           32767       0            0

    xp_cmdshell                         0           1           0            0

     

     

     

    -> Can you try changing the max server memory from GUI. RIGHT click SQL server instance from SSMS and click properties -> Click on "Memory" -> Change "Maximum server memory (in MB):" on the right side to 29000 and click ok.

    ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓


     

     

     

    Now execute , EXEC sys.sp_configure N'max server memory (MB)'

    GO

    ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

    name                                minimum     maximum     config_value run_value

    ----------------------------------- ----------- ----------- ------------ -----------

    max server memory (MB)              16          2147483647  29000        12288

     

     

    Friday, March 17, 2017 9:04 AM

  • ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

    name                                minimum     maximum     config_value run_value

    ----------------------------------- ----------- ----------- ------------ -----------

    max server memory (MB)              16          2147483647  29000        12288

     

     

    From sp_configure output I can see 

    max server memory (MB)              16          2147483647  11264        12288

    But from above I can see config_value as 29000 and run value as 12288 are both outputs from same SQL Server.

    If I understand your question correctly you want to change max server memory to 29 G correct ? But somehow you are not able to. 

    For last time run

    exec sp_configure 'max server memory (MB)', 29000
    go
    reconfgure with override
    go

    and then restart the SQL Server instance. After this run

    sp_readerrorlog
    and post the content of log file here


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Friday, March 17, 2017 9:46 AM
    Moderator
  • Hello podongpodong,

    If you look at your sp_configure output for "Filestream access level",

    sp_configure 'filestream access level'

    name                                minimum     maximum     config_value run_value
    ----------------------------------- ----------- ----------- ------------ -----------
    filestream access level             0           2           2            0

    -> The Config_value is set to 2. i think someone has enabled it by mistake. We all know that filestream will not work in wow64.

    -> perform the below and that will resolve the issue.

    sp_configure 'filestream access level',0
    GO

    reconfigure with override
    GO


    Sp_configure 'max server memory (MB)', 29000;
    GO
    RECONFIGURE WITH OVERRIDE
    GO

    Let me know how it goes...

    Regards;
    -------------
    Vivek Janakiraman, https://jbswiki.com/

    Friday, March 17, 2017 11:17 AM
  • Wow ~~~~~

    Why did I miss this?
    Do not you know who set it?

    Thank you very much

    I have not applied to Real DB yet.
    I applied it to a test DB in a similar environment. Resolved

    Real DB seems to be solved.

    Thanks again.

    If applying to Real DB does not work
    I will add a comment.


    Monday, March 20, 2017 4:19 AM
  • Glad it worked. We will check further if the problem is not resolved in your Real DB.
    Mark my reply as "Answered" if everything works fine, which will help others looking at the forum in future.

    Regards;
    -------------
    Vivek Janakiraman, https://jbswiki.com/

    Monday, March 20, 2017 12:09 PM