SQL Server System Configuration Values

Problem

Often times we need to review the SQL Server instance configuration values while troubleshooting performance problems.  What alternatives are available to capture this information?  With all of the views in SQL Server is there one with the configuration values, a description and the list of possible values? 

Solution

Catalog views return information used by the Microsoft SQL Server database engine. They are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized access to this information. All user available catalog metadata is exposed through catalog views. The catalog view that provides details about all the server wide configuration values is called sys.configurations.

The sys.configurations view has over 60 counters which provide information about all the server wide configuration options to include:

• name – Name of the Configuration option.
• value – Configured value for this option.
• minimum – Minimum value for the configuration option.
• maximum – Maximum value for the configuration option.
• value_in_use – Running value currently in effect for this option.
• description – Description of the configuration option.
• is_dynamic – A value of 1 indicates that the variable takes effect when the RECONFIGURE statement is executed.
• is_advanced – A value of 1 indicates that the variable is displayed only when the show advanced option is set.

Querying sys.configurations

The sys.configurations catalog view can be queried with simple SELECT statements.  Here are some examples:

Simple SELECT for all Values

SELECT *
FROM sys.configurations
GO

Advanced Options Configurations

SELECT *
FROM sys.configurations
WHERE is_advanced = 1
GO

Options Requiring the RECONFIGURE Option

SELECT *
FROM sys.configurations
WHERE is_dynamic = 1
GO

Memory Related Configurations

SELECT *
FROM sys.configurations
WHERE Name LIKE '%mem%'
GO

Alternative Information Sources

Below are alternatives to the sys.configurations catalog view:

sp_configure

The sp_configure command can be executed in a SQL Server Management Studio query window to obtain many of the same results as the sys.configurations catalog view.

EXEC sp_configure
GO

SQL Server Management Studio

The Server Properties interface can be found in Management Studio by right clicking on the server name, selecting ‘Properties’ then selecting the page in the left panel.  This interface gives you an opportunity to not only view but also change values.  Some requiring a restart while others do not.

Next Steps

• Take a look at the configured values for each of the server wide options and see if you want to modify the settings to take care of your typical usage scenario.
• It is recommended to change the value of the configuration options only after a thorough understanding of  the implication of doing the same.
• For more information about dynamic management views and functions visit:
  • Dynamic Management Views and Functions
    
    

Soundararajan Venkkataramani

Soundararajan Venkkataramani’s bio is coming soon…