Configuring and Tuning Microsoft SQL Server

Updated : July 19, 2001

On This Page

Running and Modifying SQL Server Setup
Configuring SQL Server with Enterprise Manager
Configuring SQL Server with Stored Procedures
Troubleshooting Configuration Problems

Microsoft SQL Server 2000 is designed to balance the workload dynamically and to self-tune configuration settings. For example, SQL Server can dynamically increase or decrease memory usage based on overall system memory requirements.

SQL Server also manages memory efficiently, especially when it comes to queries and user connections—and memory is just one of dozens of areas where the configuration is automatically adjusted.

While self-tuning works in most cases, there are times when you'll need to configure SQL Server settings manually. For example, if you're running a large database with special constraints and the database isn't performing the way you expect it to, you may need to customize the configuration. You may also need to modify configuration settings for SQL Server accounts, authentication, and auditing.

Key tools you'll use to modify configuration settings are

  • SQL Server Setup Allows you to create new instances of SQL Server, add components, rebuild the SQL Server registry, uninstall SQL Server, and more.

  • SQL Server Enterprise Manager Provides an easy-to-use interface that updates the database and registry settings for you.

  • Stored Procedures Lets you view and manage configuration settings through stored procedures, such as sp_configure and xp_regwrite. Note that you can change some options of sp_configure only when Show Advanced Options is set to 1, as in the following example:

exec sp_configure 'show advanced options', 1
  • SQLSERVR.EXE Starts SQL Server from the command line, and you can use it to set configuration parameters at startup.

In this chapter I use a task-oriented approach to explain how you use these tools to configure SQL Server.

Running and Modifying SQL Server Setup

SQL Server Setup is the utility you use to perform key installation tasks for SQL Server. Using SQL Server Setup, you can

  • Create new instances of SQL Server

  • Add or remove components

  • Rebuild the SQL Server registry

  • Uninstall SQL Server

  • Create an unattended installation file for SQL Server installations

Creating New Instances of SQL Server

You can install multiple instances of the SQL Server 2000 database engine on a single computer. Running multiple instances of the database engine is ideal when

  • You need to support multiple test and development environments on a single large server.

  • You need to run multiple applications on a desktop and each application installs its own instance of the SQL Server 2000 desktop engine.

  • You need to securely isolate the databases that are available on a single server.

In most other situations, however, you should not run multiple instances of the SQL Server 2000 database engine. Each instance of the SQL Server 2000 database engine has its own set of system and user databases. Each instance also has separate SQL Server and SQL Server Agent services. All other components and services are shared, and this adds to the overhead on the server.

Understanding SQL Server Instances

When you install SQL Server 2000, you have the option of installing a default instance of the SQL Server 2000 database engine or a named instance of the SQL Server 2000 database engine. In most cases, you'll want to install the default instance first and then install additional named instances of the SQL Server database engine as necessary. There is no limit to the number of named instances that you can run on a single computer.

A default instance is identified by the name of the computer on which the SQL Server 2000 database engine is running and doesn't have a separate instance name. Applications connect to the default instance by using the computer name in their requests. Only one default instance can run on any computer, and this default instance can be any version of SQL Server.

All instances of SQL Server other than the default instance are identified by the instance name that you set during installation. Applications connect to a named instance by specifying the computer name and the instance name in the format computer_name\instance_name. Only the SQL Server 2000 database engine can run as a named instance. Previous versions of SQL Server do not support named instances.

Note: When you run SQL Server 2000 Enterprise Edition, you can create server clusters that link up to four servers together in a node. Applications connect to the default instance on a SQL Server cluster by specifying the virtual server name. Applications connect to a named instance on a SQL Server cluster by specifying the virtual server name and the named instance in the format virtual_server_name\instance_name.

Installing a SQL Server Instance

To install an instance of the SQL Server 2000 database engine, complete the following steps:

  1. Log on to the server using an account with administrator privileges. Then insert the SQL Server 2000 CD-ROM into the CD-ROM drive.

    Tip Be sure to keep a detailed record of the actions you perform in steps 6-9. These actions should explicitly state the server, server instance, and installation options you're using. You'll need this record if you ever need to rebuild the SQL Server registry settings for the server instance.

  2. If Autorun is enabled, the SQL Server 2000 setup program should start automatically. Otherwise, you'll need to double-click AUTORUN.EXE in the root directory of the CD-ROM.

  3. Click SQL Server 2000 Components and then select Install Database Server. This starts the Microsoft SQL Server Installation Wizard. Click Next.

  4. You can install SQL Server 2000 on local or remote computers. Select Local Computer or Remote Computer as appropriate. Click Next.

    Note: You can also install SQL Server 2000 as part of a cluster. If a cluster is detected, the Virtual Server option is selected by default.

  5. Select Create A New Instance Of SQL Server and then click Next. Afterward, complete the user information and license agreement windows.

  6. Select Server And Client Tools and then click Next. As shown in Figure 2-1, you must now determine the instance type to install. To install a default instance of SQL Server, select Default and then click Next. Otherwise, clear the Default check box and then type the instance name in the field provided. The instance name can be up to 16 characters in length and must follow the naming rules for nondelimited identifiers.

    Note: You can install only one default instance on a computer. If a default instance already exists, you can't select the Default check box.

    Cc917616.ppc0201(en-us,TechNet.10).gif

    Figure 2-1: Use the options to select the instance type as either default or named.

  7. In the Setup Type dialog box, select the installation type as Typical, Minimum, or Custom. With a custom installation, you'll be able to select subcomponents and networking libraries. You'll also be able to set the default character set and collation.

    Caution: When you install SQL Server using the Typical or Minimal installation type, the default character set and collation are set. You can't change these settings on an existing SQL Server installation without rebuilding the master database. Rebuilding the master database detaches all other databases on the server, making them unusable. For more information, see the section of this chapter entitled "Changing Collation and Rebuilding the Master Database."

  8. In the Services Accounts dialog box, determine how the SQL Server and SQL Server Agent services will run (see Figure 2-2). To use the same account information and autostart each service, select Use The Same Account For Each Service and then set the options using the Service Settings panel. Otherwise, choose Customize The Settings For Each Service and then set options for SQL Server and SQL Server Agent using the options of the Services and Service Settings panels.

    Tip The SQL Server and SQL Server Agent services can run under the Local System account or a Domain User account. If the server requires resources on the local server only, use the Local System account. Otherwise, use a domain account.

    Cc917616.ppc0202(en-us,TechNet.10).gif

    Figure 2-2: Use the Services Accounts dialog box to determine how the SQL Server and SQL Server Agent services will run.

  9. Use the Authentication Mode dialog box to configure the authentication settings. The SQL Server instance can run under Microsoft Windows authentication or mixed mode authentication. With Windows authentication, you use only domain accounts to authenticate connections to the SQL Server instance. With mixed mode authentication, users can access the SQL Server instance using domain accounts or SQL Server IDs.

  10. Click Next, choose an appropriate licensing mode, and then click Continue to begin the installation process. Anytime there are tasks using files that setup needs to install, you'll see a prompt. The prompt tells you which tasks you need to shut down to avoid having to reboot after installation. Stop tasks as necessary. If you stop services, be sure to note dependent services that are stopped as well. You'll need to restart these services later. Click Next and then click Finish to complete the process.

  11. Register the instance of the SQL Server database engine as discussed in the section of Chapter 3 entitled "Managing Servers." You can then manage the server instance in Enterprise Manager.

Adding Components

SQL Server keeps track of those components you've installed and those you haven't. If you ever want to add components, you can do so by completing the following steps:

  1. Log on to the server using an account with administrator privileges. Then insert the SQL Server 2000 CD-ROM into the CD-ROM drive.

  2. If Autorun is enabled, the SQL Server 2000 setup program should start automatically. Otherwise, you'll need to double-click AUTORUN.EXE in the root directory of the CD-ROM.

  3. Click SQL Server 2000 Components and then select Install Database Server. This starts the Microsoft SQL Server Installation Wizard. Click Next.

  4. Select Local Computer, Remote Computer, or Virtual Server as appropriate. Click Next.

  5. Select Upgrade, Remove, Or Add Components and then click Next.

  6. Use the Instance Name dialog box to select the instance you want to work with. If necessary, clear the Default check box so that you can use the Instance Name selection list. Click Next.

  7. Select Add Components To Your Existing Installation and then click Next. You can now add components to the installation.

