SQL SERVER – Optimal Memory Settings for SQL Server – Notes from the Field #006

[Notes from Pinal]: Quite often we hear that my SQL Server is taking more memory or my server needs more memory as all the applications are running slow. The reality is that no matter how much memory you have it is never going to be enough. We all want more memory in our server and we all want our server to run faster, however the issue is that we all usually miss out to set the optimal memory settings.

SQL SERVER - Optimal Memory Settings for SQL Server - Notes from the Field #006 timradney Linchpin People are database coaches and wellness experts for a data driven world. In this very sixth episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains a very common issue DBA and Developer faces when they are setting up a new server. Do not forget the most important settings for any system – Memory!


When I get brought in to evaluate a SQL Server installation or get asked to look into performance issues on a SQL Server, one of the first items I look at is memory consumption and how memory is configured on the instance.

Many times I find that the default memory values have not been set and that the minimum memory setting is set to 0 and maximum memory setting is set to 2PB. This can be a major source of performance issues if max memory hasn’t been configured and SQL Server is starving the operating system. Just the same by not having the minimum memory setting set on a server that is shared with an application server or has multiple instances of SQL Server install, SQL Server could be starved of needed memory.

You can view your Minimum server memory and Maximum server memory in Server Properties or by querying sys.configurations .

SELECT *
FROM sys.configurations
WHERE configuration_id IN ( '1543', '1544' )

If you are using SSMS, you can see that in server properties menu.

SQL SERVER - Optimal Memory Settings for SQL Server - Notes from the Field #006 memorysettings

Setting the minimum memory setting for SQL usually isn’t the hard number to come up with. I usually pick a value that is fairly low just to make sure SQL would not get starved to the point of becoming unstable. I must say that I have rarely encountered an issue where the minimum value not being set has caused a problem; my experience has been with SQL Server starving the OS. I have still made it a best practice to set the minimum value just as a precaution.

Where things get a little more complicated is with the max memory value. I have a predetermined value I use when the server is dedicated for SQL that I use as a starting point. For example, on a server with 16 GB of ram I would set my maximum memory value to 12 GB leaving 4 GB for the OS. I would set my minimum value to 4 GB and then monitor the available mbytes memory counter to see how much memory is left for the operating system.

I am a little more conservative than some of my peers and I typically leave a little more for the OS. Some articles will recommend values in the 150-300 mb available; I usually like to leave around 1 GB, so if I see that 2 GB is still available for the OS, I will increase the maximum memory value to allow SQL to consume more. With SQL Servers today having 16, 32, 64, 128 GB as standard builds, I think I can afford to give the OS more than the 150-300 recommended.

I recently blogged about how to get the mbytes memory counter from within SQL Server without having to use Perfmon Counters, you can read more about that here Get OS Counter Memory Available Using T-SQL.

If you want me to take a look at your server and its settings or if your server is facing any issue we can Fix Your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL Reporting – How to Add Excel and Word Reporting to Your Own Application – An Innovative Approach
Next Post
MySQL – How to Create Stored Procedure

Related Posts

5 Comments. Leave new

  • Dear Pinal Dave, I am reading ur message and getting more knowledge. We are using sql with one user login for more than 20 users. If we make more users and login with different user will take less time in report process ?

      Thank you Gavesh Talati     Pan :AATPT8076G Interface Software Inc, Service Tax:AATPT8076G ST001 Wizard Infocom   VAT 24060304614 1/2/2012 Bajaj Allianz life ,Tally account Partner 9825082967

    Reply
  • Hello Pinalbhai,

    Right now I m working on WPF application which is fetching real time patient data and storing into SQL server. Our PC is having is having 2 GB RAM. but SQL server memory is going to be increase up to 1 GB sometimes in Task Manager. And hence some time Low Memory error comes and application is crashed. So I have changed “max server memory” to 256 MB. Is it sufficient or I need to change? Because of this much memory , is application get affected and run slow by this???

    Please guide me on this.

    Thanks,
    Ashish

    Reply
  • Sandeep Tripathi
    March 25, 2015 3:51 pm

    Hello sir,
    I have dedicated server with 8 Gb RAM.I have developed the CRM when multiple user working at a time Sql server working very slow task is going on suspended.
    Sql server 2008 R2 Express Edition
    Database Size=6Gb
    Maximum Memory=2147483647 Mb
    Minimum Memory Per query=1024 kb

    Reply
  • Hi Pinal,
    I have a windows 2008 R2 server. We are getting E-mails reporting that one of our programs which uses SQL database is crawling. Can you help us with the memory configuration. The VM itself has 12GB of RAM

    Reply

Leave a Reply

Menu