SQL Server 2000 Administrator's Pocket Consultant: Configuring and Tuning Microsoft SQL Server

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

exec sp_configure 'show advanced options', 1

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

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

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. Figure 2-1: Use the options to select the instance type as either default or named. See full-sized image.
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. Figure 2-2: Use the Services Accounts dialog box to determine how the SQL Server and SQL Server Agent services will run. See full-sized image.
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:

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

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

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:

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:

Top of page

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:

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

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.
See full-sized image.

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:

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:

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. 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. See full-sized image.

Removing Startup Parameters

You can remove startup parameters by completing the following steps:

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

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:

Using a Domain Account

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

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.

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

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

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:

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.

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.
See full-sized image.

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:

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:

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:

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:

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. 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. See full-sized image.
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

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:

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:

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.

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

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:

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

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:

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.

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

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:

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:

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:

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

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:

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:

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.

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

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:

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:

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:

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:

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:

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:

Top of page

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:

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:

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.
See full-sized image.

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.

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.
See full-sized image.

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:

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:

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

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

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

Top of page

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 confi