Two SQL jobs are supplied by OSIsoft when installing an AF server
- "OSIsoft Backup (> PIFD)" which is the template for backing up the PIFD and PIFD_distribution databases with their transaction logs.
- "OSIsoft AF Maintenance (PIFD)" which consists of 5 steps:
- REINDEX, runs the stored procedure usp_reindex
- 2. UPDATE_STATISTICS, run the stored procedure sp_updatestats
- 3. REBUILD PATH CACHE, executes the stored procedure usp_AFElementCache_refresh
- 4. DELETE ORPHANED ELEMENTS, executes the stored procedure usp_BackroundJob_DeleteOrphanElements
- 5. BACKGROUND JOB, executes the stored procedure usp_backroundjob
The job "OSIsoft AF Maintenance (PIFD)" is deactivated by default.
Note that the reindexing which is the 1st step can also be executed from the AFDiag tool.
OSIsoft draws attention to the fact that the performance of the AF bases is affected during reindexing:
On the development platform, this job is activated and runs on Sunday, so during periods of inactivity.
The execution log of this job shows that the indexing step lasts between 1 and 2 hours, whereas the following steps last between a few seconds and a few minutes.
During a manual launch of this job, we found that the connections to the AF bases become very long (several minutes) and that the ACE processes (for example) fail due to lack of successful connection:
A solution based on exchanges PISquare = https://pisquare.osisoft.com/ideas/1017, would be to modify the job so that the 1st step = REINDEX, uses a new stored procedure that traces the indexes of all tables like usp_reindex (Initial), but by running the DBCC DBREINDEX command only if the fragmentation rate is high.
Note that Microsoft recommends (https://msdn.microsoft.com/en-us/library/ms189858.aspx):
- Use the "ALTER INDEX REBUILD WITH (ONLINE = ON) command for a fragmentation rate> 30%
- Use the "ALTER INDEX REORGANISE" command for a fragmentation rate between 5 and 30% (5% <rate <= 30%).
- No action for a fragmentation rate <5%
Note also that according to the Microsoft = https://msdn.microsoft.com/en-us/library/ms181671.aspx link, the DBCC DBREINDEX command is an OFFLINE operation, that is, a Lock is placed on the table corresponding to the index being rebuilt. Unlike the controls:
- "ALTER INDEX REORGANISE" which always runs online, and
- "ALTER INDEX REBUILD" that runs in line with the following option "WITH (ONLINE = ON)"
In fact, the solution would be to rebuild the index both conditionally (depending on the rate of fragmentation), and using commands running ONLINE to commission the maintenance plan without jeopardizing the availability of AF databases.
Did someone experiment a maintenance plan on that way?
Does that way seem to be a good solution?