locked
How to Set (and get as well) Minimum server Memory and Maximum server Memory of a SQL Server Instance using T-SQL Query ? RRS feed

  • Question

  • I want to set Minimum and Maximum server Memory for a SQL Server using T-SQL. I am able to do it using SSMS->Object Explorer->Server Properties but don't know how to do it using T-SQL commands. 
    Along with this, also can anyone help me out with how to set 'Processor Affinity' in SSMS->Object Explorer->Server Properties  using T-SQL commands ? 

    Thanks in advance. 
    Thursday, November 10, 2016 9:51 AM

Answers

  • You need to use sp_configure to change server settings. Try the query below

    EXEC sys.sp_configure @configname = 'show advanced options', @configvalue = 1
    GO
    EXEC sys.sp_configure @configname = 'min server memory (MB)', @configvalue = 5120 --In MB
    GO
    EXEC sys.sp_configure @configname = 'max server memory (MB)', @configvalue = 1020140 --In MB
    GO
    EXEC sys.sp_configure @configname = 'show advanced options', @configvalue = 0
    GO

    There are configname for affinity mask which is "affinity mask" & "affinity64 mask" however the value you need to manipulate the data in bit and convert to decimal for affinity mask. Refer https://msdn.microsoft.com/en-us/library/ms187104.aspx article before you does a change to affinity mask and try it in Dev environment and test it thoroughly. 


    Mark as ANSWER if I helped you today :-) www.sql-articles.com


    • Edited by VidhyaSagar Thursday, November 10, 2016 10:09 AM User requested for affinity mask data as well
    • Marked as answer by _Anonymous_858585 Thursday, November 10, 2016 10:45 AM
    Thursday, November 10, 2016 10:06 AM
  • @VidhyaSagar

    EXEC sys.sp_configure @configname = 'show advanced options', @configvalue = 1; Reconfigure with override;
    GO
    EXEC sys.sp_configure @configname = 'min server memory', @configvalue = 0 --In MB
    GO
    EXEC sys.sp_configure @configname = 'max server memory', @configvalue = 2147483647 --In MB
    GO
    EXEC sys.sp_configure @configname = 'show advanced options', @configvalue = 0; Reconfigure with override;
    GO
    

    I tried the above commands and it worked great :) Thanks a lot for the kind help. 

    Thursday, November 10, 2016 10:45 AM

All replies

  • You need to use sp_configure to change server settings. Try the query below

    EXEC sys.sp_configure @configname = 'show advanced options', @configvalue = 1
    GO
    EXEC sys.sp_configure @configname = 'min server memory (MB)', @configvalue = 5120 --In MB
    GO
    EXEC sys.sp_configure @configname = 'max server memory (MB)', @configvalue = 1020140 --In MB
    GO
    EXEC sys.sp_configure @configname = 'show advanced options', @configvalue = 0
    GO

    There are configname for affinity mask which is "affinity mask" & "affinity64 mask" however the value you need to manipulate the data in bit and convert to decimal for affinity mask. Refer https://msdn.microsoft.com/en-us/library/ms187104.aspx article before you does a change to affinity mask and try it in Dev environment and test it thoroughly. 


    Mark as ANSWER if I helped you today :-) www.sql-articles.com


    • Edited by VidhyaSagar Thursday, November 10, 2016 10:09 AM User requested for affinity mask data as well
    • Marked as answer by _Anonymous_858585 Thursday, November 10, 2016 10:45 AM
    Thursday, November 10, 2016 10:06 AM
  • Hello,

    For this you can use sp_configure (Transact-SQL); see also Server Memory Server Configuration Options => Examples


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, November 10, 2016 10:06 AM
  • Hi VidhyaSagar, Thanks for your valuable response. While executing this sp, I am getting an error "The configuration option 'min server memory (MB)' does not exist, or it may be an advanced option." Please help me to get it fixed. 

    In addition I want to add, I don't know it is useful or not, however I am using SQL Server 2014 on a 8GB RAM with 64 bit Windows 7 combo. 

    Thursday, November 10, 2016 10:13 AM
  • Its an advanced property. Thats why in the query you got show advanced option used. You need to run the first line to set min and max memory.

    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    Thursday, November 10, 2016 10:16 AM
  • @VidhyaSagar

    EXEC sys.sp_configure @configname = 'show advanced options', @configvalue = 1; Reconfigure with override;
    GO
    EXEC sys.sp_configure @configname = 'min server memory', @configvalue = 0 --In MB
    GO
    EXEC sys.sp_configure @configname = 'max server memory', @configvalue = 2147483647 --In MB
    GO
    EXEC sys.sp_configure @configname = 'show advanced options', @configvalue = 0; Reconfigure with override;
    GO
    

    I tried the above commands and it worked great :) Thanks a lot for the kind help. 

    Thursday, November 10, 2016 10:45 AM
  • @VidhyaSagar

    Just a bit more confusion, Can I set this limit for a particular database level or query level or stored procedure level ? 

    Friday, November 11, 2016 5:17 AM
  • @VidhyaSagar

    Just a bit more confusion, Can I set this limit for a particular database level or query level or stored procedure level ? 

    You cannot set limit on memory for a database however you can set maximum memory that can be granted to query or group of queries and this can be done by configuring resource governor. This is not a straight forward query. You would have to define workload group and the assign maximum memory to it.

    Restricting SQL Server memory using RG


    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, November 11, 2016 5:28 AM