How many of you readers think that SQL Maintenance is something that IT people do and that it doesn’t really affect you? Do you know if routine maintenance is being performed on your databases to keep them running at peak efficiency? Would you run your car without maintenance for months or years without routine maintenance?
Recently, while working with one of our Dynamics SL clients we discovered that their SQL Server was not configured with maintenance plans (automated activities setup on a scheduled basis) to optimize and maintain the performance of their databases. They had their SQL Maintenance plan set up to backup their database, but no optimizations were configured. Much like a physical hard drive on your computer can become fragmented and inefficient, components of the database can experience similar situations. In SQL Server the indexes (used by the database engine to find data more quickly) actually store small amounts of data on the server and after time, the piece of data that makes up an index can be fragmented (scattered). After configuring their system with a maintenance plan to rebuild the indexes and running it for the first time, the payment reversal process in accounts receivable went from taking from 3 to 5 minutes to complete to about 15 seconds. To say that the A/R person was excited, would be an understatement.
About a year ago we discovered a similar issue with one of our larger Dynamics GP clients. Their database performance had been slowly decreasing, so they presumed the issue was with the growth of data. They had robust hardware for the size of the database and user count, so the slowness was not dramatic. What did happen though was that odd reporting issues began to occur with reports that had been working properly (and no recent changes) for some time. Reports that normally sorted by a particular field, began to show the data in a more random order. Once we reviewed the situation, we determined quickly that SQL optimizations were not configured. After adding a SQL Maintenance plan to re-build indexes, they never had this problem again.
For those of you wondering what an index is, it’s a lot like a table of contents to a book. It’s a small subset of data that tells the database engine where to find existing data more quickly.
Including a maintenance plan for rebuilding (drops and recreates indexes) or reorganizing (more similar to a hard disk de-fragmentation) is wise and recommended. Choosing to reorganize or rebuild is a somewhat subjective decision, as it depends on many factors. We tend to setup or at least recommend the rebuilding option. It takes longer but is more thorough. For this cash application client, they had a 30 GIG database. The rebuild took 6 hours for the entire database, but this process runs only once per week on Sundays. Reorganizing would typically process more quickly, perhaps 3 hours for our client’s database. This particular Dynamics SL client also had the “AUTOSHRINK” database option checked, which is not something that we recommend. The “AutoShrink” feature aggressively works to recapture physical disk space and in the process has a tendency to fragment the table indexes, thereby reducing performance of data retrievals. Maintaining your SQL Server databases is a critical component of maintaining your overall Dynamics SL, Dynamics GP or Dynamics AX accounting and ERP installation. Performance for nothing you say? Almost-you need to invest time and set up the maintenance plans properly if you haven’t already. If your system has not been maintained, you may not need to buy new hardware or upgrade software to experience better performance in many areas.
Below is a picture in SQL Server 2008 of the screen where maintenance plans are listed. Please note that there is more than one record for backing up the database.
Our typical setup of maintenance plans includes one plan for daily backups (run once per day), one plan for transaction log backups (many times this is setup to run hourly) and one plan for performing the index maintenance (rebuild or reorganize). We usually set this plan to run on Sundays, once per week. We also add other tasks to this plan, like “Update Statistics”, but your needs may drive additional tasks. At a minimum, we recommend you have 2 plans-daily backup and weekly index maintenance.
This tip is useful for SQL 2000, SQL 2005, or SQL 2008. Please note that Microsoft added its most serious performance improvements between SQL 2000 and SQL 2005. So if you are experiencing poor performance and have not upgraded your SQL Server version in a while, it may be because database maintenance has not been performed, but it may also be because you have an old version of SQL Server running or that your hardware has become outdated. We typically recommend that on-premise servers be replaced at least every four years.
The bigger picture of what we are recommending is that you ensure that your firm is taking an active role in your performance management rather than assuming a vendor or your IT group is already doing this. Please link to the following MSDN article on Using SQL Server Management Studio for more information about de-fragmenting specific indexes or setting up your maintenance plan in SQL Server Management Studio.