The installation program detects currently installed components and then allows you to select additional components. Components you might want to add through the installation program include

  • Server components SQL Server, upgrade tools, replication support, full-text search, debug symbols, and performance counters

  • Management tools Enterprise Manager, Query Analyzer, Profiler, DTC Client Support, and Conflict Viewer

  • Books online Help documentation, installed on the system's hard disk drive or accessed from a CD-ROM, or both

  • Development toolsHeaders and libraries, Backup/Restore API, and Debugger Interface

Note: As the installation dialog box says, selecting components already installed doesn't reinstall them and canceling components already installed doesn't remove them.

The SQL Server CD-ROM provides other tools that aren't part of the standard installation. You access these tools on the initial installation screen and they include

  • Analysis Services Used with online analytical processing applications and data warehousing

  • English Query Used with applications that allow plain-English queries of the SQL database

Restoring the SQL Server Registry

Each instance of the SQL Server 2000 database engine running on the computer has settings that are stored in the Windows registry. If these settings are corrupted or accidentally deleted, you can restore them by using the SQL Server Setup program. Setup rebuilds the registry settings based on information you provide and won't copy files. You must know how you installed the corrupted instance of SQL Server. If you don't remember how you installed the SQL Server instance, you'll need to uninstall and then reinstall SQL Server.

Note: The registry settings are only for the specific instance of the SQL Server 2000 database engine you identify. The restore process doesn't restore registry settings for other applications and services.

To rebuild the SQL Server registry settings, follow these steps:

  1. Log on to the server using an account with administrator privileges. Then insert the SQL Server 2000 CD-ROM into the CD-ROM drive.

  2. If Autorun is enabled, the SQL Server 2000 setup program should start automatically. Otherwise, you'll need to double-click AUTORUN.EXE in the root directory of the CD-ROM.

  3. Click SQL Server 2000 Components and then select Install Database Server. This starts the Microsoft SQL Server Installation Wizard. Click Next.

  4. Select Local Computer, Remote Computer, or Virtual Server as appropriate. Click Next.

  5. Select Advanced Options and then click Next. Afterward, select Rebuild Registry and then click Next again.

  6. Proceed through the installation process, choosing the same options you previously selected for the instance of the SQL Server database engine. When you finish, Setup will begin the restoration process. If the rebuild is successful, you'll see a prompt indicating this. If the rebuild is unsuccessful, you'll see a prompt notifying you that you need to uninstall and then reinstall SQL Server for this particular database instance.

Uninstalling SQL Server

You use the SQL Server Setup program to uninstall SQL Server 2000. You can uninstall each instance of the SQL Server database engine separately.

To uninstall an instance of SQL Server, complete these steps:

  1. Log on to the server using an account with administrator privileges. Then insert the SQL Server 2000 CD-ROM into the CD-ROM drive.

  2. If Autorun is enabled, the SQL Server 2000 setup program should start automatically. Otherwise, you'll need to double-click AUTORUN.EXE in the root directory of the CD-ROM.

  3. Click SQL Server 2000 Components and then select Install Database Server. This starts the Microsoft SQL Server Installation Wizard. Click Next.

  4. Select Local Computer, Remote Computer, or Virtual Server as appropriate. Click Next.

  5. Select Upgrade, Remove, Or Add Components and then click Next.

  6. Use the Instance Name dialog box to select the instance you want to remove and then click Next.

  7. Select Uninstall Your Existing Installation and then click Next. The installation wizard removes the SQL Server instance.

  8. Click Next and then click Finish to complete the process.

Configuring SQL Server with Enterprise Manager

Enterprise Manager provides the easiest way to configure SQL Server. In Enterprise Manager you access the Properties dialog box of a registered server and then use the tabs and options provided to configure the server. Behind the scenes, SQL Server executes commands that modify server settings. If the changes affect the master database or other databases, the stored procedure sp_configure is used to make changes in the sysconfigures system table. If the changes affect the Microsoft Windows Registry, the stored procedure xp_regwrite is used to update the Registry. SQL Server also uses stored procedures to read configuration settings. For example, the stored procedure xp_regread is used to read most registry settings.

Once you register a server in Enterprise Manager, you can connect to it and manage its configuration with the SQL Server Properties dialog box. To access this dialog box, complete the following steps:

  1. Select Start, choose Programs, choose Microsoft SQL Server, and then choose Enterprise Manager.

  2. Access the server group containing the server you want to work with. Right-click the server name in the Enterprise Manager tree view and choose Properties from the shortcut menu. This opens the dialog box shown in Figure 2-3.

  3. You can now configure common SQL Server settings. For more advanced settings, you'll need to use a stored procedure, such as sp_configure.

The SQL Server Properties dialog box has many tabs. The sections that follow discuss each of them. To obtain a summary of current settings in SQL Server Query Analyzer, run the following command:

exec sp_configure

Cc917616.ppc0203(en-us,TechNet.10).gif

Figure 2-3: The SQL Server Properties dialog box allows you to configure SQL Server without having to use stored procedures like sp_configure . Pointing and clicking is easy, so it's definitely recommended.

Determining System and Server Information

General system and server information is available on the General tab of the SQL Server Properties dialog box (see Figure 2-3). The information provided by the General tab helps you determine the following:

  • SQL Server edition: Standard, Enterprise, or Personal

  • Operating system version

  • SQL Server version

  • Default language

  • Platform and chip architecture

  • Amount of RAM installed on the system

  • Number of CPUs

  • SQL Server root directory location

  • Default server collation

Using the extended stored procedure xp_msver, you can obtain similar information. Execute the following command:

exec xp_msver 'ProductName', 'ProductVersion', 'Language', 'Platform', 'WindowsVersion', 
'PhysicalMemory', 'ProcessorCount'

Tip You can use the SQL Query Analyzer to execute the command shown. Basic techniques for using this utility are covered in the section of this chapter entitled "Configuring SQL Server with Stored Procedures."

Configuring Startup

SQL Server provides many ways to configure server startup. One of them is through the General tab of the SQL Server Properties dialog box. Use the check boxes in the Autostart Policies When The Operating System Starts area. These check boxes determine whether the related services start automatically when the operating system starts. You can use these check boxes as follows:

  • Configuring SQL Server startup To automatically start SQL Server (MSSQLServer service) when the system boots, select the Autostart SQL Server check box. Otherwise, clear the check box.

  • Configuring SQL Server Agent startup To automatically start SQL Server Agent (SQLServerAgent service) when the system boots, select the Autostart SQL Server Agent check box. Otherwise, clear the check box.

    Note: The MSSQLServer and SQLServerAgent services are used with the default server instance only. When you're working with a named instance of SQL Server, these services are named MSSQL$instancename and SQLAgent$instancename.

  • Configuring MSDTC startup To automatically start MSDTC (MSDTC service) when the system boots, select the Autostart MSDTC check box. Otherwise, clear the check box.

Enterprise Manager uses the stored procedure xp_regwrite to modify the service startup settings. These settings affect the startup values registry keys.

Caution: Don't write directly to the Registry unless it's absolutely necessary. Use the Properties dialog box instead—that's what it's for.

Setting Startup Parameters

Startup parameters control how SQL Server starts and what options are set when it does. The easiest way to configure startup options is through the General tab of the SQL Server Properties dialog box. Click Startup Parameters to display the dialog box shown in Figure 2-4. You can now add and remove startup parameters.

Tip Startup parameters can be passed to the command-line utility SQLSERVR.EXE as well. Passing this utility the –c option starts SQL Server without using a service. You must run SQLSERVR.EXE from the Binn directory that corresponds to the instance of the SQL Server database engine that you want to start. For the default instance, the utility is located in mssql\Binn. For named instances, the utility is located in mssql$instancename\Binn.

Adding Startup Parameters

You can add startup options by completing the following steps:

  1. Type a parameter in the Parameter field.

  2. Choose Add.

  3. Click OK to apply the changes.

    Cc917616.ppc0204(en-us,TechNet.10).gif

    Figure 2-4: Configure startup parameters for SQL Server by adding or removing options. Don't delete the default options (-d, -e, and -l) or you may have problems.

Removing Startup Parameters

You can remove startup parameters by completing the following steps:

  1. Select the parameter, and then choose Remove.

  2. Click OK to apply the changes.

Common Startup Parameters

