Post reply

script for setting min and max memory of sql server

  • November 8, 2012 at 7:38 am

    #265183

    can someone provide a script for changing sqlserver memory .

    Setting min and max memory of sql server as example below.

    for 1 CPU server, we set sql server memory to 3.5 GB

    for 2 CPU server,we allocate 8 GB of Memory and set sql server memory as 7 GB .

    for 4 CPU server,we allocate 16 GB of Memory and set sql server memory as 14 GB .

  • November 8, 2012 at 7:46 am

    #1557185

    sp_configure 'min server memory (MB)', xxxxx

    go

    sp_configure 'max server memory (MB)', xxxxx

    go

    reconfigure

    go

  • November 8, 2012 at 11:54 pm

    #1557459

    the script should count the no of CPUs then allocate 80% of memory should be allocate to SQL Server.And min memory always should be 512MB.

  • November 9, 2012 at 2:16 am

    #1557500

    What are you classing as a CPU, a physical socket or a logical core?

  • November 9, 2012 at 5:33 am

    #1557577

    logical.

  • November 9, 2012 at 5:36 am

    #1557578

    DECLARE @a SMALLINT

    SELECT @a = cpu_count FROM sys.dm_os_sys_info

    IF @a = 1

    BEGIN

    EXEC sp_configure 'min server memory (MB)',xxxxx

    EXEC sp_configure 'max server memory (MB)',xxxxx

    RECONFIGURE

    END

    IF @a = 2

    BEGIN

    .........

    .........

    .........

    END

    IF @a = 4

    BEGIN

    ........

    ........

    ........

    END

  • November 9, 2012 at 6:22 am

    #1557585

    ok clue me in, because i'm not seeing it...

    what difference does it make on # of logical cores?

    why not calculate, say 7/8ths of the total available memory for the SQL Server,? it seems to me that you are inferring total memory based on # of cores, and not even checking the available memory at all...

    what if you had a developer machine, like mine, with 4 cores, but only 4 gig of ram?

    or a 4 core with 64 gig of ram?

    something like this make smore sense to me that counting logical cores:(and thank you anthony for thescript model to adapt!)

    DECLARE @logical_cpus SMALLINT,

    @total_mem decimal (19,4),

    @min-2 int,

    @max-2 int

    SELECT

    @logical_cpus = cpu_count,

    @total_mem = physical_memory_in_bytes

    FROM sys.dm_os_sys_info

    SELECT * FROM sys.dm_os_sys_info

    --saving 1/8 of mem for OS

    SELECT @min-2 = (@total_mem *(1.0 / 8.0)) /(1024 * 1024),--1022

    @max-2 = (@total_mem *(7.0 / 8.0))/(1024 * 1024) --7159

    print @min-2 --ie

    print @max-2

    print @total_mem -- (ie 7507372544.0000 out of 8579854336)

    IF @logical_cpus = 1

    BEGIN

    EXEC sp_configure 'min server memory (MB)',@min

    EXEC sp_configure 'max server memory (MB)',@max

    RECONFIGURE

    END

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 9, 2012 at 6:30 am

    #1557588

    thanks anthony.

  • November 9, 2012 at 6:31 am

    #1557589

    Thanks Lowel.Let me try both the scripts

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply