Background 

From the Microsoft article referenced below ("Defragmenting Indexes")...

"Maintaining indexes is a primary duty of Database Administrators, because appropriate indexes are critical to maintaining high performance in any relational database, and proper maintenance is required to avoid excessive fragmentation. In case that's not clear enough, let me emphasize: index fragmentation can dramatically degrade performance."

"Indexes with less than 10% average fragmentation should usually be left alone, indexes with fragmentation between 10% and 30% should be reorganized, and indexes with fragmentation above 30% should be rebuilt."

 

Sage Estimating utilizes at least three SQL databases. They include the following:

  1. Sage 'Cost' Database - includes Phases, Items, Resources, Crews, Assemblies, Models, etc.
  2. Sage Estimates Catalog - includes Estimates.
  3. Sage Estimates Address Book - includes companies and contacts.


Installed with the Sage Estimating software suite is the Estimating Management Console or 'EMC'. The EMC includes some tools for maintaining the databases. These tools work only when Sage Estimating is installed on the same server where SQL is installed. 


In an enterprise deployment, it is common (and recommended) for the SQL Server instance and databases to reside on a separate SQL Server.


Example Client-Server Architecture:



In the above architecture, the tools in Sage EMC cannot run against the SQL databases because the Sage Estimating software is (typically) installed only on the Workstations (or RDS Session hosts) and not the SQL Server.


Maintenance Recommendations

Sage Estimating tools through the EMC performs the following actions against the Sage Estimating databases.

  • Rebuilds Index
  • Updates Statistics


In a Client-Server environment, a maintenance plan on the SQL Server is required to complete the same optimization tasks. There are many resources available. The following plans are what Eos has recommended to our clients.


Rebuild Index

Maintenance Plan summary:

The following maintenance plan rebuilds the indexes for the Sage Estimates and 

Sage Estimates Address Book SQL Databases on a nightly schedule, Mon - Fri at 9 PM CT.


Maintenance Plan Wizard screenshots:










Update Statistics

Maintenance Plan summary:


The following maintenance plan updates the statistics for the Sage Estimates and 

Sage Estimates Address Book SQL Databases on a nightly schedule, Mon - Fri at 9:30 PM CT.


Maintenance Plan Wizard screenshots:











For the SQL DBA

See the Sage Estimates SQL DB > [dbo].[dba_indexDefrag_sp] stored procedure for details about the optimization procedure. Note that it follows Microsoft guidelines on whether an index should be left alone, defragmented, or rebuilt.



Additional recommendations include, but are not limited to the following:

The End.