Agresso – missing user indexes
Job details
| Name: | Agresso – missing user indexes | |
| Platform: | Sqlserver | |
| Category: | Agresso | |
| Description: | Checks if there are any missing user indexes in the agresso database by comparing records in dbo.asysindex and sys.indexes tables. | |
| Long description: | Checks if there are any missing user indexes in the agresso database by comparing records in dbo.asysindex and sys.indexes tables. | |
| Version: | 1.31 | |
| Default schedule: | 35 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: The purpose of this job is to monitor and identify missing user indexes in Agresso databases, ensuring that all necessary indexes are present for optimal database performance.
- Why: This job is vital as missing indexes can lead to severe performance degradation. It maintains efficiency and performance integrity in database operations. Regular monitoring helps in proactive maintenance and ensures that the database is optimized for performance.
- Manual checking: You can check for missing user indexes manually in the database by comparing the existing indexes in “dbo.asysindex” and “sys.indexes” tables. The following SQL command could be used to manually check for discrepancies:
SELECT asys.index_name FROM dbo.asysindex as asys
LEFT JOIN sys.indexes as sys ON asys.index_name = sys.name
WHERE sys.name IS NULLDetails and Operations
- The job compares records between the “dbo.asysindex” and “sys.indexes” tables to find any indexes that are documented but not implemented.
- It operates under a default schedule which runs at 7:35 AM, from the first to the fifth day of every month.
- The job recognizes and acknowledges alterations to the database environment by setting the acknowledgement parameter to true, meaning it can track changes made between runs.
Dependencies and Procedures
- The primary sources of information involve several tables and stored procedures specifically designed for the job. These dependencies include:
- Table for missing user indexes: “agresso_missing_user_idx_tab”
- Database information table: “agresso_missing_user_idx_db_info”
- Main monitoring procedure: “agresso_missing_user_idx”
- Ignored indexes table: “agresso_ignore_missing_user_idx”
Handling Outputs
- The outputs are managed through a structured report comprising three main parts:
- Database status
- Missing indexes
- Ignored indexes
- Each part is depicted in table presentations with specific columns designed to illustrate the status efficiently:
| Database | Table Name | Index Name | Column List | Unique |
|---|
bac.. | SELECT * FROM agresso_missing_user_idx_tab |
*Reports are generated according to the scheduled times within dbWatch, streamlining the process for administrators.
Environmental Requirements and Constraints
- This job is specifically designed for instances where database types are “sqlserver” higher than the year 2012 version, and it includes both “Microsoft SQL Server” and “Azure SQL Managed Instance.”
- The implementation includes precautions for a smooth application, including setup of the necessary tables and indices if they are not present.
Upgrade Implications
- Upgrades from previous versions (1.2 or 1.3) to version 1.31 involve modifications to the stored procedure that checks for missing indexes, ensuring that the checks are kept up to date with any changes in database structure or indexing strategies.
Custom Configuration
- Users can add custom configurations through parameters such as specifying which databases to include in the checks or defining which indexes to ignore, providing flexibility in tailoring the job to specific needs or setups.
This comprehensive monitoring solution is crucial for maintaining the efficient operation of Agresso databases, making it a significant asset in a DBA‘s toolkit.