MS-SQL 2016 Eating Memory

Question

MS-SQL 2016 Eating Memory

Viewing 7 posts - 16 through 22 (of 22 total)

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

Thom A SSC Guru Points: 99143 More actions · Answer 1

dextrous - Wednesday, October 18, 2017 4:38 AM
Sorry - and to add - I do have full access to all platforms and systems - so yes, I can access SSMS and run the querry - just very concious I'm not an expert in this area and can not risk screwing up a live environment. 🙁

No worries. The query I've posted above is only a SELECT statement, and returns values from the system configuration table. That table can be accessed by any login on the SQL Server, whether than only have the public role or a sysadmin. So, you could create a Login with no additional permissions, and not even create a user for them on a database; they would still be able to run that query.

It returns a data set that looks like the following:
name value value_in_use description min server memory (MB) 0 16 Minimum size of server memory (MB) max server memory (MB) 2147483647 2147483647 Maximum size of server memory (MB)
So, for my Server you can see the minimum MB for memory is 0. This is then in use at 16MB, as that's the default minimum if no value.
On the other hand, my max server memory is a massive 2147483647 MB! That's not actually what I'm using though, as we're only on Standard Edition here; Thus the data engine is limited to 128MB. We have 256MB on the server, so not an issue.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99143 More actions · Answer 2

dextrous - Wednesday, October 18, 2017 4:51 AM
Definately locked in at 8gb
Here's the SCR from the query

That's not 8GB... That's 80,000 MB, or 78.125GB. 8GB is 1024 MB * 8 = 8192 MB.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 3

dextrous - Wednesday, October 18, 2017 4:51 AM
Definately locked in at 8gb
Here's the SCR from the query

80000 mb is 80gb not 8 gb. This is the reason why sql server is grabbing the memory.

Thanks

Thom A SSC Guru Points: 99143 More actions · Answer 4

NorthernSoul - Wednesday, October 18, 2017 4:56 AM
80000 mb is ~~80gb~~ not 8 gb. This is the reason why sql server is grabbing the memory.
Thanks

There's 1024Mb to a GB! 80,000MB is about 78GB! :hehe:

Sorry, but it REALLY annoys me when people state 1,000MB = 1GB, or 1,000KB = 1MB etc. >_<

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

dextrous SSC Enthusiast Points: 145 More actions · Answer 5

I just noticed that and I've changed it...

Thank guys for identifying the bloody obvious 😀

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 6

dextrous - Wednesday, October 18, 2017 5:02 AM
I just noticed that and I've changed it...
Thank guys for identifying the bloody obvious 😀

If you really want to avoid having obvious problems, 8GB for SQL Server is terribly low. Memory is fairly cheap. Buff up the machine and either buy or permanently allocate more memory to that poor machine. It'll save you a ton of problems in the immediate and foreseeable future.

--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)

jasona.work SSC Guru Points: 50217 More actions · Answer 7

Jeff Moden - Wednesday, October 18, 2017 8:03 AM
If you really want to avoid having obvious problems, 8GB for SQL Server is terribly low. Memory is fairly cheap. Buff up the machine and either buy or permanently allocate more memory to that poor machine. It'll save you a ton of problems in the immediate and foreseeable future.

Seconding what Jeff has said, 8GB is quite trim for a SQL instance. Either give it more (12-16GB wouldn't be bad out of your 24GB) or if SAGE is that much of a hog get the powers-that-be to spring for a RAM upgrade for the server or move SQL or SAGE off onto it's own dedicated box.