AWS Database Blog

Best practices for configuring performance parameters for Amazon RDS for SQL Server

February 2024: This post was reviewed and updated for accuracy.

With Amazon Relational Database Service (Amazon RDS) for SQL Server, you can quickly launch a database instance in just a few clicks and start serving your application requirements. Although Amazon RDS for SQL Server doesn’t typically require configuration changes, you may want to customize certain parameters based on your workload. To learn about best practices and configuration for Amazon RDS Custom for SQL Server, refer to Best practices and parameter configuration for enhanced performance on Amazon RDS Custom for SQL server. This post discusses some parameters you can tune to enhance performance.

For example, you can change server-level parameters available under sp_configure using a custom parameter group. You customize database-level settings using SQL Server Management Studio (SSMS) GUI or T-SQL queries. In Amazon RDS, parameters can be either static or dynamic. A static parameter change needs an instance restart to take effect. Dynamic parameter changes take effect online without any restart and therefore can be changed on the fly.

In this post, we discuss the following configuration options to fine-tune performance:

  • Maximum server memory
  • Maximum degree of parallelism
  • Cost threshold for parallelism
  • Optimize for ad hoc workloads
  • Optimize transaction logs with virtual log files
  • Configure tempdb and enable autogrowth
  • Update statistics

We also discuss the steps to make these configuration changes in a custom parameter group.

Maximum server memory

SQL Server manages memory dynamically, freeing and adding memory as needed. Starting with SQL Server 2012 Single-Page or Multi-Page allocations, common language runtime (CLR) objects were all combined under Any Page Allocator and the maximum memory allocated to these was controlled by max server memory.

After SQL Server is started, it slowly takes the memory specified under min server memory and continues to grow until it reaches the value specified in max server memory. SQL Server memory is divided into two parts: buffer pool and non-buffer pool, or memory to leave (MTL). The value of max server memory determines the size of the SQL Server buffer pool. A buffer pool consists of various caches such as the buffer cache, procedure cache, and plan cache.

Starting with SQL Server 2012, max server memory accounts for all memory allocations for all caches (such as SQLGENERAL, SQLBUFFERPOOL, SQLQUERYCOMPILE, SQLQUERYPLAN, SQLQUERYEXEC, SQLOPTIMIZER, and SQLCLR). For a complete list of memory clerks under max server memory, see sys.dm_os_memory_clerks.

The objective behind a buffer pool is to minimize the disk I/O. You use a buffer pool as the cache, and max_server_memory controls its size. The target of the buffer pool is not to become so big that the entire system runs low on memory and to minimize disk I/O.

The non-buffer pool or MTL is comprised mainly of thread stacks, third-party drivers, and DLLs. SQL Server (on 64 bit) takes 2 MB of stack memory for each thread it creates. This thread stack memory is placed outside of max server memory or the buffer pool and is part of the non-buffer pool.

To find the total server memory, use the following query:

SELECT total_physical_memory_kb / 1024 AS MemoryMb

FROM sys.dm_os_sys_memory

The idea is to cap max server memory to a value that doesn’t cause system-wide memory pressure. However, there’s no universal formula that applies to all the environments. You can use the following guidelines as a starting point:

max_server_memory = total_RAM – (memory_for_the_OS + MTL)

This code sets the following parameters:

  • Memory for the operating system is 1–4 GB
  • MTL includes the stack size, which is 2 MB on 64-bit machines per worker thread

Alternatively, you can use the following code:

max_server_memory = total_RAM – (1 GB for the OS + memory_basis_amount_of_RAM_on_the_server)

In this example, the memory basis amount of RAM is determined as follows:

  • If RAM on the server is between 4 GB and 16 GB, leave 1 GB per 4 GB of RAM. For example, for a server with 16 GB, leave 4 GB.
  • If RAM on the server is over 16 GB, leave 1 GB per 4 GB of RAM up to 16 GB, and 1 GB per 8 GB of RAM above 16 GB.

For example, if a server has 256 GB of RAM, the calculation will be:

  • 1 GB for the OS
  • Up to 16 GB RAM: 16/4 = 4 GB
  • Remaining RAM above 16 GB: (256-16)/8 = 30
  • Total RAM to leave: 1 + 4 + 30 = 35 GB
  • max_server_memory: 256 – 35 = 221 GB

Note that because all the workloads have their own uniqueness, after the initial configuration, you should always monitor the memory for SQL Server and the memory available for the operating system to ensure that you have not left too little or too much memory outside the buffer pool. Too little will cause operating system to starve and cause system-wide issues; too much will cause underutilization of the available resources.

