Calculate the values for MAX Memory for SQL Server

  • Rudy Panigas

    SSChampion

    Points: 10695

    Comments posted to this topic are about the item Calculate the values for MAX Memory for SQL Server

    Rudy

  • pkrudysz

    Right there with Babe

    Points: 742

    "Reserve 1 Gigabyte (GB) of RAM for the Operating System (OS)"

    Are you sure? Shouldn't it be 2GB?

  • Rudy Panigas

    SSChampion

    Points: 10695

    Yes this is correct as per the authors of the book. Also, there is other memory reserved it not just 1GB of RAM.

    Rudy

  • Tec_Chief

    SSC Enthusiast

    Points: 115

    Great Script.. Thank You. Can you add direction on how to take your script results and have it use the MAX MEM (SQLMaxMemoryMegaByte) results to automatically change/update the SQL settings (i.e. sp_configure max server memory)?

  • scweichel

    SSC Journeyman

    Points: 77

    Change these two lines in your script to make it 2012 compatible -

    -- Read physical memory on server

    SET @TotalMEMORYinBytes = (select physical_memory_kb from sys.dm_os_sys_info)

    -- Coverting value from bytes to megabytes

    SET @TotalMEMORYinMegaBytes = (@TotalMEMORYinBytes /(1024))

  • Rudy Panigas

    SSChampion

    Points: 10695

    Tec_Chief (4/30/2012)


    Great Script.. Thank You. Can you add direction on how to take your script results and have it use the MAX MEM (SQLMaxMemoryMegaByte) results to automatically change/update the SQL settings (i.e. sp_configure max server memory)?

    Hello,

    Not sure why you want to automatically change the memory setting. Since you don't need to do this often, I would recommend just executing the script and then copy/paste the results to make you memory change.

    Thanks,

    Rudy

  • Rudy Panigas

    SSChampion

    Points: 10695

    scweichel (6/26/2012)


    Change these two lines in your script to make it 2012 compatible -

    -- Read physical memory on server

    SET @TotalMEMORYinBytes = (select physical_memory_kb from sys.dm_os_sys_info)

    -- Coverting value from bytes to megabytes

    SET @TotalMEMORYinMegaBytes = (@TotalMEMORYinBytes /(1024))

    Thanks for the update and it seem to work just fine in 2012 now 🙂

    Rudy

  • nisha_rana

    SSC Enthusiast

    Points: 149

    Thank you for the scripts. It was a great help for me.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

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

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