Identifying the performance bottleneck is already 50% of the solution.

In my previous blog post I'm in favor to visit customers as a product manager. You get useful feedback how customers are using your product. This week I did, as Principal Research Engineering, a consult to find the bottleneck in the Globe environment of a customer. It was an interesting consult with useful feedback. I will summarize some experiences:

1. SQL 2000 (X86) was installed on a Windows Server 2003 (X64). The server has 8 GB memory. However only 2 GB was used by the 32 bits version of SQL Server. It is possible to allocate more memory for SQL Server (X86) but only on a 32 bits operating system. To use all memory on Windows Server 2003 (X64), install an X64 version of SQL Server. After installing SQL 2005 (X64) all server memory was used. The more memory SQL server can use, the better is the performance.
2. An application took a while to display the information. With SQL profiler, one query was identified as the bottleneck. Two suggestions to improve the query will be investigated. Hopefully are these suggestions possible. The development organization is currently analyzing these suggestions.
3. The Intrastat application resulted almost always in a query time out. Also in this case, SQL Profiler was used to identify the slow query. The query itself was correct however in the query plan; a clustered index scan was executed on the biggest transaction table GBKMUT.  After creating an index, the application executed within 2 minutes. This index will be added to the index structure of GBKMUT for our logistic Globe customers.

Conclusion: Identifying the performance bottleneck is already 50% of the solution. In this case, a wrong SQL Server installation, a query which can be improved and an improvement of the standard index structure of Globe. This consult resulted in a win-win situation. For the customer, the performance of his Globe environment is improved. For Exact, our customers can benefit of the improvements in the application and index structure.