Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Like this article? We recommend

Configuring SQL Server

SQL Server is largely self-configuring, and in many cases it works correctly "right out of the box." You should understand some options, however, in case your circumstances require changes from the default behavior. You can change all configuration options with Transact-SQL, many of them with Enterprise Manager, and all of them with SQL DMO.

This section discusses generic options, covering options that relate to specific tasks as the task is discussed. Configuration options are classified on two different dimensions:

  • Standard versus advanced. Standard options are the most commonly changed, whereas advanced options are infrequently changed.

  • Static versus dynamic. Static options do not take effect until the server is restarted, whereas the dynamic options take effect immediately.

In SQL Server 7.0, the advanced options are visible by default. If you turn off the Show Advanced Options, you can't modify any of the advanced options using Transact-SQL. In the following discussions on options, these categories are in parentheses at the end of the description of the option:

  • (S, S). Standard option, requires restart

  • (S, D). Standard option, takes effect immediately

  • (A, S). Advanced option, requires restart

  • (A, D). Advanced option, takes effect immediately

Use these category descriptions to understand when options take effect and when you are required to restart SQL for them to take effect.

Miscellaneous Options

Some options apply to the server as a whole and don't fit into a neat category such as "memory." I've grouped them here.

  • Allow Updates. When this option is set to true, direct updates to the system tables are permitted. In most cases, you do not want this option to be turned on. In those rare cases in which you must modify system tables, I recommended that you do it with the server in single-user mode. Note that if this setting is true when you create a stored procedure, the stored procedure can modify system tables even after the option is turned off. (S, D)

  • Danger

    Updating system tables directly can cause your server to fail to start or to behave erratically.

  • Default Language. This option specifies the ID (taken from syslanguages) for the default language, and controls default formats for displaying dates and gives meaning to the date parts.

    If you have a localized version of SQL Server (French, German, Spanish, Japanese), the default language specifies the language in which error messages display. If you have a Japanese SQL Server, and the default language is Japanese, for example, SQL Server error messages display in Japanese. If the default language on a localized SQL Server is not the local language, the messages displays in U.S. English. If you have a French SQL Server and specify Italian as the default language, for example, all error messages will be in U.S. English. (S, D)

  • Language in Cache. This option specifies the number of languages that can be held in cache. The default is 3. (S, S)

  • Network Packet Size (B). The network packet size specifies the network packet size in bytes. By default, SQL Server uses 4096 bytes as the packet size. If you routinely send large amounts of data across the network, you may benefit from a larger packet size. Conversely, if most of your transmissions are small, you might want to set this to 512 bytes, which is sufficient for many small data transmissions. If you have multiple protocols, set the network packet size to that which is appropriate for the most commonly used protocol. Note that a client application can specify a packet size that differs from the one specified here. (A, D)

  • Recovery Interval (Min). This option specifies the number of minutes it will take SQL Server to recover all databases on the system at startup. It influences the frequency with which SQL Server takes checkpoints. By default, it is 0, which means that SQL Server configures it appropriately. In general, you should leave this option alone unless you find that checkpoints are being taken too frequently (see Chapter 6 for a discussion of checkpoints). In that case, you may want to experiment with increasing the value in small increments. (A, D)

  • Show Advanced Options. This option determines whether you can see or change the advanced options when you run sp_configure. By default, it is set so that advanced options are visible.

    Note that if you run the Upgrade Wizard (see Chapter 2, "Installation and Upgrade," for more information on this) and ask to migrate SQL Server 6.5 configuration options, you will find that this option is set to off. (S, D)

Memory-Related Options

The options in this section all relate to how SQL Server uses memory. In SQL Server 7.0, memory is largely self-configuring. If you have a machine running only SQL Server, you probably won't have to modify these settings. If you are running other applications, however, you may want to adjust some of these settings.

By default, SQL Server monitors its environment. When the operating system has less than 5MB (± 200KB) of free memory, SQL Server gives memory it would have normally kept for its available pool back to the operating system. When there is more than 5MB free, SQL Server takes the memory back.

