what to do when you accidentally decrease sql memory

Past:   |   Updated: 2016-03-07   |   Comments (4)   |   Related: More > SQL Server Configurations

Problem

There is a frequently asked question among many people who work with SQL Server: "Why does SQL Server utilise about all of the memory on the server, even if there is essentially no activity on the databases?" This is a common and logical question if you are not familiar with SQL Server memory management and yous have not investigated the question. This tip is intended to explicate the default SQL Server retentivity configurations and how to configure SQL Server to use a fixed corporeality of RAM.

Solution

When a SQL Server instance is running on your machine, you lot may observe that memory usage is perceived as too high. Commonly, most of the retentiveness is used by SQL Server. Moreover, when you increase memory on the machine, but the database load remains the same, information technology is possible that even later on the retention upgrade SQL Server will be the top consumer of retention. The reason is that by default SQL Server dynamically allocates memory during its activeness and does not release information technology until there is a request from Windows.

It is normal for SQL Server, considering it tries to go along equally much memory as possible for performance purposes. Information technology reserves retentiveness and caches data into retentiveness to subtract access to deejay drives and therefore increasing functioning. When other processes on the same server require retentiveness, SQL Server releases the needed retention. Then, SQL Server tin use almost all bachelor memory on the server.

If your server is a dedicated database server in that location is no problem regarding memory resource allotment, withal sometimes there are many applications running on the database server. Moreover, some applications utilize memory bachelor at their starting fourth dimension and may be unable to request additional memory from Windows, if needed. In this case, to permit the other applications to work properly, nosotros tin can limit SQL Server's access to memory by setting the "max server memory" option. By default it is set to 2147483647 MB, which allows SQL Server to use approximately all of the server'due south memory. We can modify this setting to a lower value, according to our requirements. It could be done either past SQL Server Management Studio or a T-SQL script.

Setting "max server memory" using SQL Server Management Studio

At a very high level, let's monitor memory usage on our machine where SQL Server is running, and so set "max server retentivity" in SQL Server Management Studio (SSMS) and view the changes in retention usage. In our example, the test server has 1 GB of Memory (RAM) and SQL Server 2014 is installed, but non in a running state. Also, at that place are no other resources consuming memory on our server. By running Task Managing director we tin can monitor memory usage in the Functioning tab to become a sense of the retentivity usage prior to starting SQL Server:

Now we will beginning SQL Server and monitor memory usage again:

We tin can see that the corporeality of used retention has increased:

And the pinnacle memory "consumer" is SQL Server:

In SSMS we can run the following query and detect the memory used past SQL Server in megabytes:

SELECT (physical_memory_in_use_kb/1024) AS Used_Memory_By_SqlServer_MB FROM sys.dm_os_process_memory          

In our server information technology is 117 MB:

Now let'southward do some activity in our instance. We have TestDB database on our case and TableA in it, which contains 300,000 rows. Allow's select all rows from this table:

SELECT * FROM TestDB.dbo.TableA          

When the query finishes nosotros will check retention usage once more in SQL Server:

SELECT (physical_memory_in_use_kb/1024) Equally Used_Memory_By_SqlServer_MB FROM sys.dm_os_process_memory          

In this case nosotros tin can see that 432 MB is used by SQL Server, yet there are no active queries on our example:

And 93% of our server memory is used (mostly by SQL Server):

Ready "max server memory" in SQL Server Management Studio

Now we will set the "max server memory" option to limit the retention usage by SQL Server. We tin do this by right-clicking on our instance and choosing "Properties":

Subsequently than choose the "Memory" page:

Nosotros tin see higher up that "Maximum server retention" is set to 2147483647 MB. This is the default value. We will change information technology to 200 MB and click "OK":

Past running this query over again we can see that retentiveness used by SQL Server decreased to 244 MB:

SELECT (physical_memory_in_use_kb/1024) AS Used_Memory_By_SqlServer_MBFROM sys.dm_os_process_memory          

A question arises: why 244 MB, when nosotros set up "maximum server memory" for SQL Server to 200 MB? The reason is that our query returns currently used memory, all the same max server retentivity controls memory used by the buffer pool, compiled retention, all cache and then on, but it does not command memory for linked server providers other than SQL Server, retentiveness allocated by a non SQL Server DLL, memory for thread stacks, memory heaps, etc. and therefore the upshot of our query can be a bit more than than the value of "max server memory".

Set "max server memory" in SQL Server using T-SQL

We tin ready "max server memory" also by using a T-SQL script:

DECLARE @maxMem INT = 2147483647 --Max. memory for SQL Server instance in MB EXEC sp_configure 'bear witness avant-garde options', 1 RECONFIGURE  EXEC sp_configure 'max server memory', @maxMem RECONFIGURE          

This script sets "max server memory" to its default value - 2147483647 MB:

We can bank check in SSMS that "max server retention" has changed:

Recommendations on setting "max server memory"

Microsoft recommends to allow SQL Server to use memory dynamically, withal in some situations it is preferable to limit the memory usage past SQL Server. For example, when our server is not a dedicated database server and at that place are other applications running on the same server which are unable to request memory from Windows, it would be better if we approximate the maximum memory which tin can be used by SQL Server and gear up "max server retention" appropriately.

Too, when there are more than than i SQL Server instances running on your server, we can prepare "max server memory" for each of them, because their load, to control memory usage amidst these instances. Note that nosotros should consider that the sum of "max server retention" values for all instances will be less than the total concrete memory in our server. This ensures that complimentary memory will be available immediately for instances after offset up. Notwithstanding if 1 of instances is not running, the running instances volition be unable to use the remaining free retention.

Conclusion

As we can meet, SQL Server tends to use all bachelor retentivity on a server. However in some cases, when we demand to limit memory used by SQL Server or properly distribute retentivity amidst SQL Server instances we tin can set the "max server memory" option for each instance. One nearly important thing which we should consider before managing memory for SQL Server is to correctly estimate the maximum amount of memory which volition be needed for each case.

Next Steps

Read related information:

• Server Memory Server Configuration Options
• Retentiveness Architecture
• Optimizing Server Performance Using Retentiveness Configuration Options

Related Articles

Pop Articles

About the writer

Sergey Gigoyan is a database professional person with more than 10 years of experience, with a focus on database blueprint, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips

Article Final Updated: 2016-03-07

trotterothy1981.blogspot.com

Source: https://www.mssqltips.com/sqlservertip/4182/setting-a-fixed-amount-of-memory-for-sql-server/