This section helps you configure the SQL Server-based PMDB after you install Precise. This is not a replacement of the content within Installation and Administration. The configuration recommendations help you to fine-tune the PMDB, which then results in faster and more efficient performance.
Prerequisites
Before applying the information in this section, refer to the content within Installation and Administration .
We recommend that the PMDB database is an MS-SQL dedicated instance. Change the following parameters to avoid using the MS-SQL full automatic configuration function:
- max server memory (MB) should be 80% of the server's RAM
- min memory per query (KB) should be 512 instead of 1024
An example to configure a parameter is the following command from the Query Analyzer:
EXEC sp_configure 'max server memory (MB)', [new value]
RECONFIGURE WITH OVERRIDE
GO
Maintenance operations
Check the table and index status by running the following command:
dbcc showcontig({table name}) with all_indexes
The results should be that the Extent Scan Fragmentation is less than 50, and the Logical Scan Fragmentation is low, otherwise it damages the index scans.
If one of the above conditions is confirmed, perform a reorganization on the indexes using the following command:
dbcc dbreindex ('{databasename.tableName}')
Be aware that the index and the table are unavailable during the rebuild.
Backing up the database and creating archiving
Use the Database Maintenance Plan function to create a backup plan for the database. Make sure that the SqlServer agent is automatically started at database startup.
- If you choose the simple recovery mode for the database (the transaction log can be deleted after a commit or checkpoint) , the recovery does not work to a point in time, but only to the backup time.
- If you choose a full recovery mode for the database, make sure you also backup the transaction log and shrink the database periodically , to stop the transaction log from growing infinitely.
If you do not create a backup, information is lost when you have a database crash and you cannot recover data .
Additional DBA settings
To improve performance, the DBA on site must check that the settings in the following table contain the correct values.
Table 1 Additional DBA settings
| Setting | Description | Value |
|---|---|---|
| Size of the datafile | The default size when you create the PMDB. | 2GB |
| Size of the log file | The default size when you create the PMDB. | 500MB |
| Auto extent size for datafiles and logs | The size of the new segment to be allocated. | 30% of the initial datafile For example, if the datafile is initially 1GB, the auto extent size should be 300MB. If you have a very large installation, the auto extent size should be 1GB. |
| Tempdb location | Put the tempdb database on a fast I/O system. |