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: EXEC sp_executesql N'DBCC DBREINDEX (@tbl, @idx, 85)',
Execute the script below when the database is not in use. Replace "MyDatabase" with the actual name of the database to be re-indexed before executing the script. The script can be executed using SQL Server Management Studio.
If the results are not satisfactory, the script can be run again with a different fill factor value.
If the operation improves performance, consider running this operation regularly.
DECLARE @db_name nvarchar(1000)SET @db_name=N'MyDatabase'EXEC ('USE ' + @db_name)DECLARE @index_name nvarchar(1000)DECLARE @table_name nvarchar(1000)DECLARE @ind_id intDECLARE index_cursor CURSOR forSELECT name, object_name(id), indid FROM sysindexes WHERE id > 1000 and indid >=1 and ROWS > 0 ORDER BY object_name(id), indid, nameOPEN index_cursorFETCH NEXT FROM index_cursor INTO @index_name, @table_name, @ind_idWHILE @@FETCH_STATUS = 0BEGIN IF @ind_id = 1 BEGIN EXEC sp_executesql N'DBCC DBREINDEX (@tbl, @idx, 85)', N'@idx nvarchar(1000), @tbl nvarchar(1000)', @index_name, @table_name END EXEC sp_executesql N'DBCC INDEXDEFRAG (@db, @tbl, @idx)', N'@db nvarchar(1000), @idx nvarchar(1000), @tbl nvarchar(1000)', @db_name, @index_name, @table_name FETCH NEXT FROM index_cursor INTO @index_name, @table_name, @ind_idENDCLOSE index_cursorDEALLOCATE index_cursor
Disable Auto Shrink to avoid performance degradation
Backup Project
Creating and Updating the Project Database
Reorganizing and Rebuilding Indexes
DBCC DBREINDEX (Transact-SQL)