Database maintenance is integral to the speed of an application. An application’s speed is driven by many factors, one of which is database performance. In an application such as Enterprise Alert that is I/O intensive, database speed is a critical factor in its performance. As such, a solid database maintenance plan is needed to ensure that performance doesn’t lag for users, causing a poor user experience. Enterprise Alert doesn’t incorporate full database maintenance features, but it does integrate a good first step in that plan if you’re experiencing slowed performance.

On average, how fast does Enterprise Alert’s database grow?

Taking an average scenario, which includes around 500 events/day, 100 users spread across 10 teams, and each event creating two alerts, you can expect an average of about 3GB per month of database growth. (This will be on the high side for most use cases) This does show that a consistent plan is needed.

In Enterprise Alert, we offer a quick, first line Database Maintenance option. This option, located in System–> General, allows for the most used tables to be trimmed. When the tables are trimmed, this can speed up performance a bit due to the time needed by the database to read and write to these tables. Larger tables obviously mean slower read and write times as the database pointer has to filter through more records.

The effected tables are as follows:

1. MMEVENTS (Incoming Events)
2. EAEvents (Log Entries)
3. MMINBOUND (Inbound Messages)
4. MMOUTBOUND (Outbound Messages)
5. MMTICKETS (Active and Closed Alerts)
6. MMTICKETMESSAGES (Events)
7. PolicyExecutionJournal (Executed Alert Policies)
8. EmergencyCalloutAlerts (Closed Callouts)
9. EmergencyCalloutInstances (Closed Callouts)
10. RemoteActionsHistory (Executed Remote Actions)
11. RemoteJobsHistory (Executed Remote Actions)

When looking at the tables, in general, the most used tables are going to be associated with the log entries and events (both entries will have the largest totals for most users), and these are the ones you would want to look at first under Database Maintenance. Both sets of tables are written to often, basically every time something happens in Enterprise Alert. Because of this, these tables can get rather large, rather quickly (as can be seen in the image below).

Our Database Maintenance option simplifies the process of deleting rows from these tables by allowing you to simply enter a date of the last record you’d like to have in the table. Everything prior to that date will be deleted from the table, and should help speed up performance a bit.

As you can see in the image below, it’s a very quick and easy method. Simply click in the text field, select the date on the calendar, and then click the circled “X” to the right of the row.

Once the “X” is clicked beside the text box, the records in the appropriate tables will be deleted.

Depending on the size of the files being deleted and the hardware you’re running, this process can take a bit of time. Just be patient and let the process run if you’re attempting to delete a large amount of entries. The other option is to select smaller “chunks” of time and delete the files in increments. But once done, you should be able to offer up a more pleasant experience for your users.

This process can also be automated by setting up a script to run as a Window’s Task. To find out how, read here
As mentioned, though, this is very small part of what should be a more thorough database maintenance plan, and simply a suggested first place to look when you’re experiencing a slow user experience. Some suggested reading on database maintenance plans and their importance can be found at the following links:

1. https://technet.microsoft.com/en-us/library/2008.08.database.aspx
2. http://www.sqlshack.com/tips-and-tricks-for-sql-server-database-maintenance-optimization/
3. https://msdn.microsoft.com/en-us/library/ms177500(v=sql.105).aspx

For further reading on Enterprise Alert’s features, please visit us here.