**Please Note: For issues setting up Clustered Indexes, please consult your internal Database Administrator. The following information is for background and reference purposes.
A Clustered Index is an index on a table or view that reorganizes the physical data of a table or view into sequentially ordered rows. The danger with using Clustered Indexes is that since the data is sequentially ordered, inserts into the table will take a slight performance hit due to the reordering of the data. For example, if a record is added to the table that is close to the beginning of the sequentially ordered list, any records in the table after that record will need to shift to allow the record to be inserted.
Indexes (including Clustered) are organized as B-Trees. Unique keys make smaller B-Trees and therefore allow look-ups in the database to be more efficient. Clustered Indexes depend on uniqueness in the key to organize the data. If a non unique key is used for a Clustered Index SQL Server will generate an additional key column for the index to ensure that each leaf in the B-Tree is unique. Although this approach speeds up the look up capabilities it takes up more physical disk space and more time when inserting data. For this reason it is best to apply Clustered Indexes to unique, static key columns.
Indexes in general can improve the performance of any database. Indexes either physically or virtually order the data so that the database can locate and retrieve the data faster. Clustered Indexes in Microsoft SQL Server physically reorder the data for a given table and a given key. By reordering the data sequentially for a given key column or key columns in a table, Clustered Indexes can potentially lead to significant increases in the performance of the database. The increased performance of the database is directly proportionate to the increased performance of the ProjectWise Server accessing the database. In performance testing of a ProjectWise Server, increases of more than 30% were obtained just by adding Clustered Indexes to the database.
**Please note: Individual performance improvement may vary less than or greater than the testing done on the ProjectWise Server used in this document. The 30% improvement should not be implied as an expected result in all cases.
Also, it is important to note that these options for creating Clustered Indexes apply to all PW 8.x releases up to and including ProjectWise 8.9.3.x. Future releases are expected to have the Clustered Indexes created automatically.
There are three main restrictions in creating Clustered indexes. First, each table or view may only contain one Clustered Index. Second, Clustered Indexes can not be used with tables containing Large Object, LOB, data types. Lastly, when applying Clustered Indexes to a view the Clustered index must be created before creating any other indexes. This restriction does not apply for table indexes.
**Please note: When converting your database in the upgrade from ProjectWise V8 to XM, the Clustered Indexes will need to be re-created as they will be lost in the conversion.
In general tables can contain 255 Non-Clustered indexes. When a Clustered Index is applied the table is then limited to 254 Non-Clustered Indexes. Typically Non-Clustered indexes will rely on the table Row Id or RID for doing look-ups. If a Clustered Index exists for a table, Non-Clustered indexes will rely on the Clustered Index instead of the RID. If you create a Clustered Index from an existing index in SQL Server 2005 you will receive a message box indicating that other indexes for that table will be rebuilt to make use of the Clustered Index.
Primary Key Constraints are automatically created as Clustered Indexes if another Clustered Index does not exist for the given table. Primary Key constraints make good Clustered indexes because they are unique indexes into the table.
A general rule of thumb for creating Clustered Indexes is (S.U.N) Static, Unique and Narrow. This acronym implies that Clustered Indexes work best on tables containing static data or data that is changed infrequently. Lookup or metadata tables are a perfect example of this. The column or columns contained in the index should also create aunique key. As mentioned above if the column or columns aren't unique SQL Server will create an additional key to force uniqueness across the rows. The creation and maintenance of this additional key takes up more disk space and requires additional work for the database when inserting records. The narrow key word of the acronym implies that the indexes should be made of as few columns as possible. For instance a unique primary key makes a great fit for a Clustered Index.
The following indexes in ProjectWise have been identified as prime candidates for Clustered Indexes. This list is not exhaustive, but in testing, changing these indexes to Clustered Indexes gave the biggest performance gain.
In reviewing these indexes, notice that most of them fall exactly into the S.U.N. paradigm. For example i_dms_proj_1, i_dms_rtv_1, i_dms_stor_1 and i_dms_user_1 are all Unique Primary Keys for their respective tables. They are fairly static tables and containing only one column they are very narrow.
So what about the other indexes in the list? If we look at the i_dms_doc_1 and the i_dms_link_1 indexes we find that the indexes include two columns. They are not as narrow as the previously mentioned indexes, but the combination of the two columns does form a unique key to the data stored in the table. Also, once a document or a link is added to ProjectWise it can be thought of as fairly static.
There is one other index left to discuss. The i_dms_acce_1 index is not the best fit for the S.U.N. rules because it is a wide index made up of all the columns in the table. The reason this index works as a Clustered Index is because ProjectWise always queries the DMS_ACCE table ordering the records the same way they are ordered by the Clustered Index. By storing the records in a sort list a performance gain is achieved because each query of the DMS_ACCE table does not spend time re-sorting the records. Combining all the columns in this table will form a unique key and for most installations of ProjectWise, the addition and removal of users is infrequent making this table static.
Clustered indexes can be created from an existing index or created as a new index when initially creating indexes on a table or view. Once again if a Primary Key constraint is used it automatically is assigned as a Clustered Index.
To create a Clustered Index from an existing index:
Use the Close button to close the dialog and accept the changes
Creating a Clustered Index in SQL Server 2005 is slightly different because of the new database interface.
In SQL Server 2005 to create a Clustered Index from an existing table do the following:
CREATE CLUSTERED INDEX i_dms_rtv_1 ON dms_rtv (o_rtvno);
ALTER INDEX i_dms_rtv_1 ON dms_rtv REORGANIZE;
Kimberly Tripp - BLOG on Clustered Indexes:
"MCSE, Exam 70-229, Microsoft SQL Server 2000 Database Design and Implementation Training Kit", by Robert Sheldon and Ethan Wilanshy, Copyright 2001, Microsoft Corporation
Product TechNotes and FAQs
ProjectWise TechNotes And FAQs
Bentley Technical Support KnowledgeBase
Bentley LEARN Server
Bentley's Technical Support Group requests that you please confine any comments you have on this Wiki entry to this "Comments or Corrections?" section. THANK YOU!