Product(s):	Promis.e, Bentley Substation
	Version(s):	through 08.11.13.140
	Environment:	Microsoft SQL Server
	Area:	Database Platform Support
	Subarea:	N/A

Background

Sometimes performance issues reported with the software can be alleviated by rebuilding the indexes in 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 [[Creating and Updating the Project Database|new project database]] attached to the same SQL Server does not significantly improve performance, then the problem is not with the existing database and rebuilding indexes will not help.

If rebuilding indexes does help, it is worth checking the Auto Shrink property of the database and [[Disable Auto Shrink to avoid performance degradation|disabling it to possibly prevent future performance degradation]].

The important part of the index rebuilding 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

Steps to Accomplish

1. [[Backup Project|Back up the projects]] and/or back up the project database in case something catastrophic occurs during the re-index.
   
   

2. 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.

3. Click the New Query button in SSMS.

4. Paste the script below into the new query window.

5. 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.

6. Click the Execute button to execute the script.

7. If the results are not satisfactory, the script can be run again with a different fill factor value.  Fill factor values are typically between 70 and 95.

If the operation improves performance, consider running this operation regularly.

Script

Script credit goes to Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor by Pinal Dave

USE MyDatabase
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 85
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

See Also

[[Disable Auto Shrink to avoid performance degradation]]

[[Backup Project]]

[[Creating and Updating the Project Database]]

External Links

Rebuilding & Reorganizing all indexes from single database in a one query

Reorganizing and Rebuilding Indexes

How to: Back Up a Database (SQL Server Management Studio)

Original Author:

John Spathaky

keywords: reindex, re-index, timeout, delay, performance