Microsoft KB Archive/166244

= SMS: SQL Server Tuning Parameters for Systems Management Server =

Article ID: 166244

Article Last Modified on 10/27/2006

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft Systems Management Server 1.0 Standard Edition
 * Microsoft Systems Management Server 1.1 Standard Edition
 * Microsoft Systems Management Server 1.2 Standard Edition

-



This article was previously published under Q166244



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.



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:

166967 INF: Proper SQL Server Configuration Settings

Additional query words: prodsms prodsql Slow Site Updates Performance

Keywords: kbconfig kbdatabase kbhowto kbinterop kbnetwork KB166244

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.