SMS: SQL Server Tuning Parameters for Systems Management Server

ID: Q166244

The information in this article applies to:

Microsoft SQL Server versions 4.2x, 6.0, 6.5
Microsoft Systems Management Server versions 1.0, 1.1, 1.2

SUMMARY

Systems Management Server requires several SQL Server configuration options to be set correctly to perform at maximum efficiency. This article summarizes the recommended configuration options for a SQL Server running a Systems Management Server database.

MORE INFORMATION

Network Support

Named Pipes network support is required for Systems Management Server to use to communicate with the Systems Management Server database. You can change SQL Server network support by running SQL Server setup, selecting the Change Network Support option, and then selecting Named Pipes as an installed network.

Recommended Options for Tempdb and the Systems Management Server Database

TempDB should be 20% of the size of the largest database on the SQL server.

Tempdb database options enabled:


   Select Into/ Bulk Copy
     Truncate Log on CheckPoint

Tempdb database options disabled:


   Columns Null by Default
   No CheckPoint on Recovery
   Single User
   DBO Use Only
   Read Only

Systems Management Server database options enabled:


   Truncate Log on CheckPoint

Systems Management Server database options disabled:


   Select Into/ Bulk Copy
   Columns Null by Default
   No CheckPoint on Recovery
   Single User
   DBO Use Only
   Read Only

In SQL Server 6.0 and 6.5, you can change the database options through the SQL Enterprise Manager user interface by clicking Databases on the Manage menu. From there, double-click the database to edit, and click the Options tab. It is also possible to double-click the database name in the Server Manager window.

In SQL Server 4.2a, 6.0, and 6.5, you can change the database options by using the SP_DBOPTION stored procedure.

Recommended SQL Server Configuration Options

SQL Server Memory:
Set the SQL Server memory appropriately. This is the amount of RAM dedicated to SQL Server. This setting depends on the amount of physical RAM in the computer and the usage and performance requirements of SQL Server. Memory is designated in 2-KB blocks. For example, for a dedicated SQL Server with 128 megabytes (MB) of RAM, you may want to set the memory to 64 MB of RAM (32,768 2-KB blocks) to SQL Server. On a SQL Server and Systems Management Server site server with 128 MB of RAM, you may only want to dedicate 40 MB of RAM (20,480 2-KB blocks) to SQL Server.

SQL Server open Objects:
Set open objects to 5,000-7,000, depending on the size of your site and the child sites below it. The SQL Server default for open objects is 500, which is not adequate for a small SQL Server running Systems Management Server. Symptoms of open objects being set too low on a SQL Server include poor Systems Management Server or SQL Server performance, a backlog of deltamifs or .mif files in the Systems Management Server directory structure, or delays in inventory, package distribution, and job status MIF processing.

SQL Server User Connections:
Set user connections appropriately. Each user connection takes 40 KB of RAM, so this value is determined by the amount of memory dedicated SQL Server and the number of concurrent connections required. Each Systems Management Server site server reporting to a SQL Server requires at least 10 connections. Each running instance of the Systems Management Server Administrator program and the SQL Enterprise Manager requires at least one more connection.

Tempdb in RAM:
Microsoft does not recommend placing tempdb in RAM on a SQL Server running Systems Management Server.

In SQL Server 6.0 and 6.5, you can change the SQL Server configuration options through the SQL Enterprise Manager user interface by clicking SQL Server Configure on the Server menu. From there, click the Configuration tab.

In SQL Server 4.2a, 6.0, and 6.5, you can change the configuration options by using the SP_CONFIGURE stored procedure.

Additional Information

If you make any changes to these parameters, stop and restart the MSSQLServer service.

Please refer to your SQL Server documentation or online Help for more information on these settings.

A regularly scheduled database dump, along with backup of the Systems Management Server registry and directory structure, is a mandatory part of a good backup and recovery procedure.

For more information, see the following article in the Microsoft Knowledge Base:

Q166967 INF: Proper SQL Server Configuration Settings

Additional query words: prodsms prodsql Slow Site Updates Performance

Keywords : kbinterop kbnetwork smsconfig smsdatabase
Version : winnt:1.0,1.1,1.2,4.2x,6.0,6.5
Platform : winnt
Issue type : kbhowto