Welcome to the Exact Product Blog

On this blog we will cover stories from all around the Exact ecosystem.

Improve performance of your Exact solution by implementing index suggestions.

Written by André van de Graaf on . Posted in Exact Globe

First of all I would like to thank all customers who have already used the Exact System Information tool . Because of these customers, we have received valuable information to improve the standard index structure of Exact Globe. At a lot of customer sites we have seen that the same index suggestions were reported by the SQL Server database engine. We have used this information to improve the standard index structure of Exact. A good index structure enables the SQL Server database engine to retrieve requested data by the Globe application in an efficient way. For example: Finding something in a book without a good index will take you a lot of time. The better the index the easier it is for you to find the content you are looking for.

In the upcoming releases of Globe we will add these new indexes to the standard index structure of Exact Globe. After you update to these new release, you will automatically make use of this new index structure. However, there can be reasons why you can’t update to this new release immediately. For instance, your custom solution needs to be changed to be compatible with the new Globe release. In this blog I will explain how you can already make use of these new indexes in your current release of Exact Globe.

First of all, Index suggestions will ONLY be available if your Exact database runs on SQL 2005, SQL 2008 or SQL 2008 R2.  The SQL Server database engine stores all index suggestion in dynamic views. A dynamic view with index suggestions can contain content as long as SQL Server is not restarted or indexes are added. The longer SQL Server is running, the more index suggestions can be available in the dynamic views. For the best results your SQL Server should run at least 4 weeks.

 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). Otherwise you do not have access to the reported index suggestions. During the generation of the improvement report, we will compare the index suggestions reported by your SQL Server Database engine with the indexes we are adding in the new releases of Globe. If one or more matches are found we will add these matches to a new section in the report: Index improvements. See next example. 

 

To add these indexes to your Exact database you can create them in 2 ways:

  • Use the EPTIndex tool 
  • Manually with SQL Server Management Studio
 

  

To create these indexes with the EPTIndex tool: 
  • Download the latest version of the EPTIndex.exe tool
  • Copy EPTIndex.exe to the BIN Folder of your Globe installation folder.
  • Download the latest version of the PerformanceTuningIndices.xml file. Put your mouse on the filename:  PerformanceTuningIndices.xml and right click, Use Save Target As. Save the file in the XML folder of your Globe installation folder. If you double click on the filename, the XML will be opened instead of downloaded.
  • Browse to the BIN folder of your Globe installation and double click on the EPTIndex.exe to start to the EPTIndex tool
  • Select Server and Database
  • Press the Preview button to get a list of possible indexes which can be implemented
  • Press the Generate all button to implement all suggested indexes.
  • More information about the EPTIndex tool can be found here.

To create these indexes manually with SQL Server Management Studio: 

  • Select Start button, Run, SSMS.
    If you do not have installed SQL Server Management Studio, you can install it here.
  • Press the New Query button to open a new query.
  •  Connect to your SQL Server. This is the same server as you have used to run the Exact System Information tool.
  • Paste this script from the improvement report via Copy/Paste in the SQL Server Management Studio (SSMS) New query window.
  • Execute the script by pressing the Execute button.
  • Depending on the index and the size of your database this can take some time to execute.

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

Tags: , , , ,

Leave a comment