Table 2-1 shows startup parameters and how they're used. The first three parameters (-d, -e, and -l) are the defaults for SQL Server. The remaining parameters allow you to configure additional settings.

Table 2-1 Startup Parameters for SQL Server

Parameter

Description

Example

-d<path>

Sets the full path for the master database.

-dC:\MSSQL\data\master.mdf

-e<path>

Sets the full path for the error log.

-eD:\LOGS\ERRORLOG

-l<path>

Sets the full path for the master database transaction log.

-lD:\LOGS\mastlog.ldf

-B

Sets a breakpoint on error; used with the -y option when debugging

 

-c

Prevents SQL Server from running as a service. This makes startup faster when you're running SQL Server from the command line.

 

-f

Starts SQL Server with minimal configuration. Enables the sp_ configure Allow Updates option, which is disabled by default.

 

-g number

Specifies the amount of memory in MB to reserve for SQL Server.

-g 192

-m

Starts SQL Server in single-user mode. Only a single user can connect and the checkpoint process isn't started. -n Tells SQL Server not to log errors in the application event log.

 

-p<level>

Sets the precision level for numeric and decimal data types. Default is 38 digits but the range is 1–38. Maximum precision (38) is assumed if you use the switch without setting a level.

-p38

-s<altreg>

Sets an alternative Registry key for SQL Server. This is used during startup instead of the default key.

-sHKEY_LOCAL_MACHINE SYSTEM\CurrentControlSet\ Services\MSSQLServerMod

-T<tnum>

Sets a trace flag. Trace flags set nonstandard behavior and are often used in debugging or diagnosing performance issues.

-T237

-t<tnum>

Sets an internal trace flag for SQL Server. Used only by SQL Server support engineers.

-t8837

-x

Disables statistics tracking for CPU time and cache-hit ratio. Allows maximum performance.

 

-y number

Sets an error number that causes SQL Server to dump the stack.

-y 1803

Setting the Startup Service Account

SQL Server inherits rights and permissions from the startup service account. These permissions are used whenever SQL Server performs tasks on the local system or across the network. As discussed in the section of Chapter 1 entitled "Service Accounts," you can use two types of accounts: local system accounts and domain accounts. If SQL Server performs only local operations, use the local system account. Otherwise, use a properly configured domain account.

Note: If you plan on using SQL mail or SQLAgent mail, it's a good idea to make sure that the domain user account you set to run the SQL Server and SQL Agent services is a member of the local administrators group and that you assign both services to the same account. If you change the account after you've installed SQL Server, you'll have to make several changes to security settings to restart the services.

Using the Local System Account

Specify the local system account for SQL Server startup by completing the following steps:

  1. Access the Security tab of the SQL Server Properties dialog box.

  2. Use the local system account by choosing the System Account option button in the Startup Service Account area.

  3. Click OK. Now the server can only access local resources.

Using a Domain Account

Specify a domain account for SQL Server startup by completing the following steps:

  1. Set appropriate privileges for the SQL Server account and make sure the user account has the "log on as a service" local security policy setting enabled.

  2. From the SQL Server Properties dialog box, go to the Security tab.

  3. Choose the This Account option button. Then type the designated account name and password. If necessary, specify the domain as part of the account name, such as cordata\wrstane, where cordata is the domain name and wrstane is the account name.

  4. Click OK.

Authentication and Auditing

You configure authentication and auditing options with the Security tab of the SQL Server Properties dialog box. This tab is shown in Figure 2-5.

Setting Authentication Mode

As discussed in the section of Chapter 1 entitled "Authentication Enhancements," SQL Server can use combined domain and SQL Server authentication or domain authentication only. To use combined authentication, select the SQL Server And Windows option button. Now users in Windows domains can access the server using a domain account and other users can be logged on using a SQL Server logon ID.

To use domain authentication only, select the Windows Only option button. Now only users with a domain account can access the server.

Cc917616.ppc0205(en-us,TechNet.10).gif

Figure 2-5: Configure authentication and auditing with the Security tab's options.

Tip With combined authentication, SQL Server first checks to see if a new logon is a SQL Server logon. If the logon exists, SQL Server then uses the password provided to authenticate the user. If the logon doesn't exist, it uses domain authentication. Note also that domain authentication isn't available on SQL Server Desktop running on Windows 95 or Windows 98.

Setting Auditing Level

Auditing allows you to track user access to SQL Server. You can use auditing with both authentication modes as well as with trusted and untrusted connections.

When auditing is enabled, user logons are recorded in the Windows application log, the SQL Server error log, or both, depending on how you configure logging for SQL Server. The available auditing options are

  • None Disables auditing (the default setting)

  • Success Audits successful logon attempts

  • Failure Audits failed logon attempts

  • All Audits both successful and failed logon attempts

Tuning Memory Usage

SQL Server is designed to manage memory needs dynamically, and it does an excellent job in most cases. Using dynamic memory allocation, SQL Server can add memory to handle incoming queries, free up memory for another application you're starting, or reserve memory for possible needs. The default memory settings are the following:

  • Dynamically configure SQL Server memory

  • Minimum memory allocation of 0 MB

  • Maximum memory allocation is set to the total amount of RAM on the system

  • No memory is reserved specifically for SQL Server

  • Minimum memory for query execution of 1024 KB

You can change these settings if you like, but you need to be very careful about allocating too little or too much memory to SQL Server. Too little memory and SQL Server has to throttle back and may not handle tasks in a timely manner. Too much memory and SQL Server may take essential resources away from other applications like the operating system, which may result in excessive paging and a drain on overall system performance.

Tip Statistics that can help you better allocate memory are the number of page faults per second and the cache-hit ratio. Page faults per second helps track paging to and from virtual memory. Cache-hit ratio helps track whether data being retrieved is in memory. You'll learn more about this in Chapter 10, "Profiling and Monitoring Microsoft SQL Server."

The following sections examine key areas of memory management. The primary way to configure memory usage is to use the Memory tab of the SQL Server Properties dialog box, shown in Figure 2-6. I'll also show you a better way to configure Windows memory usage for SQL Server.

Cc917616.ppc0206(en-us,TechNet.10).gif

Figure 2-6: You can use the Memory tab to dynamically or manually configure memory usage, but keep in mind that you shouldn't change these settings unless you're experiencing performance problems.

Working with Dynamically Configured Memory

With dynamically configured memory, SQL Server configures memory usage automatically, based on workload and available resources. Total memory usage varies between the minimum and maximum values you set. To use dynamically configured memory, complete the following steps:

  1. Select the Dynamically Configure SQL Server Memory option button in the Memory tab.

  2. If desired, set minimum and maximum memory usage values with the Minimum and Maximum sliders, respectively. It's recommended that you set the Maximum value at or near total RAM for stand-alone servers.

  3. Click OK.

You can use the stored procedure sp_configure to change the minimum and maximum settings. Use the following Transact-SQL commands:

exec sp_configure 'min server memory', <number of megabytes> 
exec sp_configure 'max server memory', <number of megabytes>

Best Practice With dynamically configured memory, you usually don't need to set minimum and maximum memory usage values. On a dedicated system running only SQL Server, however, you may achieve smoother operation by setting minimum memory to 4 MB + (24 KB * NumUsers), where NumUsers is the average number of users simultaneously connected to the server. You may also want to reserve physical memory for SQL Server. SQL Server uses about 3.5 MB for its code and internal structures. Additional memory is used as follows: 96 bytes for locks, 2880 bytes for open databases, and 276 bytes for open objects, which include all tables, views, stored procedures, extended stored procedures, triggers, rules, constraints, and defaults. To simplify tracking additional memory, I just add 500 KB. This gives a total of 4 MB.

Using Fixed Memory

If you want to override the dynamic memory management features, you can do this by setting a fixed memory size for SQL Server. Complete the following steps:

  1. From the SQL Server Properties dialog box, go to the Memory tab, and select Use A Fixed Memory Size.

  2. Adjust memory setting to the desired value using the slide control.

  3. Click OK.

Caution: Setting fixed memory incorrectly can cause serious performance problems on SQL Server. Use fixed memory only in circumstances when you need to ensure that an exact amount of memory is available for SQL Server.

Reserving Physical Memory

You can also reserve memory specifically for SQL Server. When you reserve physical memory for SQL Server, the operating system doesn't swap out SQL Server memory pages even if that memory could be allocated to other processes when SQL Server is idle. On a dedicated system, reserving memory can improve SQL Server performance by cutting down on paging and cache hits.

