Professional Association for SQL Server

Article-Object Qualification is Important

Configuration of SQL Server part 1

This is the first time I have tried to write an article, so please excuse the contents.

My first article is about ‘How to Set up Memory for SQL Server’ When You Are Configuring SQL Server.

Introduction

Why do we have to specifically setup the memory for SQL Server?

What happens if we don’t?

The memory will be taken over completely by SQL Server. This means that SQL Server will take all the available memory for it self, specifically a 64bit OS. Based on the OS SQL Server will automatically assign the memory.

If the SQL Server takes all the memory, the OS will be starved for memory; this will cause High Page File usage. Therefore we have to dedicate a certain amount of memory for the OS.

The basic rule of thumb that we have to keep in mind is, that depending of the amount of memory we will assign 10 -15% for the OS or at least 1 GB.

So how can we do this?

We can do this either by using the GUI or a set of SQL commands. In this article we will look at how to configure using T-SQL.

Configuration

First command will be sp_configure.

Why do we run that command?

sp_configure will display or change the global configuration settings for the current server, that you are configuring. To get more details about sp_configure please check this link provided.

If we run this we still can’t see where we can setup the memory. The reason behind it is the fact that is does not show all the advanced options.

In order to see the advanced option, we have to run the following command.

sp_configure’show advanced options’, 1

go

What does the value 1 do?

With 1 (bit) it specifies to the SQL Server to show the advanced options.

After running that commands we will see the following in the message.

Figure 1

In figure 1 we can see that it asks you to run the RECONFIGURE statement.

Reconfigure

Go

Reconfigure statement updates the current configured values.

Now let us run the sp_configure command again. The output will be different from the previous execution of the command.

We can see part of the results in Figure 2.

The result will be like this:

 

Figure 2

As you can see from the Figure 2, it list out all the options and their default values. The option that we will need is highlighted, it is max server memory(MB).

What do all these columns in the output list mean? Here is a small explanation of all the columns

    name - The name of the option that can be changed

minimum - The minimum value setting that is allowed

maximum -  The maximum value that is allowed

config_value - What is the value currently configured?

run_value - What value is currently being used?

The next logical step would be to find out the total available memory. How do we find the total memory available on the server using T-SQL?

We can run the following SQL statement based on the version of SQL installed.

SQL 2005/2008

USE [master];

SELECT (physical_memory_in_bytes/1024/1024) AS TotalMb FROM  sys.dm_os_sys_info


SQL 2012

USE [master];

SELECT (physical_memory_kb/1024) AS TotalMb FROM  sys.dm_os_sys_info

 

The output will give us the total memory of the server in MB.

In order to understand the different scenarios when setting up memory we will look at two extremes.

In the first scenario we will assume that the total memory available is just 4GB.

How should we assign the memory?

In this particular case the OS should get 1GB. The OS needs at least one 1 GB to do its operation. Since we have to assign memory in MB, we will convert the GB to MB.

1 GB = 1024 MB. Therefore 4 GB will be 4096 MB.

Therefore the available memory for SQL Server will be 3072 MB. The calculation is as shown below.

4096-1024=3072

Now let us assign this 3072 to the SQL Server. We can do this by using the commands below.

sp_configure 'max server memory (MB)',3072

GO

This sets the maximum amount of memory SQL Server can use to 3072. For this setting to take effect we have to run the below shown command.

Reconfigure

Go

To check if the configuration change that we did take effect we can run the below command.

sp_configure

GO


Figure 3

The above Figure 3 will show that the config_value and run_value has change to the value that we set.

Now let us consider the second scenario where the total memory available is 50 GB.

In this case how much memory should we give the OS?

The OS should get at least 10% of the total memory available.

The basic rule of thumb is 10-15% and in this case we are using 10%.

10% of the total memory is 5GB. Since the total memory available is 50 GB we will assign 45 GB to the SQL Server. That is 45*1024. This comes to 46080.

Now let us assign this 46080 to the SQL Server. We can do this by using the commands below.

sp_configure 'max server memory (MB)', 46080

GO

This sets the maximum amount of memory SQL Server can use to 46080. For this setting to take effect we have to run the below shown command.

Reconfigure

Go

To check if the configuration change that we did take effect we can run the below command.

sp_configure

GO

We have completed the memory set up. Our next step would be to hide advanced options. For that we run below command.

sp_configure'show advanced options',0

GO

Reconfigure

Go

With this command we will change the configuration option’ show advanced options’ from 1 to 0.This sets the value to false.

Now we won’t see the advanced options anymore when we run sp_configure.

Conclusion

I hope that by reading this article I was able to explain the basics of memory configuration on SQL Server on versions 2005 and above.


Here is the full set of commands to setup the memory:

--To set Memory

sp_configure'show advanced options',1

go

Reconfigure

go

sp_configure 'max server memory (MB)',3072

go

Reconfigure

go

sp_configure

go

sp_configure'show advanced options',0

go

Reconfigure

go

 

By

Surenda Djaoen

 

 

 

The Professional Association for SQL Server (PASS) is an independent, not-for-profit association, dedicated to supporting, educating, and promoting the Microsoft SQL Server community. From local user groups and special interest groups (SIGs) to webcasts and the annual PASS Community Summit � the largest gathering of SQL Server professionals in the world � PASS enables knowledge sharing, in-depth learning, access to the Microsoft SQL Server team, and the ability to influence the direction of SQL Server technologies. PASS was founded in 1999 by Microsoft and CA and continues to enjoy executive level endorsement from both organizations. As the number one user community for Microsoft SQL Server, Microsoft looks to the PASS community for valued feedback, input, and inspiration. PASS Mission Empower the global Microsoft SQL Server community to connect, share, learn and be inspired through networking, knowledge sharing, peer-based learning, and the ability to influence the direction of Microsoft SQL Server products and services.

Sponsors

PASSChapterLogo100.jpg 

 
 


 

 
     
cage-aids
cage-aids
cage-aids
cage-aids