Issue

Modern NAV/SQL troubleshooting II

Solution

Please refer to this post about what I mean with "modern troubleshooting".  This post describes methods that work on any version of SQL Server, including SQL2000. It describes one of the most common questions I get, which is "Where do we start"... General performance problems - where to start: If a system is suffering general performance problems, then it is not always easy to decide what to do first, or where to start. Then you may be tempted to collect lots of information, for example Profiler traces and databases which may or may not give any results, but which is guaranteed to require a lot of work both to collect, to send, and to analyse. Often, over-indexing is one of the main causes of both bad performance problems and blocking. Indexes take time to maintain, and updating an index causes locks, which in turn can contribute to blocking-problems. so in a cases where the problem can't be located to a specific action, one place to start is to do some index tuning. In a case like that, I often begin by asking for just two files: Excel spreadsheet containing Table Information A NAV backup or fob file which contains the customer's objects, but not their data Each of these files don't take long to prepare, and are normally small enough to send by email. You collect the Excel spreadsheet like this: In NAV, go to File -> Database -> Information, and click on Tables. Copy this into an Excel spreadhseet. When you get the spreadsheet, sort it by "No. of Records", or by "Size (KB)". The difference is, that "Size (KB)" includes the size of the indexes on each table. So either way of sorting gives you a good idea of which tables are being updated the most, and which tables have lots of both records and indexes. Then load the NAV objects that you also received, and for the top 5 - 10 tob tables in the spreadsheet, take a look at the tables in NAV, and check the number of keys and SIFT-indexes. Some times you will immediately see that these tables have had a lot of keys added, and you should try to see if you can reduce the number of keys, using the key properties MaintainSQLIndex, MaintainSIFTIndex and SIFTLevelsToMaintain (remember the SIFTLevelsToMaintain-property don't exist any longer in NAV 5 SP1 where it was removed as part of re-designing the SIFT system). Which indexes to disable the SQL-maintenance of: Only if you know how the system is being used, can you tell whether a certain index is being used or not. But, for example, a key like "IC Partner Code" in table 17 is only ever useful if the customer is using Intercompany Posting, and the "Additional-Currency Amount"-sumindex field on the same table is only needed if Additional currency is being used. So some times, a few obvious changes can be done. But to really do some index tuning, you need to know more than the objects alone can tell you. You can also take the opposite approach, and disable maintenance of all indexes and SIFT (except for the clustered index). This will show you which ones are really needed when certain processes begin to run very slowly, and you will then have to (quickly) re-enable those. This is of course a risky approach, but it can be quicker than analyzing each individual key.   In deciding which indexes may be disabled, the following tools may help as well: If running on SQL Server 2005 or later, run the query "Index Usage" (follow this link) The Key Information tool on the SQL Server Resource kit can help you deciding the cost of indexes per table, and help deciding the usefulness of SIFT indexes. With Navision Developers Tool (NDT), you can run "Where Used" on a key, to see in which object it is being used. If you use this method, then make sure to select everything in the "Where Used"-options. And keep in mind, that even with everyhthing selected, the NDT cannot see if users are manually specifying certain keys on the forms and reports they are running. Lars Lohndorf-Larsen Escalation Engineer These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use