How many megabytes is 128 GB?

Question

Post reply

How many megabytes is 128 GB?

Gail Wanabee

SSCrazy Eights

Points: 8471
More actions
December 7, 2016 at 3:12 pm

#401175

I'm about to max out the RAM allocation on a Standard Edition of SQL Server for the first time in my DBA career. I've mostly used SQL Server Enterprise Editions and never had to consider the contractual maximum specified by any licensing agreement.
The licensing agreement says words something like "128 GB" specify the maximum RAM allocation.
Being into specifics and generally avoiding ambiguities, I looked at the server Properties GUI for Memory and it wants the setting in MBytes.
Hmmm...
Being a hairsplitter by nature, I began to think and split hairs.
First thought: Set it to 128,000 MB. That was easy.
Second thought: How big is just 1 MB? Is it 1,000,000 bytes or is it actually 1,048,576 bytes (1024x1024)?
Third thought: If the answer to the second question is 1,048,576 bytes, then the mythical, contractually referenced "GB" must actually be 1,073,741,824 (1024x1024x1024) bytes (instead of 1,000,000,000), in which case, 128 GB is actually 1,073,741,824 bytes x 128 = 137,438,953,472 bytes.
Fourth thought: If the license agreement allows us to use 128 GB calculated as powers of 2, then I should set the Memory section of the Server Properties GUI to 137,438,953,472 bytes. Since the GUI will only allow for an entry in MB, I should probably round down to 137,437 MB.
Fifth thought: Maybe I should use the default setting of 2147483647 MB and let SQL Server decide what is the max RAM setting, since I can “trust it" to give us what we're contractually entitled to.
Sixth thought: I should post this on SQL Server Central and ask how you people have handled it.

Viewing 15 posts - 1 through 15 (of 19 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

If you want to play around with converting these, just google:

128 GB to MB

You get a little input box thing where you can enter your numbers and whatever you want to convert to.

Sue

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 2

Thanks but it's not a math issue.

The issue is what does Microsoft mean in its licensing agreement when it states 128 GB?

Sue_H SSC Guru Points: 90860 More actions · Answer 3

Gail Wanabee (12/7/2016)
Thanks but it's not a math issue.
The issue is what does Microsoft mean in its licensing agreement when it states 128 GB?

It actually doesn't say: 128 GB

And nothing else. Read the document.

128 is the max memory supported for that version/edition. I believe the current docs list it as Max Memory

Utilization - DBE

Sue

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 4

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?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Gail 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:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 6

The server has 160 GB of physical RAM.

P.S. Thanks for the warning. Don't ask me why I appreciate your admonition so much.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Gail Wanabee (12/7/2016)
The server has 160 GB of physical RAM.
P.S. Thanks for the warning. Don't ask me why I appreciate your admonition so much.

If that's the case and it's Standard Edition, set it to 200000 MB and let SQL Server figure out how much 128GB is. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 8

Gail Wanabee

SSCrazy Eights

Points: 8471

December 7, 2016 at 6:11 pm

#1916658

Okay.

Thanks, Jeff.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 9

Question, will you be running other services on the server, i.e. SSIS,SSRS,SSAS etc.?

😎

With 160Gb memory on the server, I would suggest to set the SQL Server Max Memory to 128Gb /131072 Mb and monitor both the SQL Server and the OS for memory related pressure.

For fun, here is an easy way of converting between units

DECLARE @SIZE_IN_BYTES NUMERIC(38,6) = 137438953472.0;

SELECT

'BYTES' AS SCALE

,@SIZE_IN_BYTES AS SIZE

UNION ALL

SELECT

'KILOBYTES'

,@SIZE_IN_BYTES / POWER(2.0,10)

UNION ALL

SELECT

'MEGABYTES'

,@SIZE_IN_BYTES / POWER(2.0,20)

UNION ALL

SELECT

'GIGABYTES'

,@SIZE_IN_BYTES / POWER(2.0,30)

UNION ALL

SELECT

'TERABYTES'

,@SIZE_IN_BYTES / POWER(2.0,40)

Output

SCALE SIZE

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

BYTES 137438953472.000000

KILOBYTES 134217728.000000

MEGABYTES 131072.000000

GIGABYTES 128.000000

TERABYTES 0.125000

richlion2 SSCrazy Points: 2484 More actions · Answer 10

I totally agree with SSCertifiable.

People were constantly complaining about one of the servers I have in my place being utterly slow. When I studied it thoroughly and implemented some Perfmon checks it turned out the server event viewer was showing messages "process XXXX was terminated due to lack of RAM, please increase RAM, etc. etc.". It boils down to the fact that the Win OS must still have some space to breathe. The MS documentation says SQL-Server will be able to balance the use of RAM, but from what I've seen one needs to take that with a pinch of salt. I had to change the allocated memory from the default 2TB to 28GB out of 32GB in my system. I never experienced any bottlenecks since then. So does it make sence to go to such extreme lenghts to calculate how much you should allocate to our instance down to a single MB? Seems like a futile and pointless excersize.

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 11

If you really want to get specific, Jonathan Kehayias' article on how much memory SQL Server really needs is a good read:

[/url]

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

andrew_dale Ten Centuries Points: 1260 More actions · Answer 12

Eirikur Eiriksson (12/7/2016)
Question, will you be running other services on the server, i.e. SSIS,SSRS,SSAS etc.?
😎
With 160Gb memory on the server, I would suggest to set the SQL Server Max Memory to 128Gb /131072 Mb and monitor both the SQL Server and the OS for memory related pressure.
For fun, here is an easy way of converting between units
DECLARE @SIZE_IN_BYTES NUMERIC(38,6) = 137438953472.0;
SELECT
'BYTES' AS SCALE
,@SIZE_IN_BYTES AS SIZE
UNION ALL
SELECT
'KILOBYTES'
,@SIZE_IN_BYTES / POWER(2.0,10)
UNION ALL
SELECT
'MEGABYTES'
,@SIZE_IN_BYTES / POWER(2.0,20)
UNION ALL
SELECT
'GIGABYTES'
,@SIZE_IN_BYTES / POWER(2.0,30)
UNION ALL
SELECT
'TERABYTES'
,@SIZE_IN_BYTES / POWER(2.0,40)
Output
SCALE SIZE
--------- --------------------
BYTES 137438953472.000000
KILOBYTES 134217728.000000
MEGABYTES 131072.000000
GIGABYTES 128.000000
TERABYTES 0.125000

strictly speaking this is wrong but is common usage

kilo/mega/giga are all powers of 10 ie. 1,000/1,000,000/1,000,000,000

kibi/mebi/gibi are the powers of 2 i.e. 1,024/8,388,608/8,589,934,592

these things fall within the international agreements, see for instance

http://physics.nist.gov/cuu/Units/binary.html

https://en.wikipedia.org/wiki/Orders_of_magnitude_(data)

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 13

"Question, will you be running other services on the server, i.e. SSIS,SSRS,SSAS etc.?"

SSIS is running but rarely used.

Eric M Russell SSC Guru Points: 125522 More actions · Answer 14

I try not to be too stingy when it comes to reserving memory for Windows OS. Even if there are no other major services like SSIS running in the background, keep in mind that RDP sessions use memory, and if you run SSMS within RDP then your session could potentially consume 2 GB or more.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho