Exact System Information improvement report extended with filtered index suggestions.

As of today the Exact System Information improvement report is extended with the possibility to implement filtered indexes for Globe databases on a server with SQL Server 2008 or SQL 2008 R2. See next screen shot from an improvement report.

As of SQL Server 2008 filtered indexes are introduced. A filtered index allows us to create an index with a filter on a subset of rows within a table. A filtered index will:

1. Improve query performance. Statistics are more accurate which can result in better query plans.
2. Reduce index maintenance costs. An index is only maintained when the data in the index is changed.
3. Reduce index storage costs.

Filtered indexes can be very useful on columns which contains mostly NULL values and where the queries retreive only the rows where the data is NOT NULL. In Exact Globe we have a lot of indexes on columns which contains mostly NULL values. See Microsoft website link for more information about filtered indexes.

After implementing filtered indexes on some customer databases, we have seen reduction of the index size between 25% to 30%. This will results in an overall database size reduction of around 10 %. The actual reduction of the index size in your database depends on the number of NULL values in your database. Please DO NOT shrink your database after implementing the filtered indexes. Shrinking databases and explanding database can result in defragmentation of the database files on NTFS level. SQL Server will first use the free space in the database before it will growth.

Performance tests indicated no noticeable performance increase or decrease with read actions, however write performance is gained due to the fact less index information needs to be written. After implementing filtered indexes, the fragmentation of your indexes will be lower. This is good for the overall performance of your Exact solution.

To begin you need to start the Exact System Information tool and request an improvement report. The user who start the Exact System Information tool should have a SQL System Administrator role (SA). If you can implement filtered indexes, it will be mentioned in the improvement report. In the improvement report you will find a link to the script to implement filtered indexes.

We strongly advise you to run the Exact System Information on a regular basis. For instance once per 3 months. We are adding on a regular basis new suggestions to the improvement report to improve your Exact solution. If you have any feedback, please let us know. You can comment on this blog post or send an email to Andre@exact.com

This blog is also published on the Exact Product Blog.

Did you already request an improvement report for your Exact solution?

As mentioned in a previous blog post, Exact started developing the Exact System Information (ESI) tool. With this tool you can receive an improvement report via e-mail. What can you expect in the report?


1. Server improvements.

The server improvement section is related to your SQL server configurations and answers questions like: Have you installed the latest Service packs? Can you make changes in your SQL Server configuration to make use of all available memory? Etcetera.

Screenshot of Server Improvements:

2. Database improvements.

The database improvements section is related to the database itself and gives advice about new available product updates, clean up of historical journal records, clean up of voided entry record, background jobs, clean up of temporary tables etcetera.

Screenshot of database improvements:

The number of requested improvement reports are growing every month. The Exact System Information tool can be downloaded and used for free by all organizations that uses Exact Globe and/or Exact Synergy (Enterprise). Did you already request an improvement report? Do not forget to specify your email address when running the tool.

If you have feedback please comment on this blog or send an email to Andre.van.de.Graaf@exact.com
The ESI tool can be downloaded here. This blogpost is also published on the Exact Product Blog.