December 8, 2016 at 1:30 pm
All your responses considered, I decided to act upon a variation offered by Jeff Moden.
In the SQL Server Properties GUI, Memory page, I set "Maximum server memory (in MB)" to the default when the server was installed: 2,147,483,647.
SQL Server responded by setting Target Server Memory in PERFMON to 154,953,144 KB.
I divided 154,953,144,000 by (1024)x(1024)x(1024) and got 144.311.
Interesting.
I don't know what that number represents but I'm sticking with it.
December 8, 2016 at 1:31 pm
Wow! 2 GB for an RDP session? I didn't know that. Thanks, Eric.
January 23, 2017 at 5:11 pm
Thanks to all of you for your responses. My initial post may have seemed a little flippant but it was not. I needed to determine an appropriate setting for "Maximum Server Memory (in MB)" for SQL Server.
Since my last post, I've been running an experiment on my employer's server. Rather than give all the details, I'm going to summarize my findings. I will preface them by stating that SQL Server has used all the RAM it would allow itself to use.
The server has 160 GB of physical RAM. This number was taken from the "System Information" program.
I allocated SQL Server 150,439 (in MB) on the Server Properties UI. The PERFMON program reports that number as 154,049,536 (KB) (SQL Server:Memory, Manager Target Server Memory). The difference between the 2 aforementioned numbers is ~3,610 (MB).
The PERFMON program has reported that SQL Server has used as much as 144,547,768 (KB) (SQL Server:Memory Manager, Total Server Memory).
Based upon my experiment, and having performed a numerical adjustment (~3,610 MB) to compensate for the difference in the values entered into the Server Properties UI and what has been captured by PERFMON, I have allocated SQL Server 142,000 (in MB) on the Server Properties UI. This value (adjusted) is slightly more than PERFMON shows that SQL Server has ever used.
----------------
I calculate that a gigabyte is 1024x1024x1024 = 1,073,741,824 bytes.
I divided 144,547,768,000 (from PERFMON, SQL Server:Memory, Manager Total Server Memory) by 1,073,741,824 giving a result of 134.621 GB.
Is this ~128 GB? I don't know, but it is the amount of RAM SQL Server is actually using.
January 24, 2017 at 3:19 am
I allocated SQL Server 150,439 (in MB) on the Server Properties UI. The PERFMON program reports that number as 154,049,536 (KB) (SQL Server:Memory, Manager Target Server Memory). The difference between the 2 aforementioned numbers is ~3,610 (MB).
The PERFMON program has reported that SQL Server has used as much as 144,547,768 (KB) (SQL Server:Memory Manager, Total Server Memory).
Based upon my experiment, and having performed a numerical adjustment (~3,610 MB) to compensate for the difference in the values entered into the Server Properties UI and what has been captured by PERFMON, I have allocated SQL Server 142,000 (in MB) on the Server Properties UI. This value (adjusted) is slightly more than PERFMON shows that SQL Server has ever used.
The maximum server memory configuration is not a hard limit on the amount of memory that will ever be used by the SQL Server process, but a limit on the amount of memory available to SQL Server to cache certain object types (think: data pages, CLR objects, etc). Have a look at this document for SQL 2016 memory limits, and this one on Memory configuration considerations.
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 24, 2017 at 5:35 am
Jeff Moden - Wednesday, December 7, 2016 4:55 PMGail Wanabee (12/7/2016)
So, what is the appropriate setting in the Server Properties GUI, Memory page section, for the "Maximum server memory (in MB):" entry, to max the RAM allocation for the Standard Edition of SQL Server?How much memory does the server actually have on it? If it's only 128GB, then you need to leave some room for the operating system. I'd leave at least 8GB which means that you'd set the max memory setting to 120000MB.Be VERY careful when making this change. If you fat finger it and only enter 120 or some other really low number, it will be very difficult to recover the SQL Server instance. Look 3 times on this change. Then look again. You cannot be too careful in making this settings change. Voice of experience right here. :pinch:
I have also done this (twice)!
single user mode time and its a nightmare
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply