Database Maintenance for ProjectWise

We get asked a lot what kind of database maintenance or things to keep in mind that a user should do on their ProjectWise system's database.  These should be basic tasks that a database administrator would take care of, but sometimes that is not the case.  Below are some areas that you will want to focus on:

Memory

• Make sure your database is using as much memory as possible.  Sometimes a database will be setup with 16gb of RAM but it is only utilizing 2GB of that RAM for the database.  You will want to make sure that your dba has as much memory allocated to the database as possible.

Disk

• Disk optimization can greatly increase SQL Server performance when managed correctly. Removing old backup files to a file-server or network share can increase available disk space needed for transaction logs, database files, and indexes.

Backup

• Regular backups of the transaction logs help to keep the transaction log's size low, which within a database they can grow quickly and consume all the available space allocated to the db.

• The backup process truncates old log records no longer needed for recovery and marks them as inactive so they can be overwritten. Implement a good backup strategy consisting of full database backups, transaction log backups, and system database backups.

Indexes

• A scheduled task to rebuild your indexes is easy to set up and can keep ProjectWise running smoothly.  We have some users that rebuild them once a week and some that rebuild them once a day.  Depending on how much your datasource is used will depend on how often you want them rebuilt.
• It is recommended to rebuild an index that is over 30% fragmented and reorganize when an index is between 10% and 30% fragmented. Rebuilding a fragmented Index is most advantageous if the index contains at least 100 pages, otherwise you may not see any difference after a rebuild is performed if the Indexes are small.

Update Statistics

• It is recommended to update the statistics if the results show an outdated date in the “Updated” column and/or if there’s a substantial difference between the “Actual Number of Rows” and the “Estimated Number of Rows” in that table. You can view the table properties get the “Actual Number of Rows” from MicroSoft SQL Server Management Studio.
• Updating statistics can also be an automated task, our users normally update statistics and rebuild indexes on the same schedule and it is just as easy to setup as rebuilding your indexes.

I/O distribution Analysis for Performance

• Analyze the I/O of your system and decide the best distribution for the I/O load. Perform object level analysis and do performance tuning at table level. The goal is to reduce I/O performance bottlenecks and set optimal setting for read and write database.

If you have any further questions or need clarification please let us know and we will be glad to help.