To reserve physical memory for SQL Server, complete the following steps:

  1. From the SQL Server Properties dialog box, go to the Memory tab and select Reserve Physical Memory For SQL Server.

  2. Click OK.

You can also use the stored procedure sp_configure to reserve physical memory. The Transact-SQL command you would use is

exec sp_configure 'set working set size', 1

Allocating Memory for Queries

By default, SQL Server allocates a minimum of 1024 KB of memory for query execution. This memory allocation is guaranteed per user, and you can set it anywhere from 512 KB to 2 GB. If you increase the minimum query size, you can improve the performance of queries that perform processor-intensive operations, such as sorting or hashing. If you set the value too high, however, you can degrade the overall system performance. Because of this, adjust the minimum query size only when you're having trouble executing queries quickly.

Best Practice The default setting of 1024 KB of RAM works in most cases. However, you may want to consider changing this value if the server operates in an extremely busy environment, with lots of simultaneous queries running in separate user connections, or in a relatively slow environment, with few (but large or complex) queries. In this case, four factors should determine your decision to adjust the minimum query size: the total amount of free memory (when the system is idle and SQL Server is running), the average number of simultaneous queries running in separate user connections, the average query size, and the query response time you hope to achieve. There's often a trade-off to be made with these values. You can't always get an instant response, but you can optimize performance based on available resources.

Use the following equation to get a starting point for the optimization:

FreeMemory / (AvgQuerySize * AvgNumSimulQueries).

For example, if the system has 200 MB of free memory, the average query size is 2 MB, and the average number of simultaneous queries is five, the optimal value for query size is 200 MB / (2*5) or 20 MB. Generally, this value represents the maximum you should assign given the current environment, and you'll want to lower this value.

To allocate memory for queries, complete the following steps:

  1. From the SQL Server Properties dialog box, go to the Memory tab and set a value for the Minimum Query Memory box. This value is set in kilobytes.

  2. Click OK.

You can also use the stored procedure sp_configure to set the minimum query size. The related command is

exec sp_configure 'min memory per query', <number of kilobytes>

Configuring Processors and Parallel Processing

Systems that use multiprocessors can take advantage of SQL Server's enhancements for parallel and symmetric multiprocessing. You can control how and when processors are used by SQL Server as well as when queries are processed in parallel. On Windows 2000, standard editions of SQL Server support up to four processors. Enterprise Editions support up to 32 processors (8 with Advanced Server and 32 on Data Center Server).

Optimizing CPU Usage

Processor settings are automatically configured and optimized when you install SQL Server. Don't change these settings without careful forethought. That said, you can manually configure processor usage by completing the following steps:

  1. Select Start, choose Programs, choose Microsoft SQL Server, and then choose Enterprise Manager.

  2. Right-click the server name in the Enterprise Manager tree view and choose Properties from the shortcut menu.

  3. From the SQL Server Properties dialog box, go to the Processor tab, as shown in Figure 2-7.

    Cc917616.ppc0207(en-us,TechNet.10).gif

    Figure 2-7: Use the Processor tab to control which processors are used by SQL Server and when parallel processing is used. Read the notes before making changes.

  4. Use the Processor list to determine which processors SQL Server uses. Select the check box for processors you want to use and clear the check box for processors you don't want to use. The first CPU on the system is identified as CPU 0, the second as CPU 1, and so on.

    Best Practice If the system has more processors than SQL Server supports, SQL Server doesn't use all of them. For example, on an eight-way symmetric multiprocessing (SMP) system, SQL Server Standard can use only four processors. This leaves four processors for other applications and system-level tasks.

    While you may be tempted to assign SQL Server to the higher numbered processors (5, 6, 7, and 8), this isn't a good idea. Windows assigns deferred process calls associated with network interface cards (NICs) to the highest numbered processors. Here, if the system had two NICs, these calls would be directed to CPU 8 and CPU 7. Definitely consult with the equipment manufacturer before changing these values.

  5. Click OK.

  6. These settings take effect when the server is stopped and restarted.

You can also use the stored procedure sp_configure to set the affinity mask. The related command is

exec sp_configure 'affinity mask', <integer value>

SQL Server interprets the integer value as a bit mask representing the processors you want to use. In this bit mask, CPU 0 is represented by bit 0, CPU 1 with bit 1, and so on. A bit value of 1 tells SQL Server to use the CPU. A bit value of 0 tells SQL Server not to use the CPU. For example, if you wanted to turn on support for processors 1, 2, and 5, you would have a binary value of

000100110

The corresponding integer value is 76:

64 + 8 + 4 = 76

Setting Parallel Processing

A lot of calculations go into determining when parallel processing is and isn't used. Generally, SQL Server processes queries in parallel

  • When the number of CPUs is greater than the number of active connections.

  • When the estimated cost for the serial execution of a query is higher than the query plan threshold. (The estimated cost refers to the elapsed time in seconds required to execute the query serially.)

That said, certain types of statements can't be processed in parallel unless they contain clauses. For example, UPDATE*,* INSERT, and DELETE aren't normally processed in parallel even if the related query meets the criteria. However, if the UPDATE or DELETE statements contain a where clause or an INSERT statement contains a select clause, the where and select can be executed in parallel. Changes are then serially applied to the database.

You can configure parallel processing by completing the following steps:

  1. From the SQL Server Properties dialog box, go to the Processor tab.

  2. To use all available processors for parallel processing (the maximum supported by SQL Server), select the Use All Available Processors option button.

  3. To use a set amount of processors for parallel processing (up to the maximum supported by SQL Server), select the Use N Processor(s) option button and then select the number of processors to use. A value of 1 tells SQL Server not to use parallel processing.

  4. Set a cost estimate in the Minimum Query Plan Threshold field. You can use any value from 0 to 32,767. On a single CPU the cost threshold is ignored.

  5. Click OK. These changes take effect immediately. You don't need to restart the server.

You can use the stored procedure sp_configure to configure parallel processing. The Transact-SQL commands are

exec sp_configure 'max degree of parallelism', <integer value> 
exec sp_configure 'cost threshold for parallelism', 
   <integer value>

Note: Transact-SQL is an enhanced version of the standard structured query language (SQL) that is used by SQL Server. You may also see Transact-SQL referenced as T-SQL.

Configuring Threading, Priority, and Fibers

Threads are a very important part of a multitasking operating system and enable SQL Server to do many things at once. Threads aren't processes, however. They're concurrent execution paths that allow applications to use the CPU more effectively.

SQL Server tries to match threads to user connections. When the number of threads that are available is greater than the number of user connections, there is at least a one-to-one ratio of threads to user connections, which allows each user connection to be handled uniquely. When the number of threads available is less than the number of user connections, SQL Server must pool threads; as a result, the same thread may serve multiple user connections, which can reduce performance and response time if additional resources are available and aren't being used.

Normally, the operating system handles threads in kernel mode but handles applications and user-related tasks in user mode. Switching between modes, such as when the kernel needs to handle a new thread, requires CPU cycles and resources. To allow the application to handle threading directly, you can use fibers. Switching fibers doesn't require changing modes and therefore can sometimes improve performance. Another way to improve performance is to increase the priority of SQL Server threads. Normally, threads have a priority of 1 to 31, and higher priority threads get more CPU time than lower priority threads. Higher priority threads can also preempt lower priority threads, forcing threads to wait until higher priority threads finish executing. By increasing thread priority, you can give the threads a higher preference for CPU time and ensure that other threads don't preempt them.

Note: The complete range for thread priority is from 0 to 31. Thread priority 0 is reserved for operating system use.

You configure worker threads, fibers, and thread priority using the SQL Server Properties dialog box. Go to the Processor tab and use these options:

  • Maximum Worker Threads Sets the maximum number of threads. By default, the field is set to 255. However, you can use any value from 10 to 32,767 (except on the desktop version). On a busy server with lots of user connections, you may want to increase this value. On a slow server with few connections, you may want to decrease this value. Computers with multiple processors can concurrently execute one thread per CPU.

  • Boost SQL Server Priority On Windows Increases the priority of SQL Server threads. Without boosting, SQL Server threads have a priority of 7 (normal priority). With boosting, SQL Server threads have a priority of 13 (high priority). On a dedicated system running only SQL Server, this option can improve performance. However, if the server runs other applications, the performance of those applications may be degraded.

  • Use Windows NT Fibers Configures SQL Server to use fibers, which it can handle directly. SQL Server still needs threads to carry out tasks. SQL Server allocates one thread per CPU and then allocates one fiber per concurrent user connection up to the Maximum Worker Threads value. You must restart the server for this option to take effect.

