Manage instance parameters in the console

Parameter

Description

Default

Value range

Ad Hoc Distributed Queries

Enables ad hoc distributed queries.

0

[0-1]

Database Mail XPs

Enables Database Mail.

0

[0-1]

blocked process threshold (s)

Specifies the threshold, in seconds (s), at which blocked process reports are generated.

0

[0-86400]

clr enabled

Specifies whether SQL Server can run user assemblies.

0

[0-1]

clr strict security

Controls the SAFE, EXTERNAL ACCESS, and UNSAFE permissions in SQL Server.

0

[0-1]

cost threshold for parallelism

Sets the cost threshold above which SQL Server creates and runs parallel plans for a query.

5

[0-32767]

default full-text language

Specifies the default language for full-text indexes.

1033

[0-6000]

default language

Specifies the default language for all newly created login accounts.

0

[0-33]

filestream access level

Changes the FILESTREAM access level for this SQL Server instance.

0

[0-2]

max degree of parallelism

Sets the maximum number of processors used in a parallel plan.

2

[0-64]

remote query timeout (s)

Specifies how long, in seconds (s), a remote operation can run before SQL Server times out.

600

[0-2147183647]

remote login timeout (s)

Specifies the number of seconds to wait before returning from a failed attempt to log in to a remote server.

10

[0-2147183647]

query wait (s)

Specifies the time that a query waits for resources before the query times out.

30

[-1-38400]

optimize for ad hoc workloads

Improves plan cache efficiency for workloads with many single-use ad hoc batches.

0

[0-1]

nested triggers

Controls whether triggers can cascade.

1

[0-1]

max worker threads

Sets the maximum number of worker threads available to SQL Server processes.

0

[128-65535]

max text repl size (B)

Specifies the maximum size of replication data.

65536

[0-2147483647]

remote proc trans

Protects server-to-server procedures by using a Microsoft Distributed Transaction Coordinator (MS DTC) transaction.

0

[0-1]

query governor cost limit

Sets an upper limit on the estimated cost for a query to run.

0

[0-2147483647]

recovery interval (min)

Defines an upper limit on the time, in minutes, that is required to recover a database.

0

[0-30]

min memory per query (KB)

Specifies the minimum amount of memory (in KB) allocated for a query's execution.

1024

[512-2147483647]

in-doubt xact resolution

Controls the default outcome for transactions that MS DTC cannot resolve.

0

[0-2]

rds_slow_log_threshold

Specifies the threshold in milliseconds (ms) for capturing slow SQL statements on an instance. SQL statements whose execution duration exceeds the threshold are recorded in slow query logs. This change takes effect in approximately 5 minutes. No instance restart is required.

0

[0-60000]

rds_capture_sql_param

Controls whether to display full SQL parameter details in the audit log and Slow Log Details pages. Set to 1 to enable (disabled by default). This change takes effect in approximately 5 minutes. No instance restart is required.

(@n int, @keyword nvarchar(100))
SELECT
    TOP (@n) PersonID,
    FullName,
    PhoneNumber
FROM
    Application.People
WHERE
    FullName LIKE @keyword
    OR PreferredName LIKE @keyword
ORDER BY
    PersonID;

exec sp_executesql N'
SELECT TOP (@n) PersonID, FullName, PhoneNumber
FROM    Application.People
WHERE   FullName LIKE @keyword OR
PreferredName LIKE @keyword
ORDER BY PersonID;',
N'@n int,@keyword nvarchar(100)',
@n = 5,
@keyword = N'%Kim%'

0

[0-1]

rds_sql_literal_masking

Controls whether to mask SQL literals in audit logs and slow query logs. Set this parameter to 1 to enable masking, or 0 (default) to disable it. When enabled, string literals in SQL statements are replaced with '***', numeric literals with *, and hexadecimal literals with 0x***. DDL statements are not affected. This change takes effect in approximately 5 minutes without an instance restart.

DELETE FROM
    test_mask
WHERE
    id = 3
    AND name = N'Wang Wu'
    AND phone = '12345678910'

DELETE FROM
    test_mask
WHERE
    id = *
    AND name = N'***'
    AND phone = '***'

0

0 or 1

Parameter

Description

Default

Value range

1204

Returns the resources and types of locks that are participating in a deadlock and the currently affected command.

0

[0-1]

1211

Disables lock escalation that is based on memory pressure or the number of locks. The SQL Server Database Engine does not escalate row or page locks to table locks.

0

[0-1]

1222

Returns the resources and types of locks that are participating in a deadlock and the currently affected command in an XML format that does not conform to any XSD schema.

0

[0-1]

1224

Controls lock escalation behavior. When enabled, SQL Server limits lock escalation based on the number of locks to help avoid widespread blocking in high-concurrency scenarios. Compared with Trace Flag 1211, which disables lock escalation entirely, 1224 is more flexible and limits escalation only when potential blocking is detected. It is commonly used in high-concurrency environments to avoid performance issues caused by unnecessary table-level locks.

0

[0-1]

2528

Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE.

0

[0-1]

3205

Disables hardware compression for tape drives.

0

[0-1]

3226

Suppresses backup log entries.

0

[0-1]

4199

Enables query optimizer (QO) hotfixes that are released in SQL Server cumulative updates and service packs.

0

[0-1]

4616

Makes server-level metadata visible to application roles.

0

[0-1]

6527

Disables memory dump generation on the first out-of-memory exception in CLR integration.

0

[0-1]

692

Disables fast inserts when you bulk load data into a heap or clustered index.

0

[0-1]

1117

When a file in a filegroup meets the autogrow threshold, all files in the filegroup grow.

0

[0-1]

1118

Forces page allocations on uniform extents instead of mixed extents to reduce contention on the SGAM page.

0

[0-1]

1262

Optimizes parallel processing for partitioned tables, especially in high-throughput scenarios. When you process large-scale partitioned tables, this parameter can help mitigate performance bottlenecks that are caused by uneven load distribution or partition-level parallel processing. This is particularly useful for queries that require parallel access to large amounts of data.

0

[0-1]

2335

Ensures that memory that is configured for SQL Server is still used by data caches, query execution, and other consumers.

0

[0-1]

2371

Changes the fixed update statistics threshold to a dynamic update statistics threshold.

0

[0-1]

2430

Enables alternate lock class cleanup.

0

[0-1]

3604

This flag is typically used with DBCC commands (e.g., DBCC PAGE) to view internal structures and metadata. It helps troubleshoot in-depth performance issues like lock or resource contention and is often used for performance diagnostics or data structure analysis.

0

[0-1]

6498

Manages the memory usage for compiling incoming queries to avoid compilation waits for concurrent large queries.

0

[0-1]

8048

Converts NUMA-partitioned memory objects to CPU-partitioned memory objects.

0

[0-1]