The memory options available are as follows:

  • Extended Memory Size (MB). This option is primarily for forward compatibility. It will be available in future versions of SQL Server running on a future version of Windows NT that has support for 64-bit addressing. Some hardware vendors may support this capability under Windows NT 4.0. (A, S)

  • Locks. In previous versions of SQL Server, the amount of space available for locks was fixed, and it was necessary to tinker with this option. In SQL Server 7.0, SQL Server allocates 2% of the available memory for locks. When additional memory is needed, SQL Server allocates it unless doing so would reduce the amount of free memory for the OS to less than 5MB. If this happens, SQL Server issues a message that you are out of locks. You should change the number of locks only if you receive this message. The default for this option is 0, which means that it is self-configuring. If you explicitly specify this value, the value must be between 5,000 and 2,147,483,647. (A, S)

  • Min Server Memory (MB), Max Server Memory (MB). These two options work together. If you set Min Server Memory and Max Server Memory to the same value, SQL Server uses a fixed portion of memory. If SQL Server is the only application running on the machine, leave these settings alone. If other applications are running, setting a Max Server Memory influences how quickly they start up, because there is normally a delay between when the competing application begins and when SQL Server frees memory for it. If you want to guarantee that SQL Server has a set minimum amount of memory no matter what, specify a Min Server Memory. The smallest value you can specify for Max Server Memory is 4MB; the highest value is limited by the resources available on your system. If you specify Min Server Memory, and the server is involved with replication, the value must be greater than or equal to 16.

    If your server is periodically idle for long periods of time, you may want to set Min Server Memory so that the memory is immediately available when a query does come. If you have other critical applications on the machine that might also have idle applications, you might want to set Max Server Memory so that memory is available to those applications when they become active. (A, S)

  • General Tip

    If you have installed Full-Text Indexing and are running the MS Search Service, you must specify a Max Server Memory so that there is enough room for the Search Service to run. In this case, you must configure NT's virtual memory so that there is virtual memory (virtual memory for SQL Server and virtual memory for any other concurrently running applications equal to 1.5 times the machine's physical memory for the search service):

    NT virtual memory "e 1.5 x physical memory

    You also need to specify a Max Server Memory if you are running SQL Server on the same machine as Exchange Server.

  • Set Working Set Size. This option reserves physical memory for SQL Server. If Windows NT must page, it must swap out other processes. By default, this option is off, and idle SQL Server memory can be swapped out. It is best to leave it off. (A, S)

  • Open Objects. An open object is any table, stored procedure, view, and so on currently in use. Note that no matter how many users there are for an object, it is one open object. This option is self-configuring by default, and in most cases you should leave it that way. If you receive repeated instances of the following message in your Error log, you may want to consider setting it to some value:

    I would start with 10,000 and keep increasing it until the messages go away. Note that doing this reserves memory for open objects that cannot be reused for other purposes. (A, S)

  • Warning

    OPEN OBJECTS parameter may be too low; attempt was made to free up descriptors in localdes(). Run sp_configure to increase parameter value.

Look, But Don't Touch Options

All the options listed in this section enable you to look at information. You should never modify the sort order ID or the Unicode information. The only successful way to change these is to reinstall SQL Server. Likewise, there is nothing to be gained from modifying the User Connections option. Following are the "look, but don't touch" options:

  • Default SortOrder ID, Unicode Comparison Style, Unicode Locale ID. These options show you the sort order ID, Unicode comparison style, and Unicode locale that you chose when you installed SQL Server. These options are numbers; to find out what they mean, issue the following command:

  • sp_helpsort
  • User Connections. In previous versions of SQL Server, it was necessary to specify the maximum number of concurrent connections to SQL Server. In this release, SQL Server manages the number of connections dynamically and there is no reason to specify a value for this option. If you set it too low, users may be denied access to SQL Server. (A, S)

  • General Tip

    Don't confuse connection with user. Each user may have many simultaneous connections to the server. You can't use the User Connections option to limit concurrent users.

Options That Affect Applications

Some of the SQL Server options can change the behavior of applications. The following list identifies these options:

  • Two Digit Year Cutoff. Since time immemorial, SQL Server has observed the rule that, if dates are entered without a century, values less than 50 are assumed to be in 20xx and values greater than 50 are assumed to be 19xx. Therefore, 1/14/44 is interpreted as 1/14/2044, and 1/14/56 is interpreted as 1956. However, the OLE automation interface, including the capability to maintain data in Enterprise Manager (and ADO as well), has a cutoff of 30. With this rule, 1/14/44 becomes 1/14/1944. If at the time you are reading this you still have any applications that are not providing the century as part of the date, you probably have bigger things to worry about than this option. Your best option is to leave it alone and make sure the century is always supplied.

  • Danger

    It is very easy to enter incorrect date data in Enterprise Manager's Open Table, Return All Rows dialog box because the dates display in mm/dd/yy format without century. Enterprise Manager, being a graphical tool going through OLE automation, uses 30 as the pivot point.

  • Nested Triggers. When you are working with triggers, you can set up your applications so that a trigger on Table A performs some operation on Table B. With nested triggers, if there is a trigger on Table B, the trigger fires when Table A's trigger modifies Table B. This makes triggers much smaller and much more modular. It is unusual, although not impossible, for there to be situations in which this behavior is undesirable. In such a case, you turn nested triggers off. In most cases, you do not want to do so. Nested triggers has been SQL Server's default since 4.2; be very careful changing this if you have legacy applications that have been upgraded. This is a serverwide option. Don't confuse it with "recursive triggers," which are requested at a database level. (S, D)

  • User Options. Many session-level (connection-level) settings control the behavior of applications. These options are specified with the SET statement. Some of these can be specified at the server level by using the appropriate bit masks for the User Options configuration option. The following options can be set at the server level.

  • SET Option

    Bit Mask

























    - - See the discussion of the SET statement in your SQL Server documentation for the meaning of these settings. If you are not comfortable working with bit masks, you can set these on or off through the Enterprise Manager graphical user interface. (S, D)