Additionally, note that some of the exceptions to the preceding method of calculation will be the t2/t3 kind of lower-sized instances, so you should be cautious when configuring max server memory on these instances.

After the initial configuration, monitor the freeable memory over a typical workload duration to determine if you need to increase or decrease the memory allocated to SQL Server.

When using SSIS, SSAS, or SSRS, you should also consider the memory usage by those components when configuring max server memory in SQL Server.

You can configure the value under a custom parameter group. To check the current value, use the following query:

# sp_configure 'max_server_memory'

To change the maximum server memory in Amazon RDS for SQL Server, you can use a custom parameter group. In the following screenshot, we change the maximum server memory to 100 GB.

For additional information, see the Memory management architecture guide and Server memory configuration options in the Microsoft SQL Server documentation.

Monitoring

When using the Amazon RDS Performance Insights dashboard, you can monitor the following:

  • physAvailKb – The amount of physical memory available in KB
  • sqlServerTotKb – The amount of memory committed to SQL Server in KB

For more information, see Performance Insights is Generally Available on Amazon RDS for SQL Server.

When to change the configuration

You should change the configuration based on monitoring in your environment. Select the metrics to monitor on the Performance Insights dashboard, under OS metrics.

Maximum degree of parallelism (MAXDOP)

In an online transaction processing (OLTP) environment, with high core, hyperthreaded machines being a norm these days, you should pay special attention to max degree of parallelism. Running with the default configuration can lead to severe parallelism-related wait times, severely impair performance, and—in extreme cases—it can bring the server down. A runaway query can lead to server-wide blocking due to parallelism-related wait times. A runaway query example here could be a query going for a parallel plan and spending a lot of time waiting on operations of parallel threads to complete. Such queries typically spend a long time waiting on CXPACKET.

A maximum degree of parallelism controls the number of processors used to run a single statement that has a parallel plan for running. The default value is set to 0, which allows you to use the maximum available processors on the machine.

With SQL Server 2016 and above, if more than eight physical cores per NUMA node or socket are detected at startup, soft NUMA nodes are created automatically. Starting with SQL Server 2016 (13.x), use the following recommended guidelines when you configure the maximum degree of parallelism server value:

  • Single NUMA node: < = 8 logical processors, keep MAXDOP <= actual number of cores
  • Single NUMA node: > 8 logical processors, keep MAXDOP = 8
  • Multiple NUMA nodes: < =16 logical processors, keep MAXDOP <= actual number of cores
  • Multiple NUMA nodes: > 16 logical processors, keep MAXDOP = 16 (SQL Server 2016 and above), keep MAXDOP = 8 (prior to SQL Server 2016)

For more information, see Configure the max degree of parallelism (server configuration option).

SQL Server estimates how costly a query is when run. If this cost exceeds the cost threshold of parallelism, SQL Server considers a parallel plan for this query. The number of processors it can use is defined by the instance-level maximum degree of parallelism, which is superseded by the database-level maximum degree of parallelism, which in turn is superseded by the query hint for maximum degree of parallelism at the query level.

To gather the current NUMA configuration for SQL Server 2016 and higher, run the following query:

select
 @@SERVERNAME,
 SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
cpu_count,  /*the number of logical CPUs on the system*/
hyperthread_ratio, /*the ratio of the number of logical or physical cores that are exposed by one physical processor package*/
softnuma_configuration, /* 0 = OFF indicates hardware default, 1 = Automated soft-NUMA, 2 = Manual soft-NUMA via registry*/
softnuma_configuration_desc, /*OFF = Soft-NUMA feature is OFF, ON = SQL Server automatically determines the NUMA node sizes for Soft-NUMA, MANUAL = Manually configured soft-NUMA */
socket_count, /*number of processor sockets available on the system*/
numa_node_count /*the number of numa nodes available on the system. This column includes physical numa nodes as well as soft numa nodes*/
from 
 sys.dm_os_sys_info

You can configure the max_degree_of_parallelism value under a custom parameter group. In the following screenshot, we change the value to 4.

You can check the current value using the following query:

# sp_configure 'max_degree_of_parallelism'

Monitoring

You can use the sys.dm_os_wait_stats dynamic management view (DMV) to capture details on the most common wait types encountered in your environment. On the Performance Insights dashboard, you can slice by wait types to get details on top wait types, as shown in the following screenshot.

