Stored procedures for the AF Maintenance job

The AF Maintenance job executes the following major stored procedures, each of which are defined in following sections:

• BackgroundJob

• RebuildPathCache

• Reindex

• UpdateStatistics

  Note: For details on security requirements for any of the stored procedures, review Security requirements for SQL Server maintenance jobs .

Understand AF Maintenance job parameters

There are two input parameters you can use with the AF Maintenance job:

• @delayMinutes

  Use @delayMinutes to ensure any objects that were saved a specified number of minutes prior to running the stored procedure get cleaned up and deleted. A very minimal @delayMinutes deletes all applicable objects.

• @skipDatabase

  Use @skipDatabase to skip a specified database.

The BackgroundJob stored procedure is configured to perform multiple functions. The following list describes the actions involved in this stored procedure:

• Removes any AF databases marked for deletion. AFDatabases is a database instance on any PI AF server; it is not the PI AF SQL Server database. When an instance of AFDatabases is deleted, it is marked as “deleted” in the PI AF SQL Server database. Running the BackgroundJob allows the actual deletion process to occur in the background, minimizing user interruption when a database is deleted. This occurs because all the rows in the AFDatabases must be deleted in addition to the AFDatabases record, which can take a period of time from minutes to hours.

• Deletes orphaned element objects, such as those created when the child element was saved but the parent element was not. This job also cleans up orphaned element references in which the parent element was saved but the child element was not.

• Deletes orphaned files, which are files that cannot be referenced by way of a parent object.

• Removes entries in the findchanges_at table that are older than a specified number of days. This number of days value is assigned to the clearchangetables variable. The default value is seven (7) days. You can assign a different value to this variable when executing the BackgroundJob stored procedure. This table holds references to all objects that have been inserted, updated and/or deleted.

• Deletes orphaned security descriptors, which are security descriptors that cannot be referenced by way of a parent object.

• Deletes orphaned NotificationRuleSubscriber object(s)

You can run the BackgroundJob stored procedure using any of the following approaches:

• Schedule the AF Maintenance job to run at a specific time via the SQL Server Management Studio

• Manually run the AF Maintenance job via the SQL Server Management Studio

• Manually run the BackgroundJob stored procedure independent of the AF Maintenance job

Code example: BackgroundJob

The following example uses a T-SQL command to run the BackgroundJob stored procedure.

1. EXEC [dbo].[usp_backgroundjob] ;

Learn about rebuilding the element path cache

The path cache is used to locate elements and attributes quickly during a search based upon one object's relationship to another through AF SDK methods, such as FindElementsByPath and FindAttributesByPath. The path cache can become outdated after significant data insertions, edits, and deletions. Rebuilding the path cache can improve AF Server performance.

You can rebuild the element path cache in the PI AF SQL Server database using any of the following approaches:

• Schedule the AF Maintenance job to run at a specific time via the SQL Server Management Studio.

• Manually run the AF Maintenance job from SQL Server Management Studio.

• Manually run the stored procedure for rebuilding the path cache independent of the AF Maintenance job.

Code example of rebuilding the path cache

The following is an example of the T-SQL command used to execute a stored procedure that rebuilds the path cache info:

EXEC [dbo].[usp_AFElementCache_refresh] ;

Learn about reindexing the PI AF database

Indexes are used in the PI AF SQL Server database to enhance PI AF server performance. Indexes can become fragmented as objects are inserted, updated and/or deleted. Fragmented indexes may slow the PI AF server’s response time. Reindexing the PI AF SQL Server database cleans up fragmented indexes, allowing the PI AF server to become more responsive.

For additional information regarding reindexing, see the following articles:

• KB00674 - Reindexing to improve the performance of the SQL Database for PI AF

• Microsoft's Technet article - Reorganizing and Rebuilding Indexes

You can reindex the PI AF SQL Server database using any of the following approaches:

• Schedule the AF Maintenance job to run at a specific time via the SQL Server Management Studio.

• Manually run the AF Maintenance job with the SQL Server Management Studio (if you have the required permissions to access SQL Server jobs).

• Manually run the Reindex stored procedure independent of the AF Maintenance job.

• Use the AF Diagnostics (AFDiag) utility to reindex the database.

Code example: Reindex

The following is an example of the T-SQL command that can be used to execute the stored procedure that reindexes the database:

EXEC [dbo].[usp_reindex] ;

Learn about the UpdateStatistics stored procedure

SQL Server uses statistics when it queries the contents of the PI AF SQL Server database. Statistics provide information about the distribution of values in tables and indexes, which is used by SQL Server to query the data in the PI AF SQL Server database. Statistics can become outdated after significant data insertions, edits, and/or deletions. Updating statistics can improve PI AF server's performance.

For detailed information on database statistics, refer to the Microsoft article Statistics.

You can update the statistics in the PI AF SQL Server database using any of the following approaches:

• Schedule the AF Maintenance job to run at a specific time via the SQL Server Management Studio.

• Manually run the AF Maintenance job via the SQL Server Management Studio.

• Manually run the UpdateStats stored procedure independent of the AF Maintenance job.

Code example: Update Statistics

The following is an example of the T-SQL command that can be used to execute the stored procedure that updates the database statistics.

EXEC [dbo].[usp_updatestats] ;