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’)] |
Parameters
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. |
Job Summary
- 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);
Details of Dependencies
Objects and Scripts Involved
- “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.
Parameters and Configuration
- 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.
Reporting and Presentation
Report Layout
- Report Title: “Agresso index statistics”
- Content: Provides details on not registered indexes across databases configured under Agresso setups.
Tables Included:
- Not registered Agresso user indexes
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.
Technical Details and Maintenance
- 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.
Compatibility and Installation
- 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.