ProjectWise Performance Tuning: Part 1

 

Over the past few years a large part of my job function has been to do performance testing of ProjectWise.  I'm titling this article as Part 1 because the one thing I've learn is that performance tuning is an ongoing effort.  With each workflow, network configuration, database and database configuration we are constantly finding ways to improve the performance of ProjectWise.  The items I will be discussing in this article are not silver bullets or miracle cures for performance problems.  There is no magic to Performance Tuning but more a systematic approach of looking at bottle necks in the system and then taking into account the conditions that contribute to the bottleneck.

That being said the following are some items that should almost be in a checklist as you set up your ProjectWise system.  Many of these items are specific to SQL Server just because that's where our focus has been for the past several months.     

Advanced OS Memory Setting:

On Window Server 2003 there is a setting somewhat buried in the Control Panel, System Properties dialog.  The setting basically allows you to tell the Operating System that the applications running on the server should be given priority to memory over background tasks.  Since both SQL Server and ProjectWise are server applications running on the OS they should be given priority to the system memory resources over background tasks.

Start -> Control Panel -> System ->  Advanced System Settings -> Performance -> Settings -> Advanced

 

Note that once you make changes to these setting a system reboot is required.  Below are two links which provide some additional information on this setting. 

http://software.intel.com/en-us/articles/stop-is-your-system-set-up-correctly/

http://blogs.msdn.com/axperf/archive/2008/03/10/welcome-database-configuration-checklist-part-1.aspx

This setting does not exist on Windows Server 2008 and as mentioned in the link above, Processor Scheduling should be set to Background Services.  By having this setting enabled on both the SQL Server Database Server and the ProjectWise Integration Server there was a significant reduction in the time it took to log into ProjectWise. 

SQL Server TEMPDB:

The TEMPDB database is a system database that is used by SQL Server for storing temporary tables and temporary stored procedures.  It is also used for doing sorting, sub-queries, order bys, group bys, etc.  Having this database on a separate drive allows the database to work more efficiently by alleviating IO contention between this file and the main database files. 

  • As with other SQL Server database files these files should not be scanned for Viruses or Defragmented with tools external to SQL Server.
  • There should be a TEMPDB database file for each processor and they should all be created equal in size.
    • If the system has 4 dual core processors there should be 8 database files for TEMPDB.
    • Even with more than 8 processors 8 TEMPDB files will be sufficient.
  • TEMPDB files should be given ample space when initially created to minimize fragmentation.
  • Moving the TEMPDB database to another drive:
    http://www.sql-server-performance.com/tips/tempdb_p1.aspx

SQL Server Database Files:

The key thing to keep in mind with SQL Server is the more disk spindles the faster access you have to the data.  So bigger isn't always better if that big database drive you have takes forever to retrieve the data.  Since SQL Server may be writing to the log files and the database files it is very import to keep these files on separate drives as well.   

  • User Database files (.mdf) should reside on a separate drive from the data files for the System Databases.
  • Log files (.ldf) should reside on another drive from the database files (.mdf), but can reside with the other log files.

Now what about a SAN or RAID drives?  SAN drives and RAID drives can be very fast at accessing the data and they give you great flexibility for maintaining redundant data in case one of the drives would crash.  So, since SAN and RAID systems are made up of multiple drives can all the database files reside on the same logical disk drive?  No, even though a SAN system has multiple drives SQL Server may still end up trying to read and write to the same disk with the potential for Disk IO contention.  The bottom line is to keep the database files on separate drives, separate SANs or separate RAIDs.

ProjectWise Audit Trail Archive:

In ProjectWise there is a feature that allows the ProjectWise Admin to reduce to the size of the Audit Trail table, DMS_AUDT, by moving old records to an archive table.  The process runs hourly and can be set to move records that are a day old up to server years old to the archive table.  The performance issue with this feature is that it runs every hour and by default is set to move the records from same time of day but at the specified archive interval set in the ProjectWise admin tool. 

Audit records are recorded for all types of operations.  These types of operation can be set by the ProjectWise admin.  So let's say that we are recording login information.  Between 8:00 and 9:00 AM, all 400 users of a ProjectWise installation log into the system.  All the audit trail records for these logins are recorded.  We want to keep our audit trail records for a week, so a week later the ProjectWise maintenance routine runs to archive these login audit trail records.  The problem is that while we are archiving these records we are also writing new records to the table as the users begin logging in for the current day.  The outcome of this is that we have a bottle neck on the DMS_AUDT table and the performance of the systems suffers.

In the later builds of ProjectWise XM and in ProjectWise V8i there is a second setting that allows for an offset time.  This offset time allows the ProjectWise Admin to shift when the Audit trail records are moved to the archive table.  For example, if we have a 12 hour offset, the Audit Trail records from 8:00 AM would be getting moved to the Archive table at 8:00 PM.  By setting the offset hopefully the audit trail archive process moves the bulk of the records during a time when the system usage is low.

 

As I mentioned the offset value can be set in the Data Source Settings dialog by the ProjectWise admin, but is can also be set in the dmskrnl.cfg file by setting the variable AuditArchiveOffset.  This variable needs to be set in seconds, so for example and 12 hour offset would be

AuditArchiveOffset=43200

For more information on this see the ProjectWise Administrator help under:

Database Management > Setting Up Audit Trail > Truncating Audit Trail Records

And

ProjectWise Administrator Reference > Datasource Icons and Other References > Setting tab of the Datasource Properties Dialog (Datasource Settings)         

Database Indexes:  

Because the ProjectWise Integration server relies heavily on the database, having the appropriate indexes can make a huge difference in performance.  Various workflows and user environments may require additional indexes to be added to the ProjectWise database.  Please note that any additional indexes added to the ProjectWise database will be removed when the DMS Convert application is use to upgrade the database.  Because additional indexes will be dropped it is important to save the Create statements so that they can be used in the future if they have not been incorporated into the product and included as part of the DMS Convert.  I would encourage your feedback if there is an index that we are missing as part of the product.

In ProjectWise XM a significant effort was made to improve performance by adding clustered indexes to many of the tables.  For more information on clustered indexes and some of the primary tables that should use these indexes see the following tech note: 

http://communities.bentley.com/Products/ProjectWise/w/ProjectWise__Wiki/using-clustered-indexes.aspx

In ProjectWise we have set thresholds on longing running SQL Statements.  If a SQL Statement exceeds the threshold it is reported as a warning in the log file.  The following is an example of one of these log messages.

2009-04-21 14:59:00,452 WARN  [0x00001a64] pwise.database - Statement execution took 1.6993 seconds, exceeds performance threshold of 1.0000 seconds. 'select o_projectno,o_parentno,o_envno,o_type,o_classid,o_instanceid from dms_proj order by o_projectno'

These log messages are an excellent starting point to analyzing performance issues.  Using tools such as the Database Tuning Advisor in SQL Server this query can quickly be analyzed to see if any additional indexes should be added to make the query run faster.

Migrating to SQL Server 2005:

As part of the V8i release the ProjectWise Integration Server is now able to use the SQL Server SQL Native Client and SQL Native Client 10 for SQL Server 2008.  Although ProjectWise doesn't take advantage of newer features offered by these ODBC connections, performance testing showed subtle performance improvements over the MDAC connection.

On the topic of using SQL Server 2005, I had discussed in an early post making sure that the database is set to 2005 compatibility level.  If you are upgrading a database from SQL Server 2000 to SQL Server 2005 the compatibility level will automatically be set to SQL Server 2000. 

http://communities.bentley.com/Other/Old_Site_Member_Blogs/Bentley_Employees/b/allen_brown_bentleys_blog/archive/2009/02/24/are-you-using-sql-server-2005-are-you-really-using-sql-server-2005.aspx

With all the enhancements and performance improvements it is worth it to set the compatibility mode to 2005.  ProjectWise XM and V8i are both certified on SQL Server 2000 and 2005.  V8i is also certified on SQL Server 2008.

As I mentioned we are constantly looking for ways to improve the performance of ProjectWise and also to validate that new functionality doesn't hinder the performance.  Stay tuned for Part 2.