If you see an increase in these metrics and parallelism-related wait types (such as CXPACKET), you might want to revisit the max degree of parallelism setting.

When to change the configuration

When the server has more than eight cores and you observe parallelism-related wait types, you should change this value according to best practices and regressive testing, monitor the wait types, and adjust further if needed. You can monitor the wait types using the methods outlined earlier in this section. Typically, for several short-lived, repetitive queries (OLTP), a lower MAXDOP setting works well because you can lose a lot of time with higher MAXDOP for synchronization of threads running subtasks. For online analytical processing (OLAP) workloads (longer and fewer queries), a higher maximum degree of parallelism can give better results because the query can use more cores to complete the work quickly.

You can also set max degree of parallelism at the database level, starting at SQL Server 2014 SP2. The database-level setting overwrites the server-level configuration. Similarly, you can use a query hint specifying MAXDOP to override both the preceding settings.

Cost threshold for parallelism

The cost threshold for parallelism parameter determines the times at which SQL Server creates and runs parallel plans for queries. A parallel plan for a query only runs when the estimated cost of the serial plan for that query exceeds the value specified in the cost threshold for parallelism. This parameter is based on an abstracted value of cost of operation and not on estimated time.

The default value for this parameter is 5. Historically, the default value was 5 because processors had exorbitant price tags and processing power was low, therefore query processing was slower. Processors today are much faster. Comparatively smaller queries (for example, the cost of 32) don’t see much improvement with a parallel run, not to mention the overhead with coordination of a parallel run.

With several queries going for a parallel plan, you may end up in a scenario with wait types like scheduler yield, thread pool, and parallelism related.

You can configure the cost threshold for parallelism value under a custom parameter group. In the following screenshot, we change the value to 50 for 64 core environments.

You can change this parameter using a custom parameter group. To check the current value, use the following query:

# sp_configure 'cost_threshold_for_parallelism'

For more details on this configuration, refer to Configure the cost threshold for parallelism (server configuration option).

Monitoring

In Performance Insights, you can monitor CXPACKET wait events. If this is on higher side, you may want to increase cost threshold for parallelism, as described earlier in this post.

When to change the configuration

If you see lot of queries that run in serial execution, and CPU utilization is low, and where the instance can handle additional computational load, it’s advised to run queries in parallel. There is not a recommended value for this setting, and it depends on iterative testing to find the right value. This parameter works in conjunction with max degree of parallelism.

Optimize for ad hoc workloads

To improve plan cache efficiency, configure optimize for ad hoc workloads. This works by only caching a compiled plan stub instead of a complete run plan on the first time you run an ad hoc query, thereby saving space in the plan cache. If the ad hoc batch runs again, the compile plan stub helps recognize the same and replaces the compiled plan stub with the full compiled plan in the plan cache.

To find the number of single-use cached plans, enter the following query:

SELECT objtype,
 cacheobjtype, 
SUM(refcounts),
  AVG(usecounts), 
  SUM(CAST(size_in_bytes AS bigint))/1024/1024 AS Size_MB
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1 AND objtype = 'Adhoc'
GROUP BY cacheobjtype, objtype

You can check the size of a stub and the plan of a query by running a query at least twice and checking the size in the plan cache using a query similar to the following:

select * from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where text like '%<text in your query>%'

You can configure the optimize_for_ad_hoc_workloads value under a custom parameter group. In the following screenshot, we set the value to 1.

You can change this value in the custom parameter group. To check the current value, run the following query:

# sp_configure 'optimize for ad hoc workloads'

For more details, refer to optimize for ad hoc workloads (server configuration option).

Monitoring

In addition to the preceding query, you can check the number of ad hoc queries on the Performance Insights dashboard by comparing the following:

  • Batch requests – Number of Transact-SQL command batches received per second.
  • SQL compilations – Number of SQL compilations per second. This indicates the number of times the compile code path is entered. It includes compiles caused by statement-level recompilations in SQL Server.

When to change the configuration

If your workload has many single-use ad hoc queries, it’s recommended to enable this parameter. For better performance, it is advised to follow best practice of running SQL on procedural code that takes advantages on cached plans. Unless there is a specific use case of using ad hoc queries, it is highly recommended to use procedural code such as functions and stored procedures.

Optimize transaction logs with virtual log files

