Modifying SQL Server Parameters

[Previous] [Next]

Recall from Chapter 2 that if you install SQL Server as part of the SMS 2.0 setup process, SMS will set the SQL Server parameters to their optimum values for you. If you choose to install SQL Server yourself, however, you should pay attention to the following SQL Server configuration parameters and set them appropriately before installing SMS 2.0: User Connections, Open Objects, Memory, Locks, and Tempdb Size. Table 18-3 provides guidelines for setting these parameters for SQL Server 6.5 and SQL Server 7.0.

Table 18-3. SQL Server configuration parameters

Parameter	Guidelines
User Connections	SMS 2.0 requires a minimum of 40 user connections for the site server and 2 connections for each SMS Administrator Console you plan to install. It also requires 5 additional user connections for each instance of the SMS Administrator Console, if more than five consoles will be running concurrently on your site. You can set SMS 2.0 to calculate this number and configure it automatically during setup. Each installation of SMS 2.0 requires 20 user connections. In SQL Server 6.5, each user connection allocates 40 KB of RAM. In SQL 7.0, this allocation is made dynamically at the time of the connection, providing more efficient memory management.
Open Objects	This parameter indicates the number of tables, views, stored procedures, and the like that can be open at a time. If you exceed the specified number of open objects, SQL Server must close some objects before it can open others, resulting in a performance hit. For most sites, although the default is 500, you may want to set this parameter to 1000. For large sites, this number could be 5000 or more. Use SQL Server Performance Monitor counters to track the number of open objects in use to determine the optimum number for the SMS site. Note that SQL Server 7.0 sizes this number automatically.
Memory	This parameter indicates the amount of RAM that should be used for database caching and management. SMS automatically allocates 16 MB of RAM for SQL Server use. In SQL Server 6.5, memory is allocated in memory units of 2 KB. Set this value to at least 8192 (16 MB). Increasing this number may improve SQL Server performance, but it may also detract from other server operations (such as SMS site server). SQL Server 7.0 allocates memory dynamically in 8-KB units. You can define a range for SQL Server to use.
Locks	This parameter prevents users from accessing and updating the same data at the same time. Because of the volume of information contained in the database, Microsoft recommends setting this value from 5000 to 10,000 depending on the size of the database and the number of SMS Administrator Consoles.
Tempdb Size	This temporary database and log are used to manage queries and sorts. By default, the tempdb database and log information are maintained in the same SQL device. (Please see Chapter 18 for details on the SQL device.) For best performance, both should be kept in this default location. Note that this is contrary to what the Systems Management Server Administrator's Guide recommends for high volumes of activity. This recommendation was later corrected in the Microsoft Systems Management Server Version 2.0 Release Notes. Set the tempdb data device size in SQL Server 6.5 to at least 20 percent of the SMS database device size. Set the tempdb log device size to at least 20 percent of the tempdb data device size. In SQL Server 7.0, as you have by now surmised, the tempdb database is sized dynamically.

Modifying Parameters for SQL Server 6.5

To modify these parameters settings for SQL Server 6.5, follow these steps:

1. In Enterprise Manager, select your SQL server entry, right-click on it, and choose Configure from the context menu to display the Server Configuration/Options Properties window.
2. Click on the Configuration tab, shown in Figure 18-26. Scroll through the parameter list to locate the parameter you want to modify. In the Current column for the parameter, enter the new value. Repeat this process for each parameter you want to modify.



Figure 18-26. The Configuration tab of the Server Configuration/Options Properties window.

3. Click OK to save your changes.

As you select each parameter, an explanation appears in the Description text box at the bottom of the Configuration tab describing the parameter and indicating when the change will take effect. Most changes require that you stop and restart SQL Server. If you need to stop and restart SQL Server to effect your changes, simply right-click on the server entry in Enterprise Manager, and choose Stop from the context menu. To restart SQL Server, right-click on the server entry again, and choose Start.

Modifying Parameters for SQL Server 7.0

To modify these parameter settings for SQL Server 7.0, follow these steps:

1. In Enterprise Manager, select your SQL server entry, right-click on it, and choose Properties from the context menu to display the SQL Server Properties window, shown in Figure 18-27.



Figure 18-27. The SQL Server Properties window.

2. The SQL Server Properties window contains tabs for those parameters for which you can modify settings. (Recall from Table 18-3 that most SMS-specific parameters are dynamically managed by SQL Server 7.0.)
3. Click on the Memory tab, shown in Figure 18-28. Notice that the Dynamically Configure SQL Server Memory option is enabled by default, although you can modify the memory range within which SQL Server should manage memory allocation. You can also specify a fixed amount of memory as well as identify the amount of RAM to allocate per user for query execution.
4. Click on the Connections tab, shown in Figure 18-29. This tab displays the maximum number of user connections that were configured for SQL Server during the SMS setup. SQL Server will dynamically allocate connections and appropriate resources to support them up to this maximum number. The allocation of user connections is a value you should monitor. If you add additional SMS Administrator Consoles or define additional site systems, you may need to increase the maximum number of connections to accommodate the increased resource demand by modifying this setting.



Figure 18-28. The Memory tab.



Figure 18-29. The Connections tab.

5. When you have finished making your changes, click OK to save them. As with SQL Server 6.5, you may need to stop and then restart SQL Server to implement your changes. If this step is necessary, a message box will display to that effect.

MORE INFO

---

For more information about performance tuning SQL Server 7.0, download the SQL Server 7.0 Performance Tuning white paper at http://www.microsoft.com/sql/productinfo/perftuning.htm.

The topic of time synchronization is significant enough to bear revisiting here (you can refer to the "Synchronizing System Clocks" Real World section in Chapter 2 for a specific example) especially if SQL server and the SMS site server are installed on two different servers. Because the time stamp of data objects created will generally be that of the SQL server, it's important that the SMS site server and the SQL server synchronize their system times on a regular basis.

It is also important that the SMS clients synchronize their time with the SMS site server and the SQL server. SMS client computers will check their own system clocks when determining when to execute a program or run an agent. You can see how easily things can go awry if the SQL server, site server and client computer system clocks are all set to different times. A package may not execute at the time you expected, or an inventory collection may not take place because the scheduled times and the system clock are out of synch.

One way to overcome this situation is to identify one server as your time server for the SMS site. Have all your site systems, the SMS client computers, and the SQL server synchronize their times with the time server. Or, you might even consider making the SQL server the time server for the SMS site.