Optimizing Indexes and Improving Performance in Microsoft Dynamics GP
Has fragmentation slowed your performance when using Microsoft Dynamics GP? Rebuilding or reorganizing indexes can vastly improve performance. Fragmentation occurs when modifying data in your database using UPDATE, INSERT, or DELETE statements. The two types of fragmentation your database could experience include Internal Fragmentation, where index pages are taking up unnecessary space, and External Fragmentation, where index pages are not in logical order.
A fragmentation report on your SQL server could be useful to boost performance. Microsoft generally recommends indexes that are over 30% fragmented be rebuilt and indexes that are between 6% and 30% fragmented be reorganized. The picture below displays a report on fragmentation as well as recommendations based on the severity.
Another way to improve performance in your Microsoft Dynamics GP data base is to archive your historical data. The images from this blog were taken from a Dynamics GP archive tool called Company Data Archive (CDA). CDA will allow you to display the information about the fragmentation of indexes in your current company database. The Email button (shown below) will generate an email (you can specify who to send it to) that contains the Microsoft recommended SQL script to run to optimize the indexes.
You would want to run this pre archive for best archive performance, and post-archive for best daily operation performance since the contents of the database will dramatically change after you run an archive.
Some other good practices to run post archive include tasks such as updating statistics and other SQL maintenance jobs.