[sims] SQL memory use.

[Koldov]

Wasn't sure where to put this, but I figure a lot of school I.T. personnel would have a knowledge of SQL through SIMS...

 

I tried starting a VM today and got an 'out of memory' error! The (physical host) server has 32GB, which I know isn't a lot by todays standards but we're a small school.

 

It hosts 2 VMs, but also runs SIMS and file shares, we are also running Sophos and have Redstor backup software (not ideal I know, but it's our only server).

 

Anyway, from the screenshot below you can see most of the memory is used and it struck me (if I'm reading it right) that SQL has committed 7,672,948KB but is only using 342,188KB...?

 

So, my question is obviously how to make sure SQL doesn't need that 7GB and how to limit it to a more reasonable figure?

 

[willtech]

In SQL Server Management Studio, connect to the desired SQL Server database engine, right-click the desired instance and click Properties.

In the Server Properties dialog box, select the Memory item from the list on the left.

In the Maximum server memory (in MB) option, specify the desired maximum value.

Click OK to save your changes. Restart the SQL Server Service

Edited June 20, 2023 by willtech

[Koldov]

Awesome, many thanks!

 

Is there anyone who could shed any light on the Voodoo that is SQL and what sort of figure I should be looking to allocate? Does it go on the DB size, amount of users, or some other unknown mystical algorithm?

 

What DB should I be looking at (SIMS.mdf)? We use SIMS and FMS (also Discover - but not really in anger).

[3s-gtech]

You want to ideally fit the whole of the DB(s) into RAM (the MDFs), so allocate enough memory that SQL can do that. SQL will swell up to fill the allocation as it accesses more tables in use.

[Koldov]

Thanks, I've looked in Server Management Studio and it's currently set to 2147483647MB (!)...

 

Can anyone just sense check my calculations?

 

My MDFs are:

 

SIMS = 2,084,352KB ( ) 2000MB = 2.00GB

FMS = 353,472KB (so roughly) 353MB = 0.35GB

DISCOVER = 331,968KB ( ) 331MB = 0.33GB

 

There are other MDFs in there (templog, solus, MSDB, model, master which add up to about 200MB) so, am I going to get away with 4GB (4096MB)...?

Edited June 20, 2023 by Koldov

[3s-gtech]

Yes, but give it some slack too. Make sure the max allocated to SQL gives enough headroom for the OS - but don't run it all the way to 32GB as that's pointless.

[Koldov]

Make sure the max allocated to SQL gives enough headroom for the OS

 

Thanks, but I wish that didn't all sound like gibberish to me...

[3s-gtech]

Windows Server will want at least a couple of GB for itself, so if you had 8GB of RAM in the server, you may allocate 6GB to SQL and leave those 2GB free.

 

Get it right and not only does the OS run better, but you get less disk fragmentation as it doesn’t need to keep disk swapping to read from the HDD.

[localzuk]

So, SQL server is designed to grab as much memory as is available for itself - setting the limit as @willtech said is pretty much critical.

 

I always go with the size of the MDF file + a couple of GB for memory sizing. So, in your case, I'd be assigning 4-6GB based on your MDF file sizes. Though, with those small amounts, I'd realistically stick to 8GB for SQL, and probably give 4GB to the OS.

 

Your initial message is a little confusing though. Are you running SIMS in its own VM? Or is it running on the host, and then VMs on top of that?

[Koldov]

Windows Server will want at least a couple of GB for itself, so if you had 8GB of RAM in the server, you may allocate 6GB to SQL and leave those 2GB free.

 

Get it right and not only does the OS run better, but you get less disk fragmentation as it doesn’t need to keep disk swapping to read from the HDD.

 

Ah yeah, I got it now... I thought from the way I read your reply was that I needed to add more to the SQL allocation for the OS for some reason.... hence why I was confused, but re-reading now I can see that you meant don't allocate all the memory to SQL and leave some for the OS! I've got 32GB of RAM, I can't quite figure out how it's all been used up if I'm honest... but it's back down to acceptable levels now. Even restarting the SQL server service seems to have knocked it right down!

 

So, SQL server is designed to grab as much memory as is available for itself - setting the limit as @willtech said is pretty much critical.

 

I always go with the size of the MDF file + a couple of GB for memory sizing. So, in your case, I'd be assigning 4-6GB based on your MDF file sizes. Though, with those small amounts, I'd realistically stick to 8GB for SQL, and probably give 4GB to the OS.

 

Your initial message is a little confusing though. Are you running SIMS in its own VM? Or is it running on the host, and then VMs on top of that?

 

Thanks I've gone for 4GB for now and see if either SQL, the server or the staff complain... Yes, unfortunately same old small school story, one physical server running everything! I've so far managed to move its DC role onto a VM and have another VM setup and ready to take SIMS but haven't managed to do the migration as yet (long story). So, currently one physical server running everything (SIMS, file shares, etc.) and 2 x VMs.

[localzuk]

Yes, unfortunately same old small school story, one physical server running everything! I've so far managed to move its DC role onto a VM and have another VM setup and ready to take SIMS but haven't managed to do the migration as yet (long story). So, currently one physical server running everything (SIMS, file shares, etc.) and 2 x VMs.

Beware that running VMs on top of an OS that is itself running services is not supported or recommended by Microsoft, especially not SQL server. This can cause issues with memory - as you've seen already.

[Koldov]

Beware that running VMs on top of an OS that is itself running services is not supported or recommended by Microsoft, especially not SQL server. This can cause issues with memory - as you've seen already.