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.
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.
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.
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.