Page Comparison

SQL Maintenance

A Proficy Database should have the following Maintenance Plans:

...

Your Maintenance Plan (Update statistics) is all set.Save it

SQL minimum configuration

Minimum requirements (TCP/IP and PIPEs enabled, Port 1433)

SQL Performance Monitoring

Identify key DB tables

Proficy Plant Applications is made up of 100's if not 1000+ tables. Below are some of the key tables that grow larger and a used very often. If their indexes have high fragmentation Plant Applications will experience poor performance.

DBCC

The primary purpose is to check for consistency errors, and should ideally be run every day.
The basic syntax is:
DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS
DBCC CHECKDB('DatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS
NO_INFOMSGS prevents an excessive number of informational messages from being generated. There are several other options, but this is the syntax you should aim to use as it performs all integrity checks.
This may take a long time on large databases and you may want to specify the PHYSICAL_ONLY option. This checks physical on-disk structures, but omits the internal logical checks. The syntax is:
DBCC CHECKDB ('YourDatabase') WITH PHYSICAL_ONLY

Determine level of Index Fragmentation

A Table is fragmented when it has empty holes in the pages and extents that it takes up.
Fragmentation occurs as a result of page splits and deletion of rows. This could happen is a large number of rows are deleted. Because primary keys are not supposed to be reused, new data is added to the physical end of the table, leaving a hole that slows down accessing the data. To resolve the fragmentation, you can rebuild the clustered index.
Rebuild: An index 'rebuild' creates a fresh, sparkling new structure for the index. If the index is disabled, rebuilding brings it back to life. You can apply a new fillfactor when you rebuild an index. If you cancel a rebuild operation midway, it must roll back (and if it's being done offline, that can take a while).
Reorganize: This option is more lightweight. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it's able to just stop where it is (it doesn't have a giant operation to rollback).
In version 2 of this document we will provide code and discuss the following:

• Index fragmentation (specs -> warnings)

• Fragmented count

• Page Counts

• Table Record Counts

• Index level and depth

Anchor
_Toc469044940 _Toc469044940

Re-Organize and/or Rebuild Indexes

In order to reduce fragmentation, we will have to reorganize or rebuild the indexes. Choosing between reorganizing or rebuilding depends on the query results.For heavily fragmented indexes a rebuild process is needed, otherwise index reorganization should be sufficient. The following table summarizes when to use each one:

Anchor
_Toc469044941 _Toc469044941

SQL logs

SQL server log files are found in the Management section of the Object Browser of SQL.

...

You need to be a member of the security admin role in order to access these logs.

...

Anchor
_Toc469044942 _Toc469044942

Table growth

More will be provide on table growth in version two. Basically key tables should be monitored for growth so that database/disk sizing can be predicted. It can also lead to decisions on purging.
A plan to compare key table growth from week to week or month to month might provide good information.

Anchor
_Toc469044943 _Toc469044943

SQL Profiler

SQL Server Profiler is a graphical tool that allows you to monitor and collect server events such as:

...

• SQL Profiler is useful for troubleshooting spLocal_* stored procedures created specifically for sites.

• Allows you to see the values of the input parameters

• Allows the capture of run statistics.

• Open an existing trace or create a new one.

• Save to a file.

• Add filters to make traces more efficient and easier to read.

• Most useful filters are:

  • LoginName=comxclient

  • TextData Like %splocal%

  • ApplicationName Like Proficy%

Anchor
_Toc469044944 _Toc469044944

spSupport_Blocking

Plant Applications is installed with a Stored procedure that will determine whether there is Blocking occurring in the database. There may be a need to have heightened permissions to execute this. Below is an example of running it and observing that there are no blocks.

...

Real Time Information Portal Maintenance

"Portal" has reached its end of life with GE. If you are still operating Proficy Portal then you should back up your configuration files regularly.
Example of a Backup batch command for Portal files.
@echo off
rem
rem copy portal config and screens
rem
cd "C:\Program Files\GE Fanuc\Proficy Portal\webapps\"
xcopy /E /q /y C: \\remotestorageshare\MESbackups\MESPortal
Executed daily via Scheduled job Backup_Portal.job Anchor_Toc469044988_Toc469044988

Historian Server Maintenance

The Historian server is the central point for managing all of the client and collector interfaces, storing and (optionally) compressing data and retrieving data. All tag data (numbers, strings, BLOBs) are stored in a proprietary format in Data Archives. Each Data Archive represents a specific time period of historical data. Anchor_Toc469044989_Toc469044989

Routine Maintenance

On a regular schedule, examine and analyze the system performance indicators displayed on the System Statistics screen of the Historian Administrator, as follows:

...