Maintenance Requirements on the SQL Database iMonnit Enterprise

Over time, iMonnit Enterprise SQL databases will require maintenance. The exact application of that maintenance is typically going to depend on how the database is being used; what is optimal for one database may not be the most optimal for another. However, there is some general advice on what one can do to maintain a database. Typically, query performance issues start occurring when calls to the database hit tables which contain larger amounts data. The solution to those cases will either be to reduce the amount of data in the tables or to structure the data in a way that is easier for the query to execute in a timely manner. This can be done through adding indexes, and/or updating statistics on the table.

Removing data

Cleaning up and removing data may be an option. If, for example, you as a customer determined that you didn’t need any past Webhook data, you may choose to remove that data in an effort to make webhook queries faster. In order to do this, you will either need to delete data or truncate the table (which removes all data from a table). Keep in mind that deleting data in a table will cause that table to be blocked until the delete statement finishing, and you can’t delete data that is referenced on another table while there is a foreign key constraint. Also, be aware that while truncate statements are instantaneous, they can’t be done while there is a foreign key constraint, even if that data isn’t being explicitly referenced on another table. If you do remove a large portion of data, you may need to update the statistics on that table.

Indexing and Updating Statistics

In the case where deleting the data doesn’t sound like the best option, you may opt for adding an index to one or more columns on a table. An index helps speed up a query by structuring and ordering the data on a particular column or columns of your choosing. Creating an index will take up more disk space; the exact size is dependent on the data type of the column(s) being index and how many rows are on the table. In addition, indexes may require future maintenance. You may need to rebuild indexes at routine intervals and you may need to update the statistics on those indexes once in a while as well. However, an index is a powerful tool to optimize the queries hitting the database. Please keep in mind that building an index will block the tables while the index is being built.

Conclusion

The exact method of maintaining a database is going to be dependent on individual needs. One single option is not going be the right option in all cases. At some point, hiring a database admin to evaluate the case hands on might be the best choice. If you do want to attempt the above options, it is recommended to backup your database first.