Click here to Skip to main content
15,867,895 members
Articles / Enterprise

How to Configure Memory in SQL SERVER? TIP#83

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
26 Feb 2015CPOL1 min read 5.9K  
How to configure memory in SQL Server? Tip #83

One of the good sentences I remember is “When someone has teeth, he/she is not having nuts and when someone has nuts, he/she is not having teeth”. Jokes apart. You understand what I mean to say here. If you have the resources, then utilize it.

One of the most important aspects in performance is memory.

The main point here is if we have high configured machines which have more than 16 GB RAM but the pain point is our SQL Server is not using available Memory.

Now to configure memory for SQL SERVER is super easy but at the same time you need to understand how much memory you can assign to SQL SERVER to use because you need some buffer memory to your operating system and other.

Now just follow the below steps to configure the memory for SQL SERVER.

Step 1

Right click the server and open SQL SERVER Properties.

Step 2

Now select the memory tab and you will find the below screen:

Memory 

Step 3

You can change the above boxed max memory value according to your available memory calculation.

OR you can run the following command as well to set the Maximum memory that SQL SERVER can utilize.

CommandMemoryset

SQL
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘max server memory’, 4096; — 4GB
GO
RECONFIGURE;
GO

One more important point that I would like to share is if your machine is X86 machine in such case you have to use /3GB switch with boot file first.

You can find the instruction to set /3GB switch in the following link:

https://technet.microsoft.com/en-us/library/bb124810%28v=exchg.65%29.aspx

I hope this post helps you.

Enjoy!!!

Filed under: CodeProject, DENALI, Performance, Sql server, SQL SERVER 2008 - R2, SQL SERVER 2014, TIPS Tagged: /3GB Switch, Max SQL SERVER memory, Performance tips

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Team Leader
India India
I am Rajat Jaiswal from India. I am working as a Technology specialist in one of the reputed company in India with 12+ years of experience. I am a Microsoft Data Platform MVP & Alibaba Cloud MVP.
I have always had an affinity to learn something new in technology. Database, Web development, Cloud computing are the areas of my interests always.
I have been a regular contributor to technologies with more than 300+ tips. Learning & Sharing is one of my aims always. Writing blogs, delivering sessions, helping on forums is my hobby.

Comments and Discussions

 
-- There are no messages in this forum --