SQL Server System Configuration Values

By:   |   Comments   |   Related: > SQL Server Configurations


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 new views in SQL Server 2005, is one available with the configuration values, a description and the list of possible values? 

Solution
Catalog views return information used by the Microsoft SQL Server 2005 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.

Source - SQL Server 2005 Books Online - sys.configurations

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
 

All Advanced Options Configurations

SELECT *
FROM sys.configurations
WHERE is_advanced = 1
GO
 

All Options Requiring the RECONFIGURE Option

SELECT *
FROM sys.configurations
WHERE is_dynamic = 1
GO
 

All 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
 

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.

ManagementStudio MemoryConfigurationgif

Surface Area Configuration Tool
This tool provides access to some of the counters not found in Management Studio.  This tool provides a mechanism to view and change the values.  The Surface Area Configuration Tool can be launched by navigating to Start | All Programs | SQL Server 2005 | Configuration Tools | SQL Server Surface Area Configuration.

SurfaceAreaConfig

Next Steps



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Soundararajan Venkkataramani Soundararajan Venkkataramani's bio is coming soon...

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article

















get free sql tips
agree to terms