0

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?

  • you can limit the maximum memory SQL Server can handle. – Edgar Allan Bayron Jan 11 '18 at 7:19
  • Limit the SQL server maximum memory . Right click on server name -->properties -->Memory then change Max memory . by default max memory is 2147483647 MB . – Parassharma1990 Jan 11 '18 at 7:26
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.