Tuning the EventSentry Database

The database layout was completely redesigned starting in version 2.50 to resolve slow query times when using the web reports. The new EventSentry database tables and indexes were designed to accommodate large amounts of data while still providing fast query responses.

When you expect large amounts of data (e.g. more than 1 million records) it is however still important that you take steps to ensure that the database is optimized at all times. Below you will find recommendations for various supported databases when you expect to collect large amounts of data.

It is recommended that you run all queries with either a record limit and/or a time limit to significantly decrease the time it takes for a query to complete.

For example, if you are looking for events that you know were written today, then only search for events within the last 24 hours. Furthermore, restrict the query to initially only show a small amount of records, e.g. 50. Only increase the maximum records count if it turns out that you need more than 50 rows.

We do not recommend using Microsoft Access for storing large amounts of data. MS Access is a good solution to get started with a database consolidation project, but you should move to a database server once you reach about 100.000 records.

When creating the database, make sure that you set the initial database size sufficiently high. This will improve the overall performance because the database engine will not have to continuously extend the data files. This applies to both the data files and the transaction log size. In addition, configure the file growth rate larger rather than smaller. The screenshots below show a good starting point for potentially large databases:

Reorganize the tables on a regular basis, but especially after you purge or move old records. SQL Server offers a feature called Maintenance Plan that lets you schedule a database maintenance on a regular basis or by demand. Keep in mind that a database reorganization can temporarily use more disk space, so always make sure that you have enough disk space for the database and transaction log available. The maintenance plan wizard can be launched by right-clicking the database and choosing "Maintenance Plan" from the "All Tasks" submenu. The screenshot below shows a recommended configuration.