SQL Server includes many configuration options for tuning server and database operations. For most of these options, SQL Server dynamically adjusts the configuration when needed, for example by allocating more memory or adding user connections.
In most environments, you do not need to manually set or adjust any of these options. In rare cases, however, you might find it useful to override SQL Server’s dynamic allocation and manually set the value of a configuration option. The specific settings you should use can vary greatly, depending on your deployment and your database management practices.
The following table lists the SQL Server configuration options you are most likely to use for AppManager. For more information about the options and to determine which options, of any, to change, see your SQL Server documentation.
|
SQL Server Option |
Considerations |
|---|---|
|
locks |
The internal memory cost of locks is relatively low, so you might want to manually set this option to avoid running out of locks on busy systems. In general, you can calculate the value for locks by estimating (user connections) x (maximum number of tables simultaneously accessed by any user at the same time) x (maximum rows per table accessed by any user at the same time). This estimation assumes no lock escalation and more page activity than is likely to occur, but is a good starting point. |
|
min server memory max server memory |
In general, you should configure SQL Server with as much RAM as you can without causing Windows to page, but you should also consider the size of your SQL Server environment and how your AppManager components are distributed. For example, if you are using a single server for both the repository and management server, configure SQL Server to use less memory--to accommodate Windows and the management server--than if the server is a dedicated repository server. |
|
open objects |
To set this value, estimate the maximum number of tables, views, rules, stored procedures, defaults, and triggers that will be open at any one time for the SQL Server. It is better to overestimate than underestimate this value. A typical configuration in an active environment is 5000 open objects. If you are collecting or purging a large amount of data, increase the number of open objects, for example, to 20,000, or even 50,000. NOTE:Open objects consume memory, so increasing this value reduces the memory available for other SQL Server operations. It might require a larger amount of memory dedicated to the server. |
|
user connections |
To determine the maximum number of user connections that your system allows, use the following statement: SELECT @@MAX_CONNECTIONS Typically, AppManager requires 80-100 user connections. The AppManager Management Service (NetIQms) uses 40-60 connections. Each console simultaneously connecting to the repository uses 1-5 connections. If your organization has several console programs connecting to the same repository at the same time, you might need to increase this value. For example, if, on average, you have 12-15 console programs simultaneously accessing the same repository, you might set the user connections option to a value of 150 to 200 (depending on the amount of buffer you want for connections that are blocked and must time out). |
In addition to these server-level configuration options, SQL Server includes several database-level configuration options that you can use to specify the characteristics of each database. These options help you to control specific behavior for a database, such as automatic operations and recovery options. In general, these options do not affect AppManager operation or performance. You should, however, consider these options when planning your backup and database management strategy and when deciding whether you will do full backups, incremental backups, or both.