SQL Server Configuration

Databases, transaction logs, tempdb, non-clustered indexes, and back-ups should be installed on separate hard drives. The following RAID specifications are recommended when running in a Plant Applications environment without SOA entities and not using the Client or Workflow. Additional requirements may be necessary for a Unified Manufacturing Database.

  • SQL database: RAID 0+1, RAID 1, or RAID 5 or higher
  • SQL transaction logs: RAID 0+1 or RAID 1 and should be a separate physical device
  • SQL tempdb: RAID 0+1 or RAID 1
  • SQL indexes: RAID 0+1 or RAID 1
  • Back-ups: RAID 0 (no fault tolerance, but faster writes), or RAID 5 or higher
Important: For a current listing of the supported service packs and hot fixes released by Microsoft, refer to the article KB9920 in the GE support site (https://ge-ip.force.com/communities/CC_Home).

For up-to-date instructions and the latest information on installing SQL Server, visit the MSDN web site: https://msdn.microsoft.com/en-us/library/bb545450.aspx

Additionally, the following configuration is recommended:
  • Use Mixed Mode security.
  • Exclude the database files from anti-virus scanning.
  • Use multiple processors for every installation.
  • Enable the default backup job, which is disabled during the Plant Applications Server installation, or create a maintenance plan.

Multiple Databases on a Single SQL Server

Multiple Plant Applications databases cannot be installed in the same instance of SQL Server because the ‘proficydbo’ account uses a default database and there can only be one default database. To use multiple Plant Applications databases on a single SQL Server, you must install multiple instances of SQL Server.

Table Statistics

When an individual table query experiences significantly reduced performance or selects the wrong index, the first thing that can be done is to update the table statistics. The query optimizer in SQL Server bases its choice of indexes and execution plans on the current table statistics. If the table statistics are not representative of the actual data in the table, the query optimizer may make bad decisions.

By default, SQL Server automatically updates table statistics periodically, but the automatic update is not always accurate. Furthermore, the default statistics are calculated based on a small subset of the rows in a table, which may not always give the best results. SQL Server chooses a small subset for performance reasons because calculating statistics can be a time-consuming and resource-intensive task, depending on the size of the table. Manually updating the statistics based on the full set of rows in the table or even based on a larger percentage of rows (for example, 50%) may improve query performance.

Note: Care should be taken to ensure that the statistics update is done during low server utilization periods.

For Plant Applications, the User_Defined_Events table should have the statistics regularly updated, which can be accomplished using the UPDATE STATISTICS command. For example: UPDATE STATISTICS User_Defined_Events WITH FULLSCAN.

Database Sizing

The database should be initialized to the full database size. This will reduce fragmentation within the database caused by incremental database growth. It will also prevent the periodic performance slowdowns that occur when the database size is incrementally increased by SQL Server. SQL Express is not supported because it limits database size to 2 GB, which is below the minimum required for Plant Applications.