Sql-server – System Memory (RAM) for Windows Server 2012 Standard with SQL Server 2012

sql-server-2012windows-server

We are using Windows server 2012 standard server with 2.4 GHz and 16 GB RAM. SQL Server 2012 is installed in it and also our primary application is installed on the server.

Using transaction replication to get the latest data b/w the servers.

The server performance hits badly sometimes when SQL Server holding the entire memory of the server.

Will the performance of the server improve when increasing the RAM to 32 GB?

Best Answer

All of this assumes you're running 64 bit Windows and 64 bit SQL Server...

SQL Server is greedy when it comes to memory usage, and that is by design. It keeps as much data as it can in RAM because it is faster than having to access disk to fulfill a query or other request.

As a result, you may want to set the minimum memory allocation for a SQL Server instance so it doesn't suffer from what I call memory starvation. If this happens, the server's drives will thrash because database usage will require disk accesses.

You'll also want to set the maximum memory allocation as already mentioned. In general, I give 10% to the operating system and the rest is for SQL Server (I have dedicated SQL servers each with 32GB RAM so it's fairly straightforward).

This article from Brent Ozar's website has more details: https://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/

I noticed you said sometimes the performance suffers. Do you have other applications collocated on this server? Are there SQL Agent tasks scheduled to run at certain times? Backups can take a lot of resources and other applications sharing resources could end up fighting over them with SQL Server. In that case, you may need to add more memory and carefully tune how it's used, or move the application to another server.

Related Solutions

Sql-server – SQL Server 2012 Standard Edition – multiple instances and memory utilization

Knowing that two instances are absolutely not related to each other, i guess that you can use two instances on one server with 64gig for each. At least to my understanding, OS is the one who assigns memory to apps, so it should be fine :)

Sql-server – SQL Server 2012 Standard using too much memory

I don't think DBCC MEMORYSTATUS (or the other queries) are being that revealing at the moment (don't know what the others think); so I am going to ask you to run just one more:

dbcc traceon(3654, -1)
go
waitfor delay '00:05:00'
go
select top 20 memory_object_address, source_file, sum(size_in_bytes) as total_bytes
from sys.dm_os_memory_allocations
group by memory_object_address, source_file
order by total_bytes desc
go
dbcc traceoff(3654, -1)
go

You can see I've built in a delay there to allow it time to collect some information. Depending on what that reveals, I think you are faced with a choice, continue to investigate or speculate:

continue to drill further into the memory leak using more advanced debugging techniques, eg using sysinternals tool Process Explorer; find process sqlservr.exe and look for evidence of memory leak, or:
use our instincts and what we know about your SQL Server and make and educated guess this is most likely to do with the MySQL linked server. Next time you restart your service, temporarily disable this bit of functionality, eg drop the linked server and/or disable reports which depend upon it. Monitor for your issue. If the issue does not occur, you have a good candidate for your culprit. I think there are newer drivers available (5.3.4?) or you could consider refactoring this part of the application to an SSIS job which creates a local cache once a night for example. If the problem does not occur you at least rule it out.

As this is a live server I think your debugging options are a bit limited so it might be time to try some options, see how the server responds.

Just in reference to using DBCC MEMORYSTATUS, I have had some success with this technique in the past, for example identifying run-away full-text queries or use of OPENXML without the compulsory sp_xml_removedocument.

Best Answer

Related Solutions

Sql-server – SQL Server 2012 Standard Edition – multiple instances and memory utilization

Sql-server – SQL Server 2012 Standard using too much memory

Related Question