SQL Server System Configuration Values
July 28th 2008 07:04
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 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
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
| 55 |
| Vote |
Add To: 
