{"id":1424,"date":"2011-11-29T13:54:41","date_gmt":"2011-11-29T13:54:41","guid":{"rendered":"http:\/\/www.obieta.com\/?p=1424"},"modified":"2011-11-29T13:54:41","modified_gmt":"2011-11-29T13:54:41","slug":"setting-server-configuration-options","status":"publish","type":"post","link":"http:\/\/obieta.com\/?p=1424","title":{"rendered":"Setting Server Configuration Options"},"content":{"rendered":"
\n

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.<\/p>\n

\n\n\n\n\n
\"ImportantImportant<\/strong><\/th>\n<\/tr>\n
\n

Advanced options should be changed only by an experienced database administrator or certified SQL Server technician.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

\n
\n
\"\"Using the sp_configure System Stored Procedure<\/a><\/p>\n
\n
\n<\/div>\n<\/div>\n<\/div>\n
<\/a><\/p>\n

When using sp_configure, you must run either RECONFIGURE or RECONFIGURE WITH OVERRIDE after setting a configuration option. The RECONFIGURE WITH OVERRIDE statement is usually reserved for configuration options that should be used with extreme caution. However, RECONFIGURE WITH OVERRIDE works for all configuration options, and you can use it in place of RECONFIGURE.<\/p>\n

\n\n\n\n\n
\"Note\" Note<\/strong><\/th>\n<\/tr>\n
\n

RECONFIGURE executes within a transaction. If any of the reconfigure operations fail, none of the reconfigure operations will take effect.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

The value for each option can be determined with the following statement.<\/p>\n

\n
\n
\n
\n
SELECT * FROM sys.configurations\nORDER BY name ;\nGO\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
The following example shows how to use\u00a0sp_configure\u00a0to change the\u00a0fill factor\u00a0option from its default setting to a value of\u00a0100.<\/p>\n
\n
\n
\n
\n
sp_configure 'show advanced options', 1;\nGO\nRECONFIGURE;\nGO\nsp_configure 'fill factor', 100;\nGO\nRECONFIGURE;\nGO\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\"\"Categories of Configuration Options<\/a><\/p>\n
\n
\n<\/div>\n<\/div>\n<\/div>\n
<\/a><\/p>\n
Configuration options take effect either:<\/p>\n