Changing Configuration Options with Transact-SQL

To change a configuration option in Transact-SQL, use the following command:

sp_configure [option [, value]

If you just issue the command sp_configure in Query Analyzer, a report appears showing all the configuration options available together with their minimum, maximum, current, and next values. This report looks like the one shown in here.






Affinity Mask





Allow Updates





Cost Threshold for Parallelism





Cursor Threshold





Default Language





Default SortOrder ID





Extended Memory Size (MB)





The Name column in this report is the name of the particular option, and what you use if you want to change that option. The column headed config_value is the value that will be used the next time SQL Server is started. The column headed run_value is the value SQL Server is currently using. A minimum and maximum of 0 and 1, respectively, indicates that it is a true-false option. If there is a minimum and maximum, but the run_value is 0, it's most likely one of the options that SQL Server manages dynamically.

If you issue the sp_configure command with just the name of an option, you will see the line of the preceding report for only that option. For example:

sp_configure 'allow updates'

This shows you the settings for just the Allow Updates option. You don't need to spell the option name out in full; any unique left substring works. Therefore, for Allow Updates, you can use 'al', 'allow', and so on.

To change the value of an option, add a value to the command such as this:

sp_configure 'allow',

After you have done this, you must use the reconfigure (or, in some cases, the reconfigure with override) command to make the option take effect. If the option is dynamic, the change is made immediately. If it's a static option, you must stop the server and restart it before the change takes effect.

Changing Configuration Options with Enterprise Manager

Not all configuration options can be changed in Enterprise Manager. To change options using Enterprise Manager, right-click the server in the console pane and choose Properties. The SQL Server Properties dialog box displays, which includes several various tabs and options. Two screens in this dialog box can be confusing if you've never worked in them before. The first is the Memory Configuration screen shown in Figure 3.2.

Figure 3.2 Memory Configuration screen.

The check box labeled Reserve Physical Memory for SQL Server is the same as the Set Working Set Size option discussed previously.

Another confusing screen is the Connection Options screen shown in Figure 3.3.

Figure 3.3 Connection Options screen.

The check boxes for the Default Connection Options correspond to the bit masks for the User Options configuration option described previously.

In Enterprise Manager, the Apply and OK buttons cause the dynamic options to take effect. In addition, Enterprise Manager asks you whether you want to stop and restart the server when you modify a static option. If you are ready to stop the server, choose Yes. If you want to wait until a later time to stop the server (remembering that the option won't take effect until you do so), choose No.

Changing Configuration Options with DMO

The SQL Server object has a configuration object, which contains the ConfigValues collection. Each ConfigValue object has the following useful properties.














True if it's a dynamic option; false if it's a static option

To change a configuration option, you set the value for the appropriate option (using the names specified) as follows:

Set oConfigValue = oSQLServer.Configuration.ConfigValues("Show Advanced _Options")oConfigValue.CurrentValue =

When you are ready to apply the changes, you use the ReconfigureCurrentValues method of the configuration object. If the configuration option requires with override as does the Allow Updates option, use the ReconfigureWithOverride method instead. If any of the changes are to static options, you must use the Stop and Start methods of the SQL Server object to make the changes take effect.

Behind the Scenes

Information about configuration option settings is contained in two different tables, sysconfigures and syscurconfigs:

  • sysconfigures. This table contains the options SQL Server started with as well as any changes made to dynamic options since the server was started.

  • syscurconfigs. This table contains values used the next time SQL Server is started. Note that syscurconfigs is a dynamic table built only when it is referenced.

  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020