Agresso – not registered indexes

Job details

Name:	Agresso – not registered indexes
Platform:	Sqlserver
Category:	Agresso
Description:	Checks if there are any indexes in the agresso database which are not registered in the dbo.asysindex or dbo.aagindex tables.
Long description:	Checks if there are any indexes in the agresso database which are not registered in the dbo.asysindex or dbo.aagindex tables.
Version:	1.31
Default schedule:	25 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 user 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 user indexes.

Purpose: This job is designed to monitor the Agresso database for any indexes that are not registered in the dbo.asysindex or dbo.aagindex tables.
Why: Indexes that are not registered can lead to inefficiencies in database operations and may affect the performance. Ensuring all indexes are accounted for helps in maintaining the integrity and performance of the database. Unregistered indexes can go unnoticed without such monitoring, leading to potential issues in data handling and retrieval.
Manual checking: This check can be performed manually through SQL query on the database to list all indexes and comparing them against entries in the dbo.asysindex and dbo.aagindex tables:

SELECT name FROM sys.indexes WHERE name NOT IN (SELECT index_name FROM dbo.asysindex) AND name NOT IN (SELECT index_name FROM dbo.aagindex);

“agresso_not_registered_idx_tab”: Stores information on not registered indexes.
“agresso_not_registered_idx_db_info”: Contains status and info about the Agresso databases.
“agresso_not_registered_idx”: The main procedure involved in checking and updating the registry of indexes.
“agresso_ignore_not_registered_idx”: Lists indexes that should be ignored during the monitoring check.
All dependencies include mechanisms to cleanup in case of failure, ensuring robustness and consistency in monitoring practices.

ignore indexes: List certain indexes to exclude from the monitoring, formatted as [DB_NAME.IDX_NAME].
database name: Target specific databases where Agresso is installed, supports wildcard use (%), for checking multiple databases.
return status: Defines the severity levels as ALARM – 2, WARNING – 1, or OK – 0, based on the presence of unregistered indexes.

Report Title: “Agresso index statistics”
Content: Provides details on not registered indexes across databases configured under Agresso setups.

Database	_. Table name	_. Index name	_. Index type	_. Unique
Query used:	Interrogates agresso_not_registered_idx_tab to provide a detailed list of unregistered indexes. # Agresso databases	DB#	_. DB Name	_. DB Info
Lists all checked databases including those without the matching pattern; offers insights into database configuration and health status. # Ignored Indexes	Agresso Database	_. Index Name

| Details out indexes that are specifically ignored in checks, as per configuration to ensure they are deliberately excluded from regular monitoring.

Implementation: Includes creation of necessary tables and a stored procedure to perform the check. Handles various conditions like database existence, object existence, and appropriate indexing to ensure data integrity.
Upgrade Path: Adjustments in the procedure are noted in version logs. Compatibility details are systematically updated, supporting SQL Server versions post-2012 and configurations like Azure SQL Managed Instance.

Applicable on SQL Server instances post-2012 version which supports specific database engine editions. The job includes forced installation settings enabled to ensure updates are applied without manual interventions, aiding in maintaining the latest monitoring functionalities automatically.