You are currently reviewing an older revision of this page.
Sometimes performance issues reported with the software can be alleviated by re-indexing the project database. In these cases, performance has degraded over time, and is not the result of changes to the software's configuration or network performance.
If creating and using a new project database attached to the same SQL Server does not significantly improve performance, then the problem is not with the existing database and re-indexing it will not help.
If re-indexing does help, it is worth checking the Auto Shrink property of the database and disabling it to possibly prevent future performance degradation.
The important part of the re-index operation is choosing the right fill factor, otherwise the re-index could slow things down further. The appropriate fill factor really depends on the ratio of read / write transactions within the database; high fill factor = quicker read and lower fill factor = quicker write. Fill factors are typically between 70% (very high write) to 95% (very high read). One way to think of fill factor is as pages in an address book - the more tightly you pack the addresses the harder it is to change them, but the slimmer the book. The script below specifies a fill factor of 85%, as seen in the following excerpt: SET @fillfactor = 85
Open SQL Server Management Studio (SSMS) and connect to the server hosting the database to be re-indexed. The server hosting the database currently being used as the project database by promis•e or Bentley Substation can be found in the "Server name" field on Setup > Project Database.
Click the New Query button in SSMS..
Paste the script below into the new query window.
Replace "MyDatabase" in the script with the actual name of the database to be re-indexed. The name of the database currently being used as the project database by promis•e or Bentley Substation can be found in the "Database" field on Setup > Project Database.
If the results are not satisfactory, the script can be run again with a different fill factor value. These values are typically b
If the operation improves performance, consider running this operation regularly.
Script credit goes to Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor by Pinal Dave
USE MyDatabaseDECLARE @TableName VARCHAR(255)DECLARE @sql NVARCHAR(500)DECLARE @fillfactor INTSET @fillfactor = 85DECLARE TableCursor CURSOR FORSELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableNameFROM sys.tablesOPEN TableCursorFETCH NEXT FROM TableCursor INTO @TableNameWHILE @@FETCH_STATUS = 0BEGINSET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'EXEC (@sql)FETCH NEXT FROM TableCursor INTO @TableNameENDCLOSE TableCursorDEALLOCATE TableCursorGO
Disable Auto Shrink to avoid performance degradation
Backup Project
Creating and Updating the Project Database
Reorganizing and Rebuilding Indexes
How to: Back Up a Database (SQL Server Management Studio)