Configure SQL Server Memory To Optimize Performance

by Manzoor Siddiqui · Published September 9, 2015 · Updated September 24, 2015

694views

Database performance is always a headache for Database Administrators as over the time performance may degrade due to several reasons and DBAs has to identify and fix this issue. In previous articles we have seen different techniques and ways how we can identify and fix the performance issue, I am listing here few methods which we have covered earlier.

But there are always scope to identify more options for improvement and fix it. Here we will see how we can configure SQL Server memory to optimize performance and what values are preferred to set and to avoid.

To access Server memory properties option you can follow below steps.

Right click on database instance –> Go to Properties –> Click on Memory page

Now you can see two options under Server Memory as highlighted below.

Minimum server memory (in MB)
Maximum server memory (in MB)

Default value for minimum server memory is 0 MB whereas for maximum server memory it is 2,147,483,647 MB.

Also, there are other memory options as

Index creation memory (in KB)
Minimum memory per query (in KB)

SQL Server Memory Manager automatically manages all memory options and dynamically identifies and takes decision how much memory to allocate based on operating system and applications. For 32 bit operating system the minimum memory amounts allowable for maximum server memory is 64 MB and for 64 bit operating system it is 128 MB.

You can check the current allocated memory with below query.

SELECT *FROM sys.dm_os_process_memory

Minimum Server Memory

Minimum server memory option does not allow SQL Server to release memory below the configured value if that threshold hits. If you are manually setting this value then make sure that you set it to some feasible value and operating system does not request for memory too often from SQL Server. If you are running multiple instances then make sure that sum of minimum memory is approx. 2 GB less than your total physical memory on the server.

Maximum Server Memory

Maximum server memory option is used to control higher memory usage on the server. When SQL Server runs it can allocate upto maximum memory specified in this option thus it helps to minimize performance issues. If you are using multiple instance then make sure that you are setting values keeping in mind total physical memory of the server.

You can set the maximum server memory option with below query. We are setting to 4 GB (4096 MB) as shown below.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'max server memory', 4096
GO
RECONFIGURE
GO

Index Creation Memory

Index creation memory option manages the total memory required by sorting operations for index creation. Index is normally created on production system during off-peak time or over the weekend. So before index creation you can increase memory for this option and hence you can achieve higher performance for index creation. Default value for index creation memory is 0 KB which means it is managed by dynamically by database server. You can configure this option with below query.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'index create memory', 4096
GO
RECONFIGURE
GO

Minimum Memory Per Query

Minimum memory per query server configuration option controls the minimum amount of memory for execution of a query on the database server. You can increase this value when you want to execute memory intensive queries or multiple queries simultaneously. Default value for minimum memory per query is 1,024 KB and minimum to maximum range is between 512 KB to 2,147,483,647 KB.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]