Tip Fibers work best when the server has multiple CPUs and a relatively low user to CPU ratio. For example, on an Enterprise installation with 32 CPUs and 250 users, you may see a noticeable performance boost with fibers. But when you have eight CPUs and 5000 users, you may see performance decrease with fibers.

You can use sp_configure to set fibers, maximum worker threads, and priority boost. The commands are

exec sp_configure 'lightweight pooling', <0 or 1> 
exec sp_configure 'max worker threads', <integer value> 
exec sp_configure 'priority boost', <0 or 1>

Configuring User and Remote Connections

Requests for data are handled through user connections to client systems. The client opens a connection to SQL Server, makes a request, and waits for a response from SQL Server. When the client is finished with its request, it closes the connection. Other servers and applications can also connect to SQL Server remotely. To configure client connections and remote server connections, you can use the Connections tab in the SQL Server Properties dialog box.

As you see in Figure 2-8, there are many settings associated with client and server connections. The sections that follow examine these settings.

Cc917616.ppc0208(en-us,TechNet.10).gif

Figure 2-8: The default connection settings seen in the Connections tab are usually sufficient. Note the original settings before you make any changes.

Setting Maximum User Connections

In the Connections tab, the Maximum Concurrent User Connections field allows you to configure the maximum number of user connections that SQL Server allows at any one time. SQL Server allows you to set this value to anywhere from 0 to 32,767. By default, the value is set to zero, which means an unlimited number of connections is allowed. However, the actual number of user connections allowed really depends on hardware, application, and other server limitations. You can determine the number of user connections allowed on your system by executing the following command in SQL Server Query Analyzer:

select @@max_connections

To set the maximum number of user connections, complete the following steps:

  1. From the SQL Server Properties dialog box, go to the Connections tab.

  2. Type a new value in the Maximum Concurrent User Connections field and then click OK.

  3. Stop and restart the server for the changes to take effect.

You can also set the maximum user connections by using the following command:

exec sp_configure 'user connections', <integer value>

Best Practice You shouldn't need to change the maximum connections value. If you do, be careful. When the maximum number of connections is reached, users receive an error message and aren't able to connect until a connection becomes available. The only time you'll need to set this option is in an environment where you have a large number of users and need to limit the number of active connections to ensure that requests for connected users are handled in a timely manner. A better alternative is to add sufficient memory to the system or configure a cluster to balance the workload, or both. For large numbers of users, you should also ensure that SQL applications connect and then disconnect promptly when finished to free resources.

Default Connection Options

On the Connections tab you'll find a list box labeled Default Connection Options. You'll use the list box options to set default query-processing options for user connections. Select an option by selecting its related check box. Cancel an option by clearing its related check box. Any changes you make affect new logons only. Current logons aren't affected. Furthermore, users can override the defaults by using set statements, if necessary.

Table 2-2 provides a summary of the connection options as well as the default state for ODBC (open database connectivity) and OLE DB (object linking and embedding database, which may be different from the SQL Server default). You'll also find a list of commands you can use with sp_configure, the corresponding value for the configuration bit mask, and the SET commands used to override the default settings in a user session.

Table 2-2 Configuring Connection Options

Connection Option

Description

Default State

Bit Mask Value

SET Command

Interim/deferred constraint checking

When on, disables deferred checking of foreign key constraints. This applies stricter referetial integrity checking.

Off

1

DISABLE_DEF_
CNST_CHK

Implicit transactions

When on, uses transactions implicitly whenever statements are executed.

Off

2

IMPLICIT_
TRANSACTIONS

Close cursor on COMMIT

When on, automatically closes a cursor at the end of a transaction.

Off

4

CURSOR_ CLOSE_ ON_COMMIT

ANSI warning

When on, SQL Server displays null, overflow, and divide-by-zero warnings. Otherwise no error or null may be returned.

On

8

ANSI_ WARNINGS

ANSI padding

When on, data in fixed- length fields are padded with trailing spaces to fill out the width of the column.

On

16

ANSI_PADDING

ANSI nulls

Controls how NULL is used with equality operators. When on, comparing anything with NULL gives an unknown result.

On

32

ANSI _NULLS

Arithmetic abort

When on, causes a query to terminate when an overflow or divide- by-zero error occurs.

On

64

ARITHABORT

Arithmetic ignore

Returns NULL when an overflow or divide-by-zero error occurs during a query.

On

128

ARITHIGNORE

Quoted identifier

When on, SQL Server interprets double quotation marks as indicating an identifier rather than as delimiting a string.

On

256

QUOTED_ IDENTIFIER

No count

When on, turns off the display of the number of rows returned in a query.

Off

512

NOCOUNT

ANSI null defined ON

