Optimizing SQL Server instance configuration

MSDN introduced the concept of SP_Configure with the following understanding. You can manage and optimize SQL Server resources through configuration options by using SQL Server Management Studio or the sp_configure system stored procedure. The most commonly used server configuration options are available through SQL Server Management Studio; all configuration options are accessible through sp_configure. Consider the effects on your system carefully before setting these options.

Getting ready

To perform this recipe, you will need a developer or an enterprise edition of SQL Server Denali CTP 3 or a greater edition. In order to see the current instance level configuration settings in SQL Server, execute the following query:

SELECT
*
FROM
sys.configurations
ORDER BY
name
GO

The result of this query will show you the list of settings out of which we are going to see few of the most important performance-related settings. All configurations are given...