ManageEngine® SQLDBManager Plus


SQL Configuration Options

<< Prev

Home

Next >>

 

SQL Configuration Options

 

You can manage and optimize SQL Server resources using configuration options in SQLDBManager. Optimizing these resources can have a significant effect on SQL Server's behavior and performance.

 

SQLDBManager lists all available configuration options, the range (minimum and maximum value) of possible settings, and configuration values.

 

Parameters

Description

Name

Name of the configuration option

Minimum

Minimum range of configuration value

Maximum

Maximum range of configuration value

Configured value The value currently configured to the server. For few options, this value takes effect for use only after restarting the SQL instance
Value in use The value currently in use for the server

Advanced options will be available or changed only when the 'show advanced option' is set to 1.

 

You can enable these options in the following two ways:

For some of the configuration options, the changes take effect only after restarting the SQL instance.The values used for the parameters Configured value and Value in use columns should match for a dynamically updated option. Options that require SQL Server to restart will initially show the changed value only in the Configured value column. After restart, the new value will appear in both the Configured value column and the Value in use column.

 

For example, the minimum and maximum server memory options are updated dynamically in the Database Engine; therefore, you can change them without restarting the server.

 

Note: Only users with Administrator / Operator with permission can edit the SQL Server Configuration Settings.

Parameter

Description
access check cache bucket count* The Access check cache bucket count option controls the number of hash buckets used for access check result cache. The default value - 0 indicates that SQL Server is managing these options.
access check cache quota* The access check cache quota option controls the number of entries used for access check result cache. The default value - 0 indicates that SQL Server is managing these options.
Ad Hoc Distributed Queries*

By default, SQL Server does not allow ad hoc distributed queries. When this option is set to 1, SQL Server allows ad hoc access. When this option is not set or is set to 0, SQL Server does not allow ad hoc access.

affinity I/O mask*

The value for affinity I/O mask specifies which CPUs in a computer are eligible to process SQL Server disk I/O operations. The values for affinity I/O mask are as follows:

  • A 1-byte affinity I/O mask covers up to 8 CPUs in a multiprocessor computer.

  • A 2-byte affinity I/O mask covers up to 16 CPUs in a multiprocessor computer.

  • A 3-byte affinity I/O mask covers up to 24 CPUs in a multiprocessor computer.

  • A 4-byte affinity I/O mask covers up to 32 CPUs in a multiprocessor computer.

  • To cover more than 32 CPUs, configure a four-byte affinity I/O mask for the first 32 CPUs and up to a four-byte affinity64 I/O mask for the remaining CPUs.

affinity mask*

The affinity mask option dynamically controls CPU affinity. The values for affinity mask are as follows:

  • A one-byte affinity mask covers up to 8 CPUs in a multiprocessor computer.

  • A two-byte affinity mask covers up to 16 CPUs in a multiprocessor computer.

  • A three-byte affinity mask covers up to 24 CPUs in a multiprocessor computer.

  • A four-byte affinity mask covers up to 32 CPUs in a multiprocessor computer.

  • To cover more than 32 CPUs, configure a four-byte affinity mask for the first 32 CPUs and up to a four-byte affinity64 mask for the remaining CPUs.

affinity64 I/O mask* The affinity64 I/O mask binds SQL Server disk I/O to a specified subset of CPUs. This option is only visible on the 64-bit version of SQL Server.
affinity64 mask* The affinity64 mask binds processors to specific threads. This option is only visible on the 64-bit version of SQL Server.
Agent XPs* Use the Agent XPs option to enable the SQL Server Agent extended stored procedures on this server. The possible values are:
  • 0 - indicating that SQL Server Agent extended stored procedures are not available (the default).

  • 1 - indicating that SQL Server Agent extended stored procedures are available.

The setting takes effect immediately without a server stop and restart.

allow updates This option is still present in the sp_configure stored procedure, although its functionality is unavailable in SQL Server.
awe enabled* You can use the Address Windowing Extensions (AWE) API to provide access to physical memory in excess of the limits set on configured virtual memory.
backup compression default Used by the BACKUP Transact-SQL statement to select the backup compression setting if a user does not specify WITH COMPRESSION or WITH NO_COMPRESSION. Used in SQL Server 2008 Enterprise and later only. Default backup compression is set to 0
blocked process threshold (s)* Use the blocked process threshold option to specify the threshold, in seconds, at which blocked process reports are generated. The threshold can be set from values 0 to 86,400.
c2 audit mode* This option will configure the server to record both failed and successful attempts to access statements and objects.
clr enabled Use the clr enabled option to specify whether user assemblies can be run by SQL Server. Possible values are as follows:
  • 0 - Assembly execution not allowed on SQL Server

  • 1 - Assembly execution allowed on SQL Server