Each physical log file is internally split into virtual log files (VLFs) by the database engine; there is no fixed number or size for virtual log files. These are determined dynamically during creation or extension of the log file. Starting with SQL Server 2012, the following is used to determine the size and number of virtual log files:

  • If next growth is less than 1/8 of the current log physical size, then create 1 VLF that covers the growth size
  • If the next growth is more than 1/8 of the current log size, use the pre-2014 method, namely:
  • If growth is less than 64 MB, create 4 VLFs that cover the growth size (for example, for 1-MB growth, create 4 VLFs of size 256 KB)
  • If growth is from 64 MB up to 1 GB, create 8 VLFs that cover the growth size (for example, for 512-MB growth, create 8 VLFs of size 64 MB)
  • If growth is larger than 1 GB, create 16 VLFs that cover the growth size (for example, for 8-GB growth, create 16 VLFs of size 512 MB)

To find the number of VLFs on each database, use the following code:

SELECT name, count(d.database_id) as "Total_VLF_Count" from sys.databases sd
cross apply sys.dm_db_log_info(sd.database_id) d
group by name

A large number of virtual log files has the following effects:

  • Recovery of database during startup takes more time
  • Restores are longer
  • Database mirroring or Always On encounters error messages 1413, 1443, and 1479, indicating timeout
  • Memory-related errors
  • Replication or change data capture (CDC) yields significant latency

Monitoring

Use the preceding query to continuously monitor and alert on a high number of virtual log files.

When to change the configuration

If you see high I/O on log files that correlates to I/O and transactional latency, it is advised to follow best practices when creating and growing transaction log files. A reduced number of virtual log files helps in various scenarios such as recovery, restore, minimizing replication, and Always On lag.

Configure tempdb and enable autogrowth

On a busy database server that frequently uses tempdb, you may notice severe blocking when the server is experiencing a heavy load. You may sometimes notice the tasks are waiting for tempdb resources. The wait resources are pages in tempdb. These pages might be of the format 2:x:x, and therefore on the PFS and SGAM pages in tempdb.

To improve the concurrency of tempdb, increase the number of data files to maximize disk bandwidth and reduce contention in allocation structures. You can start with the following guidelines:

  • If the number of logical processors <=8, use the same number of data files as logical processors.
  • If the number of logical processors > 8, use eight data files, and increase in multiples of 4 if you see tempdb contention, maximum up to the number of logical processors on the server. For more details, refer to Recommendations to reduce allocation contention in SQL Server tempdb database.

You add multiple tempdb files because the Amazon RDS primary account has been granted the control permission on tempdb.

The following query creates and modifies four files with parameters SIZE = 8MB, FILEGROWTH = 10% (you should choose parameters best suited for your environment):

ALTER DATABASE tempdb MODIFY FILE ( NAME = N'tempdev', SIZE = 8MB, FILEGROWTH = 10%)
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdb2', FILENAME = N'D:\RDSDBDATA\Data\tempdb2.ndf' , SIZE = 8MB , FILEGROWTH = 10%)
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdb3', FILENAME = N'D:\RDSDBDATA\Data\tempdb3.ndf' , SIZE = 8MB , FILEGROWTH = 10%)
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdb4', FILENAME = N'D:\RDSDBDATA\Data\tempdb4.ndf' , SIZE = 8MB , FILEGROWTH = 10%)

You can use sp_helpdb 'tempdb' to verify the changes.

Note that for a Multi-AZ setup, remember to make this change on the Disaster Recovery as well.

When you create multiple files, you may still want to maintain the total size of the tempdb equal to what it was with a single file. In such cases, you need to shrink a tempdb file to achieve the desired size. To shrink the tempdev file, enter the following code:

exec msdb..rds_shrink_tempdbfile @temp_filename='tempdev', @target_size =10;

To shrink a templog file, enter the following code:

exec msdb..rds_shrink_tempdbfile @temp_filename='templog', @target_size =10;

Following the tempdev shrink command, you can alter the tempdev file and set the size as per your requirement.

When initial pages are created for a table or index, the MIXED_PAGE_ALLOCATION setting controls whether mixed extent can be used for a database or not. When set to OFF, it forces page allocations on uniform extents instead of mixed extents, reducing contention on the SGAM page.

Starting with SQL Server 2016 (13.x), this behavior is controlled by the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE. For example, use the following query to turn it off:

alter database <database name> MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES

AUTOGROW_ALL_FILES determines that, when a file needs to grow in a file group, all the files in the file group grow with the same increment size.

