Setting Memory Limit for a SQL Server Instance

Kailash Chandra Behera | Wednesday, June 03, 2020

Introduction

This blog discusses about the memory problem exist with SQL Server and how to set limit of memory to SQL Server.

Getting Started

Sometimes we found that the SQL Server Instance memory usage is perceived as too high. Commonly, most of the memory is used by SQL Server.

Moreover, when you increase memory on the machine, but the database load remains the same, it is possible that even after the memory upgrades SQL Server will be the top consumer of memory. The reason is that by default SQL Server dynamically allocates memory during its activity and does not release it until there is a request from Windows.

This happens because, by default after a typical installation, SQL Server is not set an upper limit on the amount of memory it can use. This is one of the common mistakes done on the SQL Server Database Server. For example, if you have 16 GB RAM server and set up SQL Server, by default settings SQL Server can use the entire 16 GB.

This is leading to situations where SQL Server will consume all available memory on the computer hosting SQL Server. You may wish to specify a maximum memory limit. Before going to discuss how we will set the memory limit to SQL Server, let's discuss how to get information about the memory consumption by SQL Server.

Checking Memory Occupied By SQL Server


The sys.dm_os_process_memory table the memory consumption details by SQL Server, where we can get the amount of memory consumed by SQL Server. see the below code for more details.

 SELECT  
      (physical_memory_in_use_kb/1024) AS [Memory Consumed in MB],  
      (total_virtual_address_space_kb/1048) AS [Total Virctual Space Allocated in MB],  
      (virtual_address_space_reserved_kb/1048) AS [Total Virctual Space Resorved in MB],  
      (virtual_address_space_available_kb/1048) AS [Total Virctual Space Available in MB],  
      (physical_memory_in_use_kb/1024) AS [Memory Available in MB]  
 FROM sys.dm_os_process_memory

There are two ways to set the limit for SQL Server, using Setting of SQL Server Management Studio and by executing SQL Queries(T-SQL).

Set Memory Limit Using SQL Server Management Studio


On SQL Server Management Studio, using the property of SQL Server Instance we can change the memory limit. The SSMS accept in two way (In MB and KB), we can specify memory limit of SQL Server either in MB or KB. The below steps describes how to specify the memory limit to the SQL Server Instance.
  1. Open SQL Server Management Studio and logging into it, using your credential if you have SQL Authentication.
  2. Right click on the Instance of SQL Server, the property and the window will appear.

    sql server memory usage

    Setting SQL Instance Memory Limit using SSMS


  3. By default the General page is selected and you will find find general page information in right pan.
  4. Click on Memory page in Left pan to select it and you will find memory information of the SQL Instance in right pan, see the below image.

    sql server memory limits by version

    Setting SQL Instance Memory Limit using SSMS


  5. As mentioned above, you can specify memory limit in MB or in KB then click on OK button.
Execute the Query from Checking Memory Occupied By SQL Server section to get the memory information you have set right now.

Set Memory Limit Using SQL Server T-SQL Query


The SQL Server system stored procedure named 'sp_configure' helps to set memory limit of SQL Server Instance. By default, the memory setting may not be available for viewing and changing. With help of the stored procedure, we can view and change the memory setting. To modify this behavior, use the following qeuery and execute it.
 USE master  
 EXEC sp_configure ''show advanced options'', 1  
 RECONFIGURE WITH OVERRIDE  
 GO
Above query will enable to view the advance option of SQL Server Instance and when you can change the memory setting. The following below T-SQL Query helps to change setting of memory.
 USE master  
 EXEC sp_configure 'max server memory', 2147483647   
 RECONFIGURE  
 GO
The above T-SQL Query sets "max server memory" to its default value - 2147483647 MB. It can be checked using SQL Server Management Studio using the steps explained in section 'Set Memory Limit Using SQL Server Management Studio' or by using T-SQL query of section 'Checking Memory Occupied By SQL Server'.*

Here we had displayed the advance setting options of SQL Server Instance, if you want you can hide it or leave it as it is. It is optional, to hide the advance option use the below T-SQL query.
 USE master  
 EXEC sp_configure ''show advanced options'', 0  
 RECONFIGURE WITH OVERRIDE  
 GO

Related Articles

  1. Enable SQL Remote Connection
  2. Encrypt data across communication channels
  3. Recovery Database From Warm-Standy Mode
  4. Setting Memory Limit for a SQL Server Instance
  5. Encrypt data using Transparent Data Encryption (TDE) in SQL

Thanks

By at