When on, new columns are defined to allow nulls (if you don't explicitly allow or disallow nulls).

Off

1024

ANSI_NULL_ DFLT_ON

ANSI null defined OFF

When on, new columns are defined not to allow nulls (if you don't explicitly allow or disallow nulls).

Off

2048

ANSI_NULL_ DFLT_OFF

For sp_configure, the default options are set with the user options parameter

exec sp_configure 'user options', <integer bit mask value>

Here, the bit mask value is the sum of the numeric values for all the options you want to use. Each option has a corresponding SET command as well. When you make a connection, you can use the SET command to override the default setting for the session. For example, if you wanted to turn on ANSI padding, ANSI nulls, and ANSI warning, you'd use the bit mask value 56, such as

exec sp_configure 'user options', 56

In a user session, you could later turn these options on or off using

set ansi_padding on set ansi_nulls off

Configuring Remote Server Connections

Connections from other servers are handled differently than user connections. You can determine whether servers can connect using remote procedure call (RPC), how long it takes for remote queries to time out, and whether distributed transactions are used. To configure remote connections, complete these steps:

  1. From the Server Properties dialog box, go to the Connections tab.

  2. To allow servers to connect by means of RPC, select Allow Other SQL Servers To Connect Remotely To This SQL Server Using RPC. Remote servers can then log on to the server to execute stored procedures remotely. You must stop and restart the server if you change this option.

    Caution: RPC connections are allowed by default. If you change this behavior, remote servers can't log on to SQL Server. This secures SQL Server from remote server access.

  3. By default, queries executed by remote servers don't time out. To change this behavior, type a time-out value in the Query Time-Out field. Time-out values are set in seconds, and the acceptable range of values is from 0 to 2,147,483,647. A value of 0 means that there is no query time-out for remote server connections.

  4. Stored procedures and queries executed on the server can be handled as distributed transactions by using MS DTC. If you want to execute procedures in this way, select the Enforce Distributed Transactions (MTS) check box. If you change this option, you must stop and restart the server.

  5. Click OK.

These options can also be set with sp_configure and the related Transact-SQL statements are

exec sp_configure 'remote access', <0 or 1> 
exec sp_configure 'remote query timeout', <number of seconds> 
exec sp_configure 'remote proc trans', <0 or 1>

Note: A value of 0 is off and a value of 1 is on.

Server Settings

You use the Server Settings tab of the SQL Server Properties dialog box to configure general server settings. As shown in Figure 2-9, you can set the default language, general server behavior, and more.

Setting Default Language for SQL Server

The default language determines default display formats for dates as well as the names of months and days. All output is in U.S. English unless you're running a localized version of SQL Server. Localized versions of SQL Server are available for French, German, Japanese, and Spanish. On a localized server, there are two sets of system messages, one in U.S. English and one in the local language. If the default language is set to the local language, SQL Server messages are displayed in the local language. Otherwise, they're displayed in U.S. English.

Cc917616.ppc0209(en-us,TechNet.10).gif

Figure 2-9: You set general configuration options for the server in the Server Settings tab.

In the Server Settings tab of the SQL Server Properties dialog box, you use the Default Language For User drop-down list box to set the default language and then click OK. Afterward, you must stop and restart the server for the changes to take effect. With sp_configure, the related Transact-SQL statement is

exec sp_configure 'default language', <language id number>

The language ID number for U.S. English is always 0.

Allowing and Disallowing System Updates

By default, users can only update the systems table with system-stored procedures, even if they have proper permissions. This is a valuable feature because it prevents users from executing statements that may corrupt the database or prevent SQL Server from starting. Still, you may want to change this behavior and allow direct updates to system tables. Once you allow modifications, anyone with proper permissions can update systems tables by executing statements or stored procedures.

This is very risky. Rather than take chances, you may want to follow this procedure:

  1. From the SQL Server Properties dialog box, go to the Server Settings tab.

  2. Enable system table modifications by selecting Allow Modifications To Be Made Directly To The System Catalogs.

  3. Click OK, and then stop SQL Server.

  4. Start SQL Server in single-user mode at the command line by typing sqlservr -m.

    Note: If multiple instances are installed, you'll need to use the –s instancename option here to start the instance.

  5. Make the necessary changes to the system tables.

  6. From the SQL Server Properties dialog box, go to the Server Settings tab.

  7. Disable system table modifications by clearing Allow Modifications To Be Made Directly To The System Catalogs.

  8. At the window running SQL Server from the command line, press Ctrl+C. Then, when prompted, type Y for Yes. This stops SQL Server. Restart the SQL Server service.

With sp_configure, the related Transact-SQL statement is

exec sp_configure 'allow updates', <0 or 1>

Note: If you use sp_configure to allow updates, you must use the reconfigure with override statement as well. Then stop and restart the server.

Allowing and Disallowing Nested Triggers

By default, SQL Server allows you to nest up to 32 levels of triggers. Nested triggers are useful for executing a series of tasks within a single transaction. For example, an action can initiate a trigger that starts another trigger, which in turn can start another trigger, and so on. Because the trigger is handled within a transaction, a failure at any level causes the entire transaction to roll back, which reverses all changes to the database. As a fail-safe measure, triggers are terminated when the maximum nesting level is exceeded. This protects against an infinite loop.

To allow or disallow nested triggers, complete the following steps:

  1. From the SQL Server Properties dialog box, go to the Server Settings tab.

  2. Set or clear Allow Triggers To Be Fired Which Fire Other Triggers (Nested Triggers).

  3. Click OK.

With sp_configure, the related Transact-SQL statement is

exec sp_configure 'nested triggers', <0 or 1>

Controlling Query Execution

The Query Governor disallows execution of any query that has a running time that exceeds a specified query cost. The query cost is the estimated time, in seconds, required to execute a query, and it's estimated prior to execution based on the query engine's analysis of execution time. By default, the Query Governor is turned off, meaning there is no maximum cost. To set the Query Governor, complete the following steps:

  1. From the SQL Server Properties dialog box, go to the Server Settings tab.

  2. Select Use Query Governor To Prevent Queries Exceeding Specified Cost.

  3. In the Query Governor field, type a maximum query cost limit. The valid range is from 0 to 2,147,483,647. A value of 0 disables the Query Governor. Any other value sets a maximum query cost limit.

  4. Click OK.

With sp_configure, the related Transact-SQL statement is

exec sp_configure 'query governor cost limit', <limit>

You can also set a per connection query cost limit in Transact-SQL using

set query_governor_cost_limit <limit>

Tip Before you set the Query Governor, you should use Query Analyzer to estimate the cost of current queries you're running on the server. This will give you a good idea of a value to use for the maximum query cost. You can also use Query Analyzer to optimize queries.

Setting the Default SQL Mail Profile

You can use e-mail facilities with SQL Server in two ways. You can

  • Configure stored procedures that are triggered by e-mail through SQL Mail.

  • Send notifications to designated operators through SQLAgentMail.

These SQL Server features can use separate mail profiles, or they can use the same mail profile. You must configure the profile(s) on the server and on associated valid e-mail account(s). Afterward, you need to complete the following steps:

  1. Tell SQL Server about the SQL Mail profile by accessing the SQL Mail Configuration dialog box and then typing the profile name in the Profile Name field. Once you configure mail properly, you can access the SQL Server Properties dialog box and type a mail logon name for SQL Mail in the Mail Login Name field of the Server Settings dialog box.

  2. Tell SQL Server Agent about the SQLAgentMail profile by accessing the SQL Server Agent Properties dialog box and then typing the profile name in the Mail Profile field in the General tab.

Note: You'll find detailed information on SQL Mail and SQL Server Agent in Chapter 3 and Chapter 12, respectively.

Configuring Year 2000 Support

SQL Server allows you to insert or modify dates without specifying the century part of the date. However, to be Year 2000 compliant, SQL Server interprets two-digit dates as being within a certain time span. By default, this time span is from 1950 to 2049. With this setting, all two-digit dates from 50 to 99 are treated as having 19 in front of them and all two-digit dates from 00 to 49 are treated as having 20 in front of them. Thus, SQL Server would interpret a two-digit year of 99 as 1999 and a two-digit year of 02 as 2002.

To maintain backward compatibility, Microsoft recommends that you leave the setting at the default value. You can, however, change this value by completing the following steps:

  1. From the SQL Server Properties dialog box, go to the Server Settings tab.

  2. In the When A Two-Digit Year Is Entered, Interpret It As A Year Between list box, select a value that is the ending year of the time span you want to work with. The valid range for the ending year is 1753 to 9999.

  3. Click OK.

Note: The time span affects all databases on the current server. Also, some older OLE clients support only a date range of 1931 to 2030. To be compatible with these clients, you may want to type 2030 as the ending year for the time span.

With sp_configure, the related Transact-SQL statement is

exec sp_configure 'two digit year cutoff', <ending year>

Database Settings

You use the Database Settings tab of the SQL Server Properties dialog box to configure server-wide database settings. As shown in Figure 2-10, you can use the tab to set index fill, backup and restore options, and checkpoint execution.

Setting the Index Fill

The default index fill determines how much space SQL Server should reserve when it creates a new index using existing data. A tradeoff is involved when setting the fill factor. Setting the fill factor too high slows down SQL Server when you add data to a table. However, setting a fill factor too low can affect read performance by an amount inversely proportional to the fill factor. For example, a fill factor of 25 percent can degrade read performance by a factor of 4 (or 4 times normal) but makes large updates faster initially. Ideally, you'll balance the need to quickly make updates against the need for good read performance and select a fill factor that makes sense for your situation.

Cc917616.ppc0210(en-us,TechNet.10).gif

Figure 2-10: You set general configuration options for the databases in the Database Settings tab.

Best Practice The fill factor is used only when an index is created and isn't maintained afterward. This allows you to add, delete, or update data in a table without worrying about maintaining a specific fill factor.

Because of this, the empty space in the data pages can fill up if you make extensive additions or modifications to the data. To redistribute the data, re-create the index and specify a fill factor when you do so. Indexes are discussed more in Chapter 6.

By default, the index fill is set to 0 but the valid range is from 0 to 100. The setting of 0 is an optimized setting for SQL Server. Any other value is an actual fill percentage.

SQL Server handles the optimized setting in much the same way as a fill percentage of 100. Here, SQL Server creates clustered indexes with full data pages and nonclustered indexes with full leaf pages. But unlike an index fill of 100, the optimized setting of 0 leaves room for growth in the upper level of the index tree. On the other hand, with an index fill of 100, there's no room for growth, which is why you should use this value only with read-only tables where you'll never add data.

If you need to, you can override the default when you create indexes. But you have to remember to do this. You can also set a fixed index fill as the default by completing the following steps:

  1. From the SQL Server Properties dialog box, go to the Database Settings tab.

  2. Select the Fixed check box.

  3. Use the Fixed slider to set a fill percentage. A low fill factor allows more room for insertions without requiring page splits, but the index takes up more space. A high fill factor allows less room for insertions that don't require page splits, but the index uses less space.

  4. Click OK.

With sp_configure, the related Transact-SQL statement is

exec sp_configure 'fill factor', <integer percentage>

Backup and Restore Time-Out

You often make SQL Server backups on tape devices. When you work with tape devices and the DB-Library, you may want to control whether or not you want to enforce a read/write time-out. The default is to have DB-Library "wait indefinitely" for a response from SQL Server, which is sometimes less than ideal. With an indefinite time-out, you won't necessarily get errors that let you know that you're having backup problems. To change this behavior, you may want to set a specific time-out, such as "try once and then quit" or "try for a certain number of minutes and then quit."

You set the time-out period by completing the following steps:

  1. From the SQL Server Properties dialog box, go to the Database Settings tab.

  2. To set an indefinite time-out, select the Wait Indefinitely option button.

  3. To try once and then quit, select the Try Once Then Quit option button.

  4. To try for a specified amount of time, select the Try For N Minute(s) option button and then enter the time-out period in the field provided.

  5. Click OK.

Backup and Restore Retention

As you'll learn in Chapter 11, SQL Server has many features to help you back up and restore data. When you write data to tapes using DB-Library, you can specify the number of days to maintain old files. This value is called the retention period, and you set it by completing the following steps:

  1. From the SQL Server Properties dialog box, go to the Database Settings tab.

  2. Enter the number of days you'd like to maintain old files in the Default Backup Media Retention (Days) field. The minimum value is 0, which specifies that old files are always overwritten. The valid range is from 0 to 365.

  3. Click OK.

With sp_configure, the related Transact-SQL statement is

exec sp_configure 'media retention', <number of days>

Flushing Cache with Checkpoints

Database checkpoints flush all cached data pages to the disk and are done on a per database basis. In SQL Server you control how often checkpoints occur using the recovery interval. By default, the recovery interval is set to 0, which allows SQL Server to dynamically control when checkpoints occur. This usually means that checkpoints occur about once a minute on active databases. Unless you're experiencing performance problems that are related to checkpoints, you shouldn't change this option.

You set the checkpoint interval manually by completing the following steps:

  1. From the SQL Server Properties dialog box, go to the Database Settings tab.

  2. Enter the checkpoint time in minutes in the Recovery Interval (Min) field. The valid range is from 0 to 32,767. This is a server-wide setting.

  3. Click OK.

With sp_configure, the related Transact-SQL statement is

exec sp_configure 'recovery interval', <number of minutes>

Replication

You use the Replication tab of the SQL Server Properties dialog box to manage and configure publishing and distribution services for SQL Server. This section discusses the available options on the Replication tab and how to start the Configure Publishing And Distribution Wizard.

Distribution and Publishing

Initially the Replication tab will display COMPUTERNAME\SQLINSTANCE Is Not Configured As A Publisher Or Distributor in the Publishing and Distribution section. To configure Publishing and Distribution services, you must run the Configure Publishing And Distribution Wizard first by selecting the Configure button. This will start the wizard and allow you to perform the following actions:

  • Specify the local SQL Server Instance or another server as a Distributor.

  • Configure the properties of the local SQL Server Instance as a Distributor.

  • Configure the properties of the local SQL Server Instance as a Publisher.

If you configure the local SQL Server Instance as a Publisher and Distributor, the Publishing and Distributor section will display View Or Change The Configuration Of COMPUTERNAME\SQLINSTANCE As A Publisher And Distributor. Selecting the Configure button using this configuration will access the Publisher and Distributor Properties page for the local SQL Server. The Publisher and Distributor Properties page will allow you to access and modify the properties for Distributors, Publishers, Publication Databases, and Subscribers.

If you configure the local SQL Server Instance as a Publisher, the Publishing and Distributor section will display View Or Change The Configuration Of COMPUTERNAME\SQLINSTANCE As A Publisher. Selecting the Configure button using this configuration will access the Publisher and Distributor Properties page, but you will only have access to the properties for Distributors, Publication Databases, and Subscribers.

Selecting the Disable button will start the Welcome To The Disable Publishing And Distribution Wizard, which will allow you to disable publishing, distribution, or both on the local SQL Server.

Replication Monitor Group

You will also see a new section on the Replication tab entitled Replication Monitor Group. Selecting the Add COMPUTERNAME\SQLINSTANCE As A Distributor In The Replication Monitor Group option will add the local SQL Server to the Replication Monitor group as a Distributor. This will allow you to monitor the replication activity of Distributors from the local computer.

Specific details covering Replication modes and setting up and configuring Publishing and Distribution servers for Subscribers will be covered in Chapter 9, "Configuring Snapshot, Merge, and Transactional Replication."

Adding and Removing Active Directory Information

You use the Active Directory tab of the SQL Server Properties dialog box to manage SQL Server information published in Active Directory services. The tab has three buttons:

  • Add Publishes information about a SQL Server instance in Active Directory.

  • Refresh Updates information related to a SQL Server instance in Active Directory. Useful when you create databases, server cubes, or data mining models and want the updates reflected throughout the directory before normal replication.

  • Remove Removes information about a SQL Server instance from Active Directory.

Configuring SQL Server with Stored Procedures

You can configure many areas of SQL Server through the SQL Server Properties dialog box. As you've learned in this chapter, you can also configure SQL Server with stored procedures, such as sp_configure. You execute stored procedures and other queries in Query Analyzer. Query Analyzer is a client tool that sends commands to the SQL Server query engine, which in turn parses, compiles, and executes the commands.

The following sections explain how you can configure SQL Server using Query Analyzer and stored procedures. You'll find more detailed coverage of Query Analyzer in other chapters.

Starting Query Analyzer

You can start Query Analyzer using any of these techniques:

  • Select Start, choose Programs, choose Microsoft SQL Server, and then choose Query Analyzer.

  • Run ISQLW.EXE at the command line.

  • From the Enterprise Manager, select the Tools menu, and then select the SQL Server Query Analyzer option.

When you start Query Analyzer from the menu or the command line, you have to specify connection information using the dialog box shown in Figure 2-11. You use the fields of this dialog box as follows:

  • SQL ServerUse this option to select the database server instance to which you want to connect. If the server instance isn't available in the drop-down list box, you can type in a server name or click the ellipsis button to display the SQL Active Directory Search dialog box, which you can use to search for all SQL servers within an entire Active Directory Forest.

  • Start SQL Server If It Is Stopped Starts the MSSQLServer service and the database if it's stopped.

  • Windows Authentication Uses your current domain account and password to establish the database connection. This works only if Windows authentication is enabled and you have appropriate privileges.

  • SQL Server Authentication Allows you to specify a SQL Server logon ID and password.

Cc917616.ppc0211(en-us,TechNet.10).gif

Figure 2-11: Use the Connect To SQL Server dialog box to select the server you want to use and then specify connection information. If you start Query Analyzer in Enterprise Manager you can often bypass this dialog box.

If you're working with an active database in Enterprise Manager and have already authenticated the connection, Query Analyzer automatically connects to the currently selected database server instance and uses this authentication information to log on. If there is no active database connection or if the logon fails, you'll see the dialog box shown in Figure 2-11.

Tip Query Analyzer normally connects you to the master database on the active server. You can use the DB drop-down list box on the Query Analyzer toolbar to change to any of the available databases on the server.

Changing Settings with sp_configure

The Query Analyzer window is normally divided into three panes (see Figure 2-12). The left pane allows you to browse objects that are available on the currently selected database server instance. The top right pane is for entering queries. The bottom right pane is for displaying results.

If you don't see the bottom right pane, don't worry. It's automatically displayed when you type a query. You can also set the pane to display by default. Select the Show Results Pane option on the Window menu.

Cc917616.ppc0212(en-us,TechNet.10).gif

Figure 2-12: Query Analyzer's newest feature is the object browser pane. You can use this pane to browse databases, tables, functions, and more.

As you know, you can use sp_configure to view and change SQL Server configuration settings. Two types of configuration settings are available: those that are dynamic and those that aren't. In this instance, a dynamic setting is one that you can change without having to stop and restart SQL Server. To execute sp_configure or other types of queries, type a command in the top pane and then click the toolbar's Execute Query button (it's the green arrow). You can also execute commands using these key sequences:

  • F5

  • Ctrl+E

  • Alt+X

Whenever you use sp_configure to modify settings, the changes don't actually take place until you also execute the RECONFIGURE command. You can change some highly risky settings only with the RECONFIGURE WITH OVERRIDE command. Additionally, sp_configure settings are divided into two categories: standard and advanced. You can execute standard commands at any time, but you can execute advanced commands only when Show Advanced Options is set to 1. With this setting in effect, you can modify both standard and advanced settings by following this procedure:

  1. In Query Analyzer, type

exec sp_configure 'show advanced options', 1 go reconfigure go

**Note**: You could later disable advanced options by setting this value to 0.
  1. Execute the commands by pressing Ctrl+E.

  2. Clear the query windows by clicking the toolbar's Clear Query Window button.

  3. Now type one sp_configure command for each option you want to change.

  4. Type reconfigure (or reconfigure with override).

  5. Execute the commands by pressing Ctrl+E.

  6. If you changed any nondynamic settings, stop and start the server. (See Table 2-3 and Table 2-4 for details.)

Configuration Parameters

Table 2-3 provides a summary of the standard configuration parameters. You'll find parameters listed in alphabetical order with the minimum, maximum, and default values shown. The dynamic parameter column tells you whether the setting is dynamic. If you see a "No" in this column, you'll need to stop and restart the server to enforce changes.

Table 2-3 Quick Reference Summary for Standard Configuration Parameters

Parameter Name

Description

Minimum Value

Maximum Value

Default Value

Dynamic Yes/No

allow updates

Allow updates to systems tables.

0

1

0

Yes

default language

Sets language formatting for dates. With localized versions, can change to local language or U.S. English.

0

9999

Varies, 0 is U.S. English

Yes

max text repl size

Sets maximum number of bytes to be written to a replicated column.

0

2147483647

65536

Yes

nested triggers

Determines whether nested triggers are allowed.

0

1

1

Yes

remote access

Controls access from remote servers. 0 denies access.

0

1

1

No

remote login timeout

Sets remote logon time-out in seconds. 0 sets no time-out.

0

2147483647

5

Yes

remote proc trans

Use DTC for distributed transactions.

0

1

0

Yes

remote query timeout

Sets time-out in seconds for queries from remote servers. 0 sets no time-out.

0

2147483647

0

Yes

show advanced options.

Set to 1 to turn on advanced options

0

1

1

Yes

user options

Sets global defaults for user connections using a bit mask.

0

16383

0

Yes

Table 2-4 provides a summary of the advanced configuration parameters. To view or change these parameters, you have to set the parameter Show Advanced Options to 1. Note also that you can't change some advanced options (you can only view them).

Table 2-4 Quick Reference Summary for Advanced Configuration Parameters

Parameter Name

Description

Minimum Value

Maximum Value

Default Value

Dynamic Yes/No

affinity mask

Associates threads with processors.

0

2147483647

0

No

AWE enabled

Enables support for very large address spaces.

0

1

0

Yes

cost threshold for parallelism

Helps determine when queries are processed in parallel.

0

32767

5

Yes

cursor threshold

-1 to generate synchronous key sets for cursors. 0 sets asynchronous. Other values perform row comparison.

-1

2147483647

-1

Yes

default full- text language

Sets the default language for full-text searches in the database.

0

2147483647

0

Yes

default language

Sets the default language in the database

0

9999

0

Yes

fill factor

Sets index fill factor percentage. 0 lets SQL Server configure.

0

100

0

No

index create memory

Sets KB of memory used when creating index to sort data.

704

2147483647

0

Yes

lightweight pooling

1 switches to fibers.

0

1

0

Yes

locks

Sets the number of available locks.

5000

2147483647

0

No

max degree of parallelism

With SMP, sets number of threads to execute a parallel plan.

0

32

0

Yes

max server memory

Sets maximum memory (MB) used by SQL Server.

4

2147483647

Varies

Yes

max worker threads

Sets the maximum number of threads.

10

32767

255

Yes

media retention

Sets number of days to retain backup media.

0

365

0

No

min memory per query

Sets minimum memory in KB to reserve for queries.

512

2147483647

1024

Yes

min server memory

Sets minimum memory (MB) used by SQL Server.

0

2147483647

0

Yes

network packet size

Sets the byte size of network packets.

512

65535

4096

Yes

open objects

Sets maximum number of open objects. 0 for SQL control.

0

2147483647

0

No

priority boost

Set to 1 to boost thread priority.

0

1

0

No

query governor cost limit

Sets cost limit for queries. 0 turns off limit.

0

2147483647

0

No

query wait

Sets time-out value for queries.

-1

2147483647

-1

Yes

recovery interval

Sets time interval in minutes for checkpoints. 0 for SQL control.

0

32767

0

Yes

scan for startup procs

Set to 1 to scan for stored procedure to execute on startup.

0

1

0

No

set working set size

1 sets no swapping for memory.

0

1

0

No

two digit year cutoff

Sets date range for two-digit dates.

1753

9999

2049

Yes

user connections

Controls maximum number of user connections for the database server instance. Set to 0 for unlimited.

0

32767

0

No

Troubleshooting Configuration Problems

The sections that follow examine two specific techniques that you can use to resolve SQL Server configuration problems. You'll learn how to recover from a bad configuration and how to rebuild the master database.

Recovering from a Bad Configuration

Although SQL Server 2000 has many safeguards that help you avoid configuration settings that keep SQL Server from starting, you may occasionally find that a configuration change prevents SQL Server from starting. In this case you can recover the server instance by completing the following steps:

  1. Log on to the affected server locally or remotely through Telnet or Terminal Server. You must log on as using a local administrator account or the account used by the database server instance.

    Make sure that the MSSQLServer or MSSQL$instancename service is stopped. If it isn't, stop the service using one of the following methods:

    • SQL Server Service Manager

    • Windows NT Service Manager

    • Windows 2000 Services

  2. If the instance of SQL Server was installed as a default installation, you can stop the service by using the following command:

net stop MSSQLSERVER

  1. From the command prompt, switch to the directory of the associated SQL Server instance (either mssql\binn or mssql$instancename\Binn). You must be in this directory to use the sqlservr utility.

  2. Start SQL Server from the command line with the following option:

sqlservr –s(instancename) –f

  1. You must use the -s option to specify the instance of SQL Server if multiple instances of SQL Server are installed. The -f option starts SQL Server in single-user mode with a minimum configuration. This ensures that the bad configuration isn't loaded.

  2. Wait for the server to start up. SQL Server should write a few pages of output to the screen. Leave the server running.

  3. In another command prompt window or Telnet session, start ISQL with the username of a SQL account with administrator privileges and password:

isql –U username –P password

**Note**: You must specify the instance you are connecting to (isql –U username –P password –S*computername\\instancename*) if multiple instances of SQL Server 2000 are installed.
  1. If you've accessed ISQL properly, you should see the prompt change to >.

  2. Reverse the changes made to the configuration by entering commands much like you would in Query Analyzer. The key difference is that you execute the commands with go, as in the following example:

exec sp_configure 'max server memory', 128 go reconfigure go

  1. When you're finished, exit ISQL by typing exit.

  2. At the window running SQL Server from the command line, press Ctrl+C. Then, when prompted, type Y for Yes. This stops SQL Server.

  3. Restart SQL Server as you normally would. If you've made the appropriate changes, the server should start normally. Otherwise, repeat this procedure.

Changing Collation and Rebuilding the Master Database

The key reasons for rebuilding the master database are

  • When you need to set a new default collation for a database server instance.

  • When you need to repair a corrupted master database.

After you rebuild the master database, all user databases are detached and unreadable. To recover them, you must re-create all your user databases. You can't restore the user databases from backup—the restore maintains the information that was set when you created the backup and you may instead want to move the databases to another server by means of Data Transformation Services (DTS), which are covered in Chapter 7.

Note: Before you rebuild the master database, you should know that collation plays a different role on SQL Server 2000 than it did on SQL Server 7. Previous versions of SQL Server used the same collation for all databases and database objects within each instance. SQL Server 2000 can specify different collations for separate databases and columns within a database. When you install SQL Server, the default collation is set. You can't change the default setting on an existing SQL Server installation without rebuilding the master database. You can, however, set separate collation options for each database that you install on the server. You can also set separate collation options for tables, parameters, and literal strings.

You can rebuild the master database by completing the following steps:

  1. Stop the SQL Server service for the database server instance that you want to work with. For the default instance, the service is named MSSQLServer. For named instances, the service is named MSSQL$instancename.

    Tip To learn how to stop the SQL Server service, see the section of Chapter 3 entitled "Starting, Stopping, and Pausing SQL Server."

  2. Run Rebuild Master from the command line using the following command:

rebuildm

  1. You should see the Rebuild Master dialog box. Use the Server selection list to choose the database server instance that you want to work with.

  2. Source Directory Containing Data Files shows the original install location for the database server instance. Normally, the source directory refers to a location on the SQL Server CD-ROM. As necessary, type a new directory path or click Browse to find a directory path.

  3. Click Settings, and then use the options of the Collation Settings dialog box to configure the new server settings. Click OK when you're finished.

  4. In Rebuild Master, click Rebuild. When prompted to confirm the action, click Yes. Rebuild Master copies the master database source files to the server and then begins remapping and reconfiguring the server.

Link
Click to order