Environment
NetIQ AppManager 7.0.x
Situation
AppManager Version 7 has made some changes internally in how it makes uses of SQL Server resources such as the tempdb. In order to ensure smooth operation, the SQL Server should be tuned accordingly. If this is not done, it can increase to increased blocking in the QDB and also an increase of the number of connections from each MS over time.
Resolution
The following guidelines should be adopted as minimum tuning guidelines:-
- The transaction log for the AppManager Repository should be approximately one third the size of the data file for the Repository. (for example, if the Repository data file is 25 Gb, then the transaction log should be sized at 10 Gb)
- The Repository database should be set to ?simple? recovery. With this recovery method, the transaction log is automatically checkpointed when it reaches approximately 70% of capacity. With the ?full? recovery method, the transaction log is not automatically checkpointed. Instead, a maintenance plan is required in order to perform regular transaction log backups. Failure to do this in full recovery mode can lead to the transaction log growing until it reaches disk capacity. When a Repository is installed, simple recovery is the default method configured.
- The data and transaction log files for the Repository should be located on separate logical drives. Depending on the underlying disk technology (for example a SAN), this could translate to a single physical drive, or separate physical drives. At a minimum, RAID level 1 should be used for the data files and RAID level 0 for the log files.
- The tempdb data file should be sized according to the largest database on the system. This file should be approximately one third the size of the largest database file. For example, if the AppManager Repository data file is 30 Gb, the tempdb datafile should be 10 Gb. The transaction log for the tempdb should be approximately half the size of the data file (i.e. in this case, it should be 5 Gb in size).
- The tempdb data and log files should be treated as any other database. The data and log files should be located on separate logical drives as described in point #3 above.
These guidelines should be followed regardless of which version of SQL Server is being used. In addition to the above, there are some specific actions that may be required depending on the version of SQL Server in use.
For SQL Server 2000 SP4, there is information in the following Microsoft article:-
This article describes issues relating to running SQL Server 2000 SP4 on servers with multiple processors. In this case, one of the recommendations is to split the tempdb data files according to the number of processors in use, and to ensure that these files are equal in size. So for example, if the tempdb is 10 Gb and located on a server with 8 processors then it would be appropriate to split the file into 8 separate 2 Gb files (it is better to exceed the sizing estimate for the tempdb when splitting it into multiple files).
All of the recommendations in the Microsoft article should be followed ? including setting the trace flag.
For SQL 2005 SP2, there is information in the following Microsoft article:-
Specifically, if the guidelines for SQL 2000 SP4 were followed and the server subsequently was upgraded to SQL Server 2005 SP2, some steps to address performance with SQL Server 2000 SP4 can cause issues after the upgrade. The article discusses this and gives details of a patch that should be applied to address the problem
There are other considerations that should be made in relation to how the data files and log files for the repository database and tempdb should be configured. If a database file needs to grow because the usage has reached the file size, then this has a major impact on performance, both at the time the file grows and potentially after that. If the tempdb grows, it will return to its configured size when SQL Server is restarted. If a database file grows, it will not automatically shrink at any time.
The first consideration here is the growing of a database file. Essentially, processing in a database will be suspended whilst a database file grows. This can lead to a backlog of transactions/activity that cannot continue until the file growth is complete.
The second consideration is physical file fragmentation. Growing a database file is unlikely to happen in a contiguous way. Especially if this happens frequently. Fragmentation of the database files can have an enormous impact on the performance of the database, and so should be avoided.
One recommendation is to disallow automatic growth of database files, and of tempdb files. This will ensure that the resource contention that takes place during any growth is avoided. However, it also means that a greater emphasis is placed on monitoring the current use of all database files, because if a file reaches capacity then that database will cease to operate.
Allowing autogrowth avoids this situation, but can lead to the issues described above. It is possible to minimize file fragmentation by setting the growth size to a reasonable figure (for example, the default growth of 1 Mb will certainly lead to file fragmentation so a figure of 512 Mb might be more appropriate). But there needs to be a balance between having the growth so small it leads to fragmentation, and so large that any growth causes an extended period of database inaccessibility.
The choice of whether to allow autogrowth, and if so what settings to use for how big the growth might be should be made by a database administrator after considering the pros and cons of each case.