Scope
The scope of this section is to help you configure the SQL Server-based PMDB after you have installed 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.