Staying on top of regular maintenance is important with your car, your house, and even your database. If you are working with your data daily, you want to make sure that it is running at its best. Here are 5 tips to keeping your database running smoothly.
Backup (and Recovery)
Creating backups is an important part of database maintenance, as it is the only reliable way to protect your data. How often you create backups is up to you. How much time can your organization risk losing if something goes wrong and you need to restore data?
Once you determine that amount of time, you can create a restore plan to make that happen. After you make the restore plan, then you can make a backup plan to ensure that your data is backed up as often as you need it. Then, you implement that full plan when required, but make sure you test restoring to ensure your plan’s success.
Compress
Do you make edits to the data in your database often? Has your database performance gone down? Are you regularly compressing your database?
Compress is an important tool in ensuring that your database is performing at it’s best. When you make edits to a versioned database, the performance slows due to the growing number of states and rows in the delta tables. This process moves rows in the delta tables to the base table while also removing states no longer referenced by a version.
If you are making a lot of edits, it isn’t unreasonable to run the compress every night. If you make a low to medium number of edits, once or a few times a week may be all you need. The best way to tell is to try different schedules to see what is best for your organization.
Analyze Datasets
When table and index statistics become outdated due to changing content, you should be running the Analyze Datasets tool. You should also plan to run this tool after compressing your database.
By running the Analyze Datasets tool, you are updating the statistics for the datasets in your database. The statistics are used to process queries, so it’s a good reason to update them occasionally.
To note, this doesn’t replace the updating of statistics on the database in your chosen database management software.
Rebuild Indexes
Indexes are important because they are used to help retrieve rows from your database faster. Over time, as your make edits, your data can become fragmented, and you should rebuild the indexes to keep things running at their best. During the compress process the data can become fragmented, so this is another time to run this operation.
This is another case where how frequently you perform this task depends on how active your edits are. If you are making a lot of updates daily, you may want to run it as part of your daily maintenance. Yet, if you make a low to medium number of edits you may want to run it a couple times a week, weekly, or even monthly.
Don’t forget about rebuilding your spatial indexes if your data has expanded past where you have been working. This is another index that can slow down your performance.
Upgrades
When upgrading your ArcGIS software to take advantage of the latest updates, don’t forget to upgrade your database. After you have upgraded the software, you can then upgrade your database.
This updates the system tables, stored procedures, types, and functions. It allows you to utilize new functionality and apply any bug fixes. Be sure to test the new version on your server to make sure it works with applications before discarding any old versions.
For more ArcGIS related tips see here.
