Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

SQL Server System Configuration Values

MSSQLTips author Soundararajan Venkkataramani By:   |   Read Comments   |   Related Tips: More > 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.

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.

Next Steps



Last Update: 7/25/2008


About the author
MSSQLTips author Soundararajan Venkkataramani
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     





 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.