Frequently Asked Questions
| Q: | What is the main gain from using A.I.M.? |
| A: | In the intro we mentioned that databases get less responsive (slower) over time. Normally that means the DBA has to analyze why that is but not rarely this is a consequence of obsolete and/or missing indexes. A.I.M. can prevent this by fixing it before users experience it. This keeps users happy and saves DBA time. |
| Q: | What if I stop using it? Does the situation get reset to prior to the installation? |
| A: | A.I.M. drops and creates indexes, just like any DBA would (or should). If you deactivate or uninstall A.I.M. nothing changes, the index collection just stays as it is at that moment. So nothing hurts, but... The process of maintaining a useful index collection stops so the DBA will have to start analyzing and maintaining ‘by hand and head’ again or the overall performance will deteriorate. |
| Q: | What size of database A.I.M becomes useful for, is there an optimal database size for best performance gain? |
| A: | There’s no general rule on database size. If a large database has lots of small tables, indexes are not that relevant. For a small database with a few large tables indexes can be very relevant. See the Purpose and Reference page for more info. |
| Q: | Can A.I.M. reduce my storage requirements in some way? |
| A: | In general yes, but that depends of course on how much useless indexes there are. Each index uses storage space so adding indexes reduces available space. However, because obsolete indexes are dropped, this valuable space is only used for indexes that are really useful. As described in the introduction; usually new indexes are added but old indexes remain. So: yes; A.I.M. can save space when there are many obsolete indexes that clutter the database because those will now be dropped, freeing up space. |
| Q: | Can I set alerts to choose whether I want and Index dropped or created? |
| A: | A.I.M. will send an email when indexes need to be created or dropped and you can designate existing (Obsolete or Counterproductive) indexes as Persistent so they will not be dropped. Furthermore, you can adjust the thresholds and retention times for dropping and creating indexes. |
| Q: | How much does this affect the hosting server? |
| A: | The overall the workload will be reduced, let me explain... The time and resources required for index maintenance varies depending on the specific case. While dropping indexes is typically a quick process, creating new indexes can be resource-intensive. It's crucial to remember that the tasks performed by A.I.M. would need to be executed by someone or something else regardless, and A.I.M. itself incurs minimal overhead. Additionally, maintaining an optimal set of indexes ultimately reduces the workload on the server. |
| Q: | Does this require ports in/out of the internet to receive updates / alerts etc.? If so, how is GDPR maintained? |
| A: | The current version does not require that, but any data that future versions of the front-end might download will be freely available from the A.I.M. website (no passord required) and only meaningful to this application. The clients data is not stored, shared, uploaded or made available in any way. |
| Q: | Does A.I.M. run continuously in the background or as a scheduled task? |
| A: | It runs as scheduled jobs, drops can be done at any time, creates should be done during off-peak hours. But the frequency and time frame in which A.I.M. performs its tasks must be adapted by the customer to fit the specific business situation. |
| Q: | What is the avg time to run for various sizes of DB’s? |
| A: | As mentioned before, it doesn't have much to do with database size but more with specific table and usage aspects. Moreover, A.I.M. works in order of the most necessary indexes across all tables and databases, so there is no fixed order. |
| Q: | I see long and partly cryptic index names, that's not practical, right? |
| A: | True, these index names can pose challenges in terms of readability and memorability. However, it is crucial to consistently generate the same name for each missing index every time it is identified. To achieve this, we employ a combination of the instance name, schema name, table name, and complete column specification. To adhere to the sysname data type's 128-character limit, we employ a hashing technique. |
| Q: | Can I use A.I.M. for Azure? |
| A: | Maybe, but Azure has Automatic Index Maintenance buit in, that's why A.I.M. is specifically developed to run on-premise and was not tested on Azure. |
| Q: | What are the System requirements? |
| A: | System requirements:
|