Agresso – missing system indexes

Job details

Name:	Agresso – missing system indexes
Platform:	Sqlserver
Category:	Agresso
Description:	Checks if there are any missing system indexes in the agresso database by comparing records in dbo.asysindex and sys.indexes tables.
Long description:
Version:	1.31
Default schedule:	45 7 1-5 *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2012′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Name	Default value	Description
ignore indexes		List of agresso system indexes (separated by comma) which will be ignored from beeing checked.
database name	agr%	Name of the database where the Agresso application is installed. You can specify a list of databases (separated by comma). You can use % (percent sign) to represent wild card characters.
return status	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when missing agresso system indexes.

Purpose:
The purpose of this job is to systematically check for any missing system indexes in Agresso databases, specifically by comparing records between the dbo.asysindex table and sys.indexes.
Why: This job is critical for maintaining the optimal performance and integrity of the Agresso database systems. Missing indexes can lead to degraded performance and slow query execution times. It helps in ensuring that all necessary system indexes are present and accounted for, streamlining database operations.
Manual checking: You can manually check for missing system indexes via SQL commands by comparing the contents of system tables:

SELECT * FROM dbo.asysindex

SELECT * FROM sys.indexes

Name: Agresso – Missing System Indexes
Version: 1.31
Description: Monitors and reports on missing system indexes within the Agresso database platforms by checking discrepancies between essential system tables.
Company: dbwatch.no
Default Schedule: Scheduled to run on the first five days of the month at 07:45 AM.

Dependencies: This job depends on several database objects and procedures to function properly:
agresso_missing_sys_idx_tab – Holds missing index information.

SQL Operations:
Creates necessary tables and a stored procedure to handle the locating of missing system indexes.

Report Details: The job outputs a formatted report detailing missing indexes, databases checked, and those indexes that are deliberately ignored.
Tables Presented in Reports:
Missing System Indexes: Lists all missing Agresso system indexes.

Scheduling: Outlined to execute during specific time frames, ensuring checks are done regularly without manual intervention.
Alerting: Configurations within the job help in sending out alerts based on the priority and severity (e.g., alarm, warning) dictated by the missing indexes status.

Compatibility: Designed to run on Microsoft SQL Server instances (version greater than 2012) including Azure SQL Managed Instances that include a SQL engine, aligning with more modern database environments.