This article lists 5 things you need to include into your database maintenance plan for AVIcode. It covers the most important aspects of AVIcode Databases Maintenance including statistics update, indices reorganize/rebuild, database integrity verification, database backup/restore and cleaning up old and historical data from msdb database. One of the most popular questions we are asked while introducing AVIcode to our customers is how to maintain AVIcode DBs. Maintenance is a really important activity as it helps to prevent possible database outages and poor performance of the AVIcode infrastructure. When you reach the frequency of several events per second, lack of regular database maintenance may result in the following issues:

• database performance problems
• servers running out of space
• database corruption and loss of data
• lack of backups to fall back to

A good maintenance plan not only improves performance but is also simplifies future troubleshooting and recovery. Most organizations have some database maintenance plan already implemented and executed on a regular basis. AVIcode database is an OLTP by nature and deals with the high volume of the inserts and selects, therefore it is important to have appropriate activities included in your database maintenance plan. In this article I will explain key maintenance aspects which will help your databases run healthy. Note: the following recommendations are applicable for SQL Server 2005/2008 series. Let’s get started:

• While handling SQL requests the Query Optimizer creates execution plan based on statistics. Therefore one of the most important maintenance tasks is Regular Statistics Update that will let the Optimizer use more efficient execution plan.We recommend running sp_updatestats stored procedure at least once a day for SEViewer DB and twice a month for Advisor one. But the actual scheduled interval how often to update statistics depends much on the load (number of server, stored events and counters, etc).
• Frequent data manipulators (i.e. INSERT, UPDATE, DELETE) cause index fragmentation. In order to improve query performance by reducing this fragmentation  you are to maintain Database Indices.You should reorganize indices once a day and execute indices rebuilding weekly. To make the task easier and automatic we may supply the script performing either reorganize or indices rebuild (depending on fragmentation amount) in online mode.
• Database Integrity Verification helps to check that the database is not corrupted. It is a rather resource consuming task, thus it should be executed once a week. Use DBCC CHECKDB(‘{databaseName}’) command for this task.
• Database Backup and Restore are intended to prevent from data loss in case the database got corrupted. Of course it cannot eliminate the possibility of data loss at all, but having implemented backing up plan correctly you ensure that the most of data will be kept regardless database issues occurring. SEViewer and Advisor DB use SIMPLE recovery model which improves query performance, but imposes the limitations on ability to restore DB at any point in time. That’s why it’s useful to backup DBs, for example, once a day. It would be better to make backups just after executing statistics update and indices maintenance tasks.Database backup is performed with the following T-SQL command: BACKUP DATABASE database_name TO DISC=N’d:\backup\database_name.bak’ WITH NAME=N’database_name_bak’ Before restoring DB it is a good idea to verify it with this command: RESTORE VERIFYONLY FROM DISC=N’d:\backup\database_name.bak’
• Removing Old, Historical Data (information about backups, SQL Server Agent lobs, Maintenance Plan execution, etc) from MSDB Database increases free disc space and speeds up operations using msdb database.Clean up can be executed with these commands: sp_delete_backuphistory sp_purge_jobhistory

That seems to be all with this small introduction into database maintenance. Hope you’ll find it useful and it will help taking care of your DBs. For more information concerning DB maintenance and optimization you may contact us. Good luck!