In order to keep your Counterpoint SQL system running efficiently, there are a few maintenance tasks that need to be done. These need to be performed on an on-going basis.
First, backing up all databases should be a top priority. Although this does not directly improve the performance of Counterpoint SQL, you will be very glad to have these in that rare case that your SQL database gets corrupted beyond repair. It does not happen often, but it can happen. Much better to make database backups at least once a day.
Maintenance for Counterpoint SQL performance, two things that we have found to have the biggest impact on performance, are indexes and statistics. What we have found, is that in order to keep Counterpoint SQL performing well, rebuilding indexes and updating statistics needs to be done frequently. In fact, I recommend that it be done on a nightly basis. Doing this frequently allows your SQL server to locate records in the most efficient manner. Sometimes it is nothing short of amazing to see the difference in the speed of your lookups, just by doing these two tasks.
Something else that is often recommended, is shrinking the database. I do not recommend this, unless a large amount of data has been purged, and you are looking to recover the space. If you do shrink your database, make sure that you allow ample empty space. If you shrink it, without allowing the empty space, your SQL server will incur significant overhead because it will frequently be needing to increase the database size automatically in small increments.
We have put together the SQL scripts to setup and schedule these tasks. By scheduling these to run every night, your Counterpoint SQL performance will be kept at a high level. Contact us to set them up on your system.
Dave.