Applies to: SQL Server (all supported variations)
Reconfigure the quantity of reminiscence (in megabytes) for a SQL Server course of utilized by an occasion of SQL Server. There are two server reminiscence choices, min server reminiscence and max server reminiscence. These choices change the quantity of reminiscence the SQL Server Reminiscence Supervisor can allocate to a SQL Server course of.
The default settings and minimal allowable values for these choices are:
|min server reminiscence|
|max server reminiscence||2,147,483,647 megabytes (MB)||128 MB|
By default, SQL Server can change its reminiscence necessities dynamically based mostly on obtainable system assets. For extra info, see dynamic reminiscence administration.
Setting max server reminiscence worth too excessive may cause a single occasion of SQL Server to compete for reminiscence with different SQL Server cases hosted on the identical host. Nonetheless, setting this worth too low might trigger important reminiscence stress and efficiency issues.
Setting max server reminiscence to the minimal worth may even stop SQL Server from beginning. Should you can not begin SQL Server after altering this selection, begin it utilizing the -f startup possibility and reset max server reminiscence to its earlier worth. For extra info, see Database Engine Service Startup Choices.
SQL Server can use reminiscence dynamically; nonetheless, you’ll be able to set the reminiscence choices manually and prohibit the quantity of reminiscence that SQL Server can entry. Earlier than you set the quantity of reminiscence for SQL Server, decide the suitable reminiscence setting by subtracting, from the entire bodily reminiscence, the reminiscence required for the OS, reminiscence allocations not managed by the max_server_memory setting, and every other cases of SQL Server (and different system makes use of, if the pc just isn’t wholly devoted to SQL Server). This distinction is the utmost quantity of reminiscence you’ll be able to assign to the present SQL Server occasion.
Set choices manually
The server choices min server reminiscence and max server reminiscence might be set to span a variety of reminiscence values. This technique is beneficial for system or database directors to configure an occasion of SQL Server at the side of the reminiscence necessities of different functions, or different cases of SQL Server that run on the identical host.
The min server reminiscence and max server reminiscence choices are superior choices. If you’re utilizing the sp_configure system saved process to vary these settings, you’ll be able to change them solely when present superior choices is about to 1. These settings take impact instantly and not using a server restart.
Use min_server_memory to ensure a minimal quantity of reminiscence obtainable to the SQL Server Reminiscence Supervisor for an occasion of SQL Server. SQL Server won’t instantly allocate the quantity of reminiscence laid out in min server reminiscence on startup. Nonetheless, after reminiscence utilization has reached this worth because of consumer load, SQL Server can not free reminiscence except the worth of min server reminiscence is lowered. For instance, when a number of cases of SQL Server can exist concurrently in the identical host, set the min_server_memory parameter as a substitute of max_server_memory for the aim of reserving reminiscence for an occasion. Additionally, setting a min_server_memory worth is important in a virtualized surroundings to make sure reminiscence stress from the underlying host doesn’t try and deallocate reminiscence from the buffer pool on a visitor SQL Server digital machine (VM) past what is required for acceptable efficiency.
SQL Server just isn’t assured to allocate the quantity of reminiscence laid out in min server reminiscence. If the load on the server by no means requires allocating the quantity of reminiscence laid out in min server reminiscence, SQL Server will run with much less reminiscence.
Use max_server_memory to ensure the OS doesn’t expertise detrimental reminiscence stress. To set max server reminiscence configuration, monitor total consumption of the SQL Server course of with a purpose to decide reminiscence necessities. To be extra correct with these calculations for a single occasion:
- From the entire OS reminiscence, reserve 1GB-4GB to the OS itself.
- Then subtract the equal of potential SQL Server reminiscence allocations outdoors the max server reminiscence management, which is comprised of stack measurement 1 * calculated max employee threads 2. What stays needs to be the max_server_memory setting for a single occasion setup.
1 Check with the Reminiscence Administration Structure information for info on thread stack sizes per structure.
2 Check with the documentation web page on easy methods to Configure the max employee threads Server Configuration Possibility, for info on the calculated default employee threads for a given variety of affinitized CPUs within the present host.
Use SQL Server Administration Studio
Use the 2 server reminiscence choices, min server reminiscence and max server reminiscence, to reconfigure the quantity of reminiscence (in megabytes) managed by the SQL Server Reminiscence Supervisor for an occasion of SQL Server. By default, SQL Server can change its reminiscence necessities dynamically based mostly on obtainable system assets.
Process for configuring a set quantity of reminiscence (not really useful)
To set a set quantity of reminiscence:
In Object Explorer, right-click a server and choose Properties.
Click on the Reminiscence node.
Beneath Server Reminiscence Choices, enter the quantity that you really want for Minimal server reminiscence and Most server reminiscence.
Use the default settings to permit SQL Server to vary its reminiscence necessities dynamically based mostly on obtainable system assets. It’s endorsed to set a max server reminiscence as detailed above.
The next screenshot demonstrates all three steps:
Lock Pages in Reminiscence (LPIM)
This Home windows coverage determines which accounts can use a course of to maintain information in bodily reminiscence, stopping the system from paging the information to digital reminiscence on disk. Locking pages in reminiscence might hold the server responsive when paging reminiscence to disk happens. The Lock Pages in Reminiscence possibility is about to ON in cases of SQL Server Customary version and better when the account with privileges to run sqlservr.exe has been granted the Home windows Lock Pages in Reminiscence (LPIM) consumer proper.
To disable the Lock Pages In Reminiscence possibility for SQL Server, take away the Lock Pages in Reminiscence consumer proper for the account with privileges to run sqlservr.exe (the SQL Server startup account) startup account.
Setting this selection doesn’t have an effect on SQL Server dynamic reminiscence administration, permitting it to increase or shrink on the request of different reminiscence clerks. When utilizing the Lock Pages in Reminiscence consumer proper it is suggested to set an higher restrict for max server reminiscence as detailed above.
Setting this selection ought to solely be used when crucial, specifically if there are indicators that sqlservr course of is being paged out.
On this case, error 17890 will likely be reported within the Errorlog, resembling the beneath instance:
A big a part of sql server course of reminiscence has been paged out. This may increasingly lead to a efficiency degradation. Length: #### seconds. Working set (KB): ####, dedicated (KB): ####, reminiscence utilization: ##%.
Beginning with SQL Server 2012 (11.x), hint flag 845 just isn’t wanted for Customary Version to make use of Locked Pages.
To allow Lock Pages in Reminiscence
To allow the lock pages in reminiscence possibility:
On the Begin menu, click on Run. Within the Open field, kind gpedit.msc.
The Group Coverage dialog field opens.
On the Group Coverage console, increase Laptop Configuration, after which increase Home windows Settings.
Increase Safety Settings, after which increase Native Insurance policies.
Choose the Person Rights Project folder.
The insurance policies will likely be displayed within the particulars pane.
Within the pane, double-click Lock pages in reminiscence.
Within the Native Safety Coverage Setting dialog field, add the account with privileges to run sqlservr.exe (the SQL Server startup account).
A number of cases of SQL Server
When you find yourself operating a number of cases of the Database Engine, there are three approaches you should use to handle reminiscence:
Use max server reminiscence to manage reminiscence utilization, as detailed above. Set up most settings for every occasion, being cautious that the entire allowance just isn’t greater than the entire bodily reminiscence in your machine. You may need to give every occasion reminiscence proportional to its anticipated workload or database measurement. This method has the benefit that when new processes or cases begin up, free reminiscence will likely be obtainable to them instantly. The disadvantage is that in case you are not operating all the cases, not one of the operating cases will be capable of make the most of the remaining free reminiscence.
Use min server reminiscence to manage reminiscence utilization, as detailed above. Set up minimal settings for every occasion, in order that the sum of those minimums is 1-2 GB lower than the entire bodily reminiscence in your machine. Once more, you could set up these minimums proportionately to the anticipated load of that occasion. This method has the benefit that if not all cases are operating on the similar time, those which might be operating can use the remaining free reminiscence. This method can be helpful when there’s one other memory-intensive course of on the pc, since it might insure that SQL Server would not less than get an inexpensive quantity of reminiscence. The disadvantage is that when a brand new occasion (or every other course of) begins, it might take a while for the operating cases to launch reminiscence, particularly if they have to write modified pages again to their databases to take action.
Do nothing (not really useful). The primary cases introduced with a workload will are likely to allocate all of reminiscence. Idle cases, or cases began later, might find yourself operating with solely a minimal quantity of reminiscence obtainable. SQL Server makes no try and stability reminiscence utilization throughout cases. All cases will, nonetheless, reply to Home windows Reminiscence Notification indicators to regulate the scale of their reminiscence footprint. Home windows doesn’t stability reminiscence throughout functions with the Reminiscence Notification API. It merely gives international suggestions as to the provision of reminiscence on the system.
You may change these settings with out restarting the cases, so you’ll be able to simply experiment to search out the very best settings in your utilization sample.
Present the utmost quantity of reminiscence
Reminiscence might be configured as much as the method digital tackle area restrict in all SQL Server editions. For extra info, see Reminiscence Limits for Home windows and Home windows Server Releases.
Instance A. Set the max server reminiscence choice to 4 GB.
The next instance units the
max server reminiscence choice to 4 GB. Observe that though
sp_configure specifies the identify of the choice as
max server reminiscence (MB), the instance demonstrates omitting the
sp_configure 'present superior choices', 1; GO RECONFIGURE; GO sp_configure 'max server reminiscence', 4096; GO RECONFIGURE; GO
This may output an announcement much like:
Configuration possibility ‘max server reminiscence (MB)’ modified from 2147483647 to 4096. Run the RECONFIGURE assertion to put in.
Instance B. Figuring out Present Reminiscence Allocation
The next question returns details about at the moment allotted reminiscence.
SELECT physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB, large_page_allocations_kb/1024 AS sql_large_page_allocations_MB, locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB, virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB, virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB, virtual_address_space_available_kb/1024 AS sql_VAS_available_MB, page_fault_count AS sql_page_fault_count, memory_utilization_percentage AS sql_memory_utilization_percentage, process_physical_memory_low AS sql_process_physical_memory_low, process_virtual_memory_low AS sql_process_virtual_memory_low FROM sys.dm_os_process_memory;
Instance C. Figuring out worth for ‘max server reminiscence (MB)’
The next question returns details about the at the moment configured worth and the worth in use by SQL Server. This question will return outcomes no matter whether or not ‘present superior choices’ is true.
SELECT c.worth, c.value_in_use FROM sys.configurations c WHERE c.[name] = 'max server reminiscence (MB)'
Reminiscence Administration Structure Information
Monitor and Tune for Efficiency
Server Configuration Choices (SQL Server)
Database Engine Service Startup Choices
Editions and supported options of SQL Server 2016
Editions and supported options of SQL Server 2017
Editions and supported options of SQL Server 2017 on Linux
Reminiscence Limits for Home windows and Home windows Server Releases