cost threshold for parallelism* The cost threshold for parallelism option specifies the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. The cost threshold for parallelism option can be set to any value from 0 through 32767.
cross db ownership chaining Use the cross db ownership chaining option to configure cross-database ownership chaining for an instance of Microsoft SQL Server. Possible Values are :
  • 0 - cross db ownership chaining is off for the instance, cross-database ownership chaining is disabled for all databases.
  • 1 - cross db ownership chaining is on for the instance, cross-database ownership chaining is on for all databases.
cursor threshold*

The cursor threshold option specifies the number of rows in the cursor set at which cursor keysets are generated asynchronously. Possible Values are:

  • 1 - all keysets are generated synchronously.
  • 0 - all cursor keysets are generated asynchronously.
Database Mail XPs* The Database Mail XPs option enables Database Mail on this server. Possible values are:
  • 0 - Database Mail is not available (default).

  • 1 - Database Mail is available.

default full-text language* The default full-text language option specifies a default language value for full-text indexed columns. The default value of this option is the language of the server and requires an LCID value.
default language The default language option to specify the default language for all newly created logins.
default trace enabled* The default trace enabled option enables or disables the default trace log files.
disallow results from triggers*

This option controls whether triggers return result sets.

  • When set to 1 (ON) any attempt by a trigger to return a result set fails, and the user receives an error message
  • The default setting for this option is 0 (OFF).
filestream access level

Use the filestream access level option to change the FILESTREAM access level for this instance of SQL Server. Possible values are:

  • 0 - Disables FILESTREAM support for this instance.
  • 1 - Enables FILESTREAM for Transact-SQL access.
  • 2 - Enables FILESTREAM for Transact-SQL and Win32 streaming access.
fill factor (%)* The fill factor option specifies how full Microsoft SQL Server should make each page when it creates a new index using existing data.
ft crawl bandwidth (max)* The ft crawl bandwidth option specifies the size to which the pool of large memory buffers can grow. The max parameter value specifies the maximum number of buffers that the full-text memory manager should maintain in a large buffer pool. If the max value is zero, then there is no upper limit to the number of buffers that can be in a large buffer pool.
ft crawl bandwidth (min)* The ft crawl bandwidth option specifies the size to which the pool of large memory buffers can grow. The min parameter specifies the minimum number of memory buffers that must be maintained in the pool of large memory buffers. Upon request from the Microsoft SQL Server memory manager, all extra buffer pools will be released but this minimum number of buffers will be maintained. If, however, the min value specified is zero, then all memory buffers are released.
ft notify bandwidth (max)* The ft notify bandwidth option specifies the size to which the pool of small memory buffers can grow. The max parameter value specifies the maximum number of buffers that the full-text memory manager should maintain in a small buffer pool. If the max value is zero, then there is no upper limit to the number of buffers that can be in a small buffer pool.
in-doubt xact resolution* The in-doubt xact resolution option controls the default outcome of transactions that the Microsoft Distributed Transaction Coordinator (MS DTC) is unable to resolve. Inability to resolve transactions may be related to the MS DTC down time or an unknown transaction outcome at the time of recovery. Possible Values are :
  • 0 - No presumption. Recovery fails if MS DTC cannot resolve any in-doubt transactions.
  • 1 - Presume commit. Any MS DTC in-doubt transactions are presumed to have committed.
  • 2 - Presume abort. Any MS DTC in-doubt transactions are presumed to have aborted.
index create memory (KB)* The index create memory option controls the maximum amount of memory initially allocated for creating indexes. If more memory is later needed for index creation, and the memory is available, the server will use it, thus exceeding the setting of this option. If additional memory is not available, the index creation will continue using the memory already allocated. The default value for this option is 0 (self-configuring).
lightweight pooling* The lightweight pooling option provides a means of reducing the system overhead associated with the excessive context switching seen in symmetric multiprocessing (SMP) environments. Possible Values:

1 - causes SQL Server to switch to fiber mode scheduling.

The default value for this option is 0.

locks* The locks option to set the maximum number of available locks, thereby limiting the amount of memory used. Possible Values:

0 (Default) - allows the Database Engine to allocate and deallocate lock structures dynamically, based on changing system requirements.

When locks is not set to 0, lock escalation occurs when the number of locks reaches 40 percent of the value specified for locks.

max degree of parallelism* You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. Possible Values:
  • 0 default value - allows SQL Server to use all the available processors up to 64 processors.
  • 1 - To suppress parallel plan generation
  • greater than 1 - to restrict the maximum number of processors used by a single query execution.
