Page Comparison

...

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

Plant Applications Database Purge

Purging the plant applications database should only be done by someone who knows the configuration and business rules of the system very well. Careful investigation and consideration should be done prior to making decisions about what to purge and when to purge. There are many different versions of purge utilities. The following links will bring you to the GE knowledge base articles that describe the purge utility and include downloads of the various versions. Depending on the size of the database, the initial purge could take several hours and should be done offline.

It is recommended that you perform backups prior to purging and develop a test plan to validate the system after the purge to identify any issues that it may have introduced.

Plant Applications Database Purge 2.0 - Version 215.8, 4.2.3, 4.3.1, 4.4, 4.4.1 | GE Customer Center

Plant Applications Database Purge - Compatible with Plant Apps 5.0, 6.1 & 6.2 | GE Customer Center

SQL Maintenance Recommendations for Plant Applications Database

1. SQL Maintenance Recommendations for Plant Applications Database based on Ola Hallegren solution.

2. The attached document is old solution which may be used when #1 can't be deplyed due to e.g. local customer's policy. Please reference the attached document for more information on SQL table fragmentations, SQL Statistics, and SQL Execution Plans.

Solution

Using Ola Hallengren Solution for Index Maintenance

Ola Hallengren's scripts are simple to configure and can be used to optimize SQL Server indexes:

• Index Maintenance: The script performs index defragmentation, index rebuilding and update statistics on all indexes in a database.

• Index Optimization: The script analyzes index fragmentation and recommends indexes to rebuild or reorganize.

• Index Verify: The script performs index verification and reports any corrupted indexes.

By using Ola Hallengren's scripts, you can automate and simplify index maintenance tasks, improve database performance, and ensure the integrity of your indexes.

Below is the Configuration of Maintenance Solution:

1. CommandLog.sql: Table to log commands

URL to Download: https://ola.hallengren.com/scripts/CommandLog.sql

2. CommandExecute.sql: Stored procedure to execute and log commands

URL to Download: https://ola.hallengren.com/scripts/CommandExecute.sql

3. IndexOptimize.sql: Stored procedure to rebuild and reorganize indexes and update statistic

URL to Download: https://ola.hallengren.com/scripts/IndexOptimize.sql

Usage for IndexOptimize Proc: Refer https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

One of the Best ways of Index and Statistics Maintenance is having as below:

• Update only Modified Statistics on Daily Basis and Log to Table “CommandLog”.

EXECUTE master.dbo.IndexOptimize --
	@Databases = 'SOADB', --List of Databases can be passed as CSV String
	@FragmentationLow = NULL,
	@FragmentationMedium = NULL,
	@FragmentationHigh = NULL,
	@UpdateStatistics = 'ALL',
	@OnlyModifiedStatistics = 'Y',
	@LogToTable = 'Y';

• Index Re-Build and Re-Organize along on Weekly Basis based on the Fragmentation Percentage.

EXECUTE master.dbo.IndexOptimize --
	@Databases = 'SOADB',
	@FragmentationLow = NULL,
	@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
	@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
	@FragmentationLevel1 = 5,
	@FragmentationLevel2 = 30,
	@UpdateStatistics = 'ALL',
	@OnlyModifiedStatistics = 'Y',
	@LogToTable = 'Y';

We can use master.dbo.CommandLog Table to Analyze the frequency of Indexes and Statistics Updation.

SQL minimum configuration

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

...

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

...

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:

SQL logs

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

...

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