Starting with SQL Server 2016 (13.x), this behavior is controlled by the AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES option of ALTER DATABASE. You may use the following query to enable AUTOGROW_ALL_FILES:

alter database <database name> set MIXED_PAGE_ALLOCATION OFF

Monitoring

You want to monitor for wait types on tempdb, such as PAGELATCH. You may monitor this via Performance Insights, as shown earlier in this post.

When to change the configuration

When wait resources are like 2:x:x, you want to revisit the tempdb configuration.

To check the wait resource in tempdb, use the following query:

# select db_name(2) as db,* from master..sysprocesses where waitresource like '2%'

Update statistics

If the optimizer doesn’t have up-to-date information about the distribution of key values (statistics) of table columns, it can’t generate optimal run plans. Update the statistics for all the tables regularly; the frequency of the update statistics depends on the rate at which the database handles DML operations.

For more information, see UPDATE STATISTICS. Note that this process works one table at a time. sp_updatestats, which is a database-level command, is not available in Amazon RDS. You may either write a cursor using UPDATE STATISTICS to update statistics on all the objects in a database, or you may build a wrapper around sp_updatestats.

Refer to the following workaround to use a wrapper around sp_updatestats:

create procedure myRDS_updatestats
with execute as ‘dbo’
as
exec sp_updatestats
go

Now, we run grant execute on our newly created procedure to a user:

grant execute on myRDS_updatestats to <user>
go

Creating a custom parameter group in Amazon RDS for SQL Server

To make these configuration changes, first determine the custom DB parameter group you want to use. You can create a new DB parameter group or use an existing one. If you want to use an existing custom parameter group, skip to the next step.

Create a new parameter group

To create a new parameter group, complete the following steps:

  1. On the Amazon RDS console, choose Parameter groups in the navigation pane.
  2. Choose Create parameter group.
  3. For the parameter group family, choose the applicable family on the drop-down menu (for example, for SQL Server 2012 Standard Edition, choose sqlserver-se-11.0).
  4. Enter a name and description.
  5. Choose Create.

For more information, see Creating a DB parameter group.

Modify the parameter group

To modify your parameter group, complete the following steps:

  1. On the Amazon RDS console, choose Parameter groups in the navigation pane.
  2. Choose the parameter group you created (or an existing one).
  3. Choose Edit parameters.
  4. Search for the parameter you want to modify (for example, max_server_memory, max_degree_of_parallelism, or 7).
  5. Change the value as needed.
  6. Choose Save.

Repeat these steps for each parameter you want to change.

For more information, see Modifying parameters in a DB parameter group.

Attach the custom parameter group to your instance

To attach the parameter group to your instance, complete the following steps:

  1. On the Amazon RDS console, choose the instance you want to attach the DB parameter group to.
  2. On the Instance Actions tab, choose Modify.
  3. On the Modify instance page, under Database Options, on the DB parameter group drop-down menu, choose your custom parameter group.
  4. Choose Continue.
  5. On the next page, select Apply immediately.
  6. Choose Continue.
  7. Choose Modify DB instance.

Restart the DB instance

For the changes to take effect, you need to restart the DB instance.

  1. On the Amazon RDS console, choose Instances in the navigation pane.
  2. Choose your instance.
  3. Under Instance details, you should see the parameter group you’re applying.
  4. When the status changes to Pending reboot (this may take a few minutes), under Instance actions, choose Reboot.

Check the parameter group is attached

To confirm that the parameter group is attached to your instance, complete the following steps:

  1. On the Amazon RDS console, choose the instance you want to check the parameter group for.
  2. On the Details tab, look at the value for Parameter Group.

Verify the configuration changes

To verify the configuration changes, complete the following steps:

  1. Connect to your Amazon RDS for SQL Server instance using your primary user account.
  2. Run the following to verify the configuration changes:
# sp_configure

Conclusion

This post discussed how to fine-tune some parameters in Amazon RDS for SQL Server to improve the performance of critical database systems. The recommended values are applicable to most environments; however, you can tune them further to fit your specific workloads. We recommend changing one or two parameters at a time and monitoring them to see the impact.

About the authors

Saikat Banerjee is a cloud support engineer with Amazon Web Services.

Sugeshkumar Rajendran, a Senior Data Architect at AWS Professional Services, offers customers architectural guidance and technical support, facilitating their transition to the cloud and modernization. He values quality time with his family and enjoys extended drives.

Audit History

Last reviewed and updated in February 2024 by Sugeshkumar Rajendran| Senior Data Architect