Issue SQL Server Performance
Solution
 The biggest problem for Navision and SQL Server has been the keys, sumindexes, etc, and SQL Server is pretty "dumb" compared to Navision sumindex field technology, because it always tries to simulate that technology by creating the companyName$table$siftlevel tables in SQL Server.  This works well the first time for storing the sums matching the keys in Navision into these tables.  One of the biggest problems so far, is that those sift tables are created on per company basis and are updated in a nested way, and the reason for that is that the sums for every combination in every sum index level table must be rewritten, and this is how the first problem in Navision performance is highly impacted on Navision.
The best recommend solution for this SQL serve problem is to optimize keys and their usage so properties like MaintainSQLIndex, MaintainSIFTIndex, and something like SiftLevelstoMaintain, i.e. MaintainSQLIndex, should only be active for the primary key and disabled otherwise.
However, if we only MaintainSQLIndex for Primary Key, we will have more troubles.  The reason is that Forms, Reports and even Codeunits have many filters dependant on others keys.  This step will cause many Full Table Scans.  Using Navision Tools we found lots of full table scans, so we activated the Indexes.
Check the SQL DB properties (using the SQL Enterprise Manager) and see if the file growth is well-configured (remember administering SQL?).  Often Navision freezes everybody while expanding the DB automatically with SQL; this database growth needs be checked periodically to see how fast that DB grows.
When you are running into performance problems, have a superuser log in to the Navision client and perform the following tasks:
1. Go to File > Database > Information
2. Click on the Sessions tab
3. Drilldown on the Sessions count field
The table is different than that of the C/SIDE equivalent.  Within this table, you can trace the user that is causing the blocking/locking scenario, and then hopefully identify what needs to be done, what tables this user is trying to access, the information needed, and how the keys are set for that table.
The following example will not cause a deadlock, the table must lock so the other processes will wait until his job is done or they receive a Timeout error.  This result is normal.  The SQL Server can be setup as Record level Lock.  So SQL Server will not lock the whole table, it will lock the records that the transaction will update.  If more users work in the same company and different batches, it should not cause trouble (in theory).
An example of how to trace the person is as follows:
Suppose that there are three users that are shipping and invoicing.  They are John, Christian, and Kevin.  As Kevin is posting, he mysteriously runs into an infinite loop that locks the Cust Ledger Entry table.  Christian now tries to ship and invoice, and he is blocked by Kevin.  John also then tries to post, but he is blocked by Christian (but remember, Christian is blocked by Kevin).  In order to find the root cause (or root user, which is Kevin) you will need to navigate from John to Christian to Kevin.  To do this, use the following steps:
1. Filter the 'Database Name' field for the current database.
2. Look for the first user that is 'Blocked'.  There is a checkbox that will be checked!
3. Look at which user is causing the other user to be blocked by looking at the 'Blocking User ID' Field.
4. Look at which Object is being blocked by examining the 'Blocking Object' on that particular blocked user (you can then deduce what may need to change in code or business process).
5. Go to the user that is now causing this block, and do the same sequence over again.  If the user does not have a 'Blocking User ID' then he or she is the culprit and should be logged off of Navision for processing to continue.
Some specific areas that cause problems with concurrency:
. Adjust-cost: If running during production, locking of Value Entries occurs and Inventory transactions cannot proceed.
. Automatic Cost Posting: Slows down the processing because of additional postings.
. Analysis View: If 'Update on Posting' is checked, the system slows down because of additional locking and posting on the Analysis view tables.
. Regenerative MRP (Material Requirements Planning) Run: Locks a lot of tables, similar to that of Adjust Cost.
. Action Messages and Order Tracking: Additional postings that cause slow-downs and locking problems.
. Commerce Portal - The Synch Message Queue becomes an extremely high-traffic table, which in turn causes slowdowns and deadlocks, etc.
. Change Log - If the change log is activated, and it is set to monitor tables, slow-downs and locking issues will occur.
If you are running on SQL Server, you must also be aware of a very specific performance problem that applies to forms.  Setting the SourceTablePlacement property to the default value (Saved) will often make opening forms that display data from tables that contain many records (1,000,000 or more), for example G/L entries, very slow.  To fix this problem, set the SourceTablePlacement property first in these forms.