max full-text crawl range* The max full-text crawl range option optimizes CPU utilization; you can specify the number of partitions that Microsoft SQL Server should use during a full index crawl. The default value of this option is 4; the minimum value is 1, and the maximum value is 256.
max server memory (MB)* Use the max server memory option to reconfigure the amount of memory (in megabytes) that is managed by the SQL Server Memory Manager for a SQL Server process used by an instance of SQL Server.
max text repl size (B) The max text repl size option to specify the maximum size (in bytes) of text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, and image data that can be added to a replicated column or captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement. The default is 65536. A value of -1 indicates no limit, other than the limit imposed by the data type.
max worker threads* The max worker threads option is used to configure the number of worker threads available to Microsoft SQL Server processes.

0(default value)- allows SQL Server to automatically configure the number of worker threads at startup. This setting is best for most systems.

media retention* The media retention option provides a system-wide default for the length of time to retain each backup set. This option helps protect backups from being overwritten until the specified number of days has elapsed. The default is 0 days.
min memory per query (KB)* The min memory per query option specifies the minimum amount of memory (in kilobytes) that will be allocated for the execution of a query.
min server memory (MB)* The min server memory option is used to reconfigure the amount of memory (in megabytes) that is managed by the SQL Server Memory Manager for a SQL Server process used by an instance of SQL Server. The default setting for min server memory is 0.
nested triggers Use the nested triggers option to control whether a trigger can cascade (perform an action that initiates another trigger that initiates another trigger, and so on). Possible values:
  • 0 - triggers cannot cascade.
  • 1 - triggers can cascade to as many as 32 levels.
network packet size (B)* The network packet size option to set the packet size (in bytes) used across the entire network. If an application sends and receives small amounts of information, the packet size can be set to 512 bytes, which is sufficient for data transfers.
Ole Automation Procedures* Use the Ole Automation Procedures option to specify whether OLE Automation objects can be instantiated within Transact-SQL batches. Possible Values:
  • 0 - OLE Automation Procedures are disabled. Default for new instances of SQL Server.
  • 1 - OLE Automation Procedures are enabled.
open objects* This option is still present in sp_configure, although its functionality has been disabled in Microsoft SQL Server. (The setting has no effect.) In SQL Server, the number of open database objects is managed dynamically and is limited only by the available memory. The open objects option available in sp_configure for backward compatibility with existing scripts.
optimize for ad hoc workloads* The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan.
PH timeout (s)* The PH timeout option to specify the time, in seconds, that the full-text protocol handler should wait to connect to a database before timing out. The default value is 60 seconds. Increase the ph timeout value when connection attempts are timing out due to temporary network issues.
precompute rank* The precompute rank server-level advanced option to improve the performance of FREETEXTTABLE queries that specify the top_n_by_rank parameter. When the option is set to 1, FREETEXTTABLE queries specified with top_n_by_rank use precomputed rank data stored in the full-text catalogs. When you use precomputed rank data, the result set that is returned by top_n_by_rank may not contain the same results as those that are returned when the precompute rank option is set to 0.
priority boost* Use the priority boost option to specify whether Microsoft SQL Server should run at a higher Microsoft Windows 2000 or Windows 2003 scheduling priority than other processes on the same computer. Possible Values:
  • 1 - SQL Server runs at a priority base of 13 in the Windows 2000 or Windows Server 2003 scheduler.
  • 0 (default) - A priority base of 7.
query governor cost limit* Use the query governor cost limit option to specify an upper limit on the time period in which a query can run. If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost exceeding that value. Specifying 0 (the default) for this option turns off the query governor, and all queries are allowed to run without any time limitation.
query wait (s)* The query wait option specifies the time in seconds (from 0 through 2147483647) that a query waits for resources before timing out. If the default value of -1 is used, or if –1 is specified, then the time-out is calculated as 25 times of the estimated query cost.
recovery interval (min)* Use the recovery interval option to set the maximum number of minutes per database that Microsoft® SQL Server™ needs to recover databases. Keep recovery interval set at 0 (self-configuring) unless you notice that checkpoints are impairing performance because they are occurring too frequently. If this is the case, try increasing the value in small increments.
remote access Use the remote access option to control the execution of stored procedures from local or remote servers on which instances of Microsoft SQL Server are running. Possible Values:
  • 0 - to prevent local stored procedures from being run from a remote server or remote stored procedures from being run on the local server.
  • 1 (default) - to grant permission to run local stored procedures from remote servers or remote stored procedures from the local server
remote admin connections SQL Server provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a SQL Server Database Engine connection. Possible Values:
  • 0 - Indicates only local connections are allowed by using the DAC.
  • 1 - Indicates remote connections are allowed by using the DAC.
remote login timeout (s) Use the remote login timeout option to specify the number of seconds to wait before returning from a failed attempt to log in to a remote server. The default setting for remote login timeout is 20 seconds. A value of 0 allows for an infinite wait.
remote proc trans The remote proc trans option protects the actions of a server-to-server procedure through a Microsoft® Distributed Transaction Coordinator (MS DTC) transaction.
remote query timeout (s) Use the remote query timeout option to specify how long (in seconds) a remote operation can take before Microsoft SQL Server times out. The default value is 600, which allows a 10-minute wait.
Replication XPs* This option is for internal use only.
scan for startup procs* The scan for startup procs option to scan for automatic execution of stored procedures at Microsoft SQL Server startup time. Possible Values:
  • 1 - SQL Server scans for and runs all stored procedures defined on the server.
  • 0 (default value) - do not scan
server trigger recursion The server trigger recursion option specifies whether to allow server-level triggers to fire recursively. Possible values:
  • 1 (ON) - server-level triggers will be allowed to fire recursively.
  • 0 (OFF)- server-level triggers cannot be fired recursively.
set working set size* The set working set size option to reserve physical memory space for SQL Server that is equal to the server memory setting. Before setting set working set size to 1, set both min server memory and max server memory to the same value, the amount of memory you want SQL Server to use.
show advanced options

The show advanced options option to display the sp_configure system stored procedure advanced options. When you set show advanced options to 1, you can list the advanced options by using sp_configure. The default is 0.

SMO and DMO XPs* Use the SMO and DMO XPs option to enable SQL Server Management Object (SMO) extended stored procedures on this server. Possible Values are:
  • 0 - SMO XPs are not available.
  • 1 - SMO XPs are available. This is the default.
SQL Mail XPs* Use the SQL Mail XPs option to enable SQL Mail on this server. The possible values are:
  • 0 indicating SQL Mail is not available (default)

  • 1 indicating SQL Mail is available

transform noise words* Use the transform noise words server configuration option to suppress an error message if noise words/stopwords, cause a Boolean operation on a full-text query to return zero rows. Possible Values:
  • 0-Noise words (or stopwords) are not transformed.
  • 1-Noise words (or stopwords) are transformed. They are ignored, and the rest of the query is evaluated
two digit year cutoff* The two digit year cutoff option to specify an integer from 1753 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years.
user connections* The user connections option specifies the maximum number of simultaneous user connections allowed.
user instance timeout* The User Instance Timeout option that you can access through sp_configure is not supported in Microsoft SQL Server 2008. This option works only with SQL Server 2008 Express (SQL Server Express).
user instances enabled The user instance enabled option that you can access through sp_configure is not supported in Microsoft SQL Server 2008. This option works only with SQL Server 2008 Express (SQL Server Express).
user options

The user options option specifies global defaults for all users. The user options option allows you to change the default values of the SET options (if the server's default settings are not appropriate). Possible Values:

  • 1- DISABLE_DEF_CNST_CHK - Controls interim or deferred constraint checking.
  • 2- IMPLICIT_TRANSACTIONS - For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.
  • 4 - CURSOR_CLOSE_ON_COMMIT - Controls behavior of cursors after a commit operation has been performed.
  • 8 - ANSI_WARNINGS - Controls truncation and NULL in aggregate warnings.
  • 16 - ANSI_PADDING - Controls padding of fixed-length variables.
  • 32 - ANSI_NULLS - Controls NULL handling when using equality operators.
  • 64 - ARITHABORT - Terminates a query when an overflow or divide-by-zero error occurs during query execution.
  • 128 - ARITHIGNORE - Returns NULL when an overflow or divide-by-zero error occurs during a query.
  • 256 - QUOTED_IDENTIFIER - Differentiates between single and double quotation marks when evaluating an expression.
  • 512 - NOCOUNT - Turns off the message returned at the end of each statement that states how many rows were affected.
  • 1024 - ANSI_NULL_DFLT_ON - Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.
  • 2048- ANSI_NULL_DFLT_OFF - Alters the session's behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls.
  • 4096 - CONCAT_NULL_YIELDS_NULL- Returns NULL when concatenating a NULL value with a string.
  • 8192- NUMERIC_ROUNDABORT- Generates an error when a loss of precision occurs in an expression.
  • 16384 - XACT_ABORT - Rolls back a transaction if a Transact-SQL statement raises a run-time error.
xp_cmdshell*

The xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. By default, the xp_cmdshell option is disabled on new installations and can be enabled.

 

 


<< Prev

Home

Next >>

WMI Monitor

Cluster Details