As you may have heard, last week the MBS team announced that Darren Laybourn will be taking a new position as General Manager of the Outlook Mobile team at Microsoft. We also announced that I would become the new General Manager for Dynamics NAV and Mobility and continue to report to Hal Howard, who will now run all of MBS ERP R & D. As a part of my new role, my family and I will relocate from Seattle to Copenhagen, where I will work at the Microsoft Development Center Copenhagen (MDCC). In this blog post, I want to introduce myself and invite your questions or comments about these changes.
Before I do that, however, I want to thank Darren for his contribution to MBS, Dynamics NAV, and Dynamics Mobility. Darren is a true veteran of this business, having been with the combination of Great Plains Software and Microsoft Business Solutions for over 15 years. He's had an incredible, positive impact on our customers, and he's been a mentor for me through most of my career at Microsoft.
Let me start my introduction with a very brief bio. I've been with Microsoft for about six years, working on the Microsoft Business Framework, Project Fenway, and Dynamics AX. Before joining Microsoft in 2002, I ran the R & D group for a Silicon Valley start-up called Bistro that built workflow-based business applications (including financials management) for small-to-medium sized businesses. The rest of my career has been in IT consulting for large companies such as Hewlett-Packard, Charles Schwab, Ryder, Diners Club, and American Express. In short, I've worked on business applications my entire career and am no stranger to metadata, journal posting, and complex business value chains, on which our customers' success is predicated.
Nonetheless, I'm new to NAV and have a lot of learning to do. But one of the first things that I've learned is just how passionate all the stakeholders of this product are, whether they are customers, partners, or employees. It's people like you, the readers of this blog, that are making this product and the customers that use it, a success. Thank you for your support, and I hope I get a chance to meet you, work with you, and learn about the things you love, don't love, or wish you could love about NAV.
In the meantime, the NAV organization will continue moving forward according to our current roadmap, including NAV 5.0 SP1 this March and NAV 6.0 at the end of the calendar year. Our priorities haven't changed, and our commitment to the NAV product is as strong as ever. I’m excited by the future that lies ahead for NAV customers, NAV partners, and the NAV team itself!
I look forward to hearing your questions and comments about the changes.
- Dan Brown
This is a follow up from an earlier blog "Finding Index usage". In that blog, I described a very simple way to list how indexes are being used. In this blog, the query is much extended so that it now shows your Navision keys, listed by either number of updates, or by their cost divided by their usage, and it shows when an index was last used for reading. The idea is to show a list of indexes that are being maintained, but never or rarely being used.
The query uses SQL Server Dynamic Managament Views (DMW), which means it will only work for for SQL Server 2005 and later.
Feel free to add comments to this blog about how useful (or not) this query is. And about any problems you may find, and suggestions to improve it. All comments will be welcome!
To use it, copy the query below into SQL Server Management Studio. Remember to set the database to your Microsoft Dynamics NAV database (not Master or any other database). Then run it. Depending on the size of your database, it may take a few minutes to run it. First time you run it, I would recommend that you do it when the SQL Server is not otherwise busy, until you konw how long it takes:
-- use NavisionDB
-- Generate list of indexes with key list
-- populate key string
-- Generate list of Index usage
-- Select and join the two tables.
--order by Cost_Benefit desc
The query will show you one line for each index in the SQL Database. It shows you the table name, and a list of fields in the index. Note that any non-clustered index also contain the clustered index. For example on SQL Server, the key "Document No." in the "Cus. Ledger Entry table" is "Document No.","Entry No.". Also note that the indexes shown by SQL Server is not always shown in the same order as you have defined them in NAV.
The column "No_Of_Updates" basically shows you the cost of this index, since every update requires a lock as well as a write to the database. The next column, "User_Reads", shows you how often this index has been used, either from the UI, or by C/AL code. Compare these two, and you have way to compare the cost against the benefits of each index, as shown in the column "Cost_Benefit", which is simply "No_Of_Updates" / "User_Reads". The column "Last_Used_For_Reads" shows you when an index was actually used for reading.
The query sorts the indexes by "No_Of_Updates", with the most updated (most costly) index first. At the last line of the query you can change the sorting to "order by Cost_Benefit desc", and you are likely to see a different picture.
Finally, the query shows you whether each index is clustered or non-clustered.
The query will create two new tables called "z_IUQ_Temp_Index_Keys" and "zIUQ_Temp_Index_Usage". Although highly unlikely, if you already have tables with these names in your database, then the query will overwrite those without warnings. These tables collect index usage statistics, so if you need to run the query again, for example because you lost the results, or wat to run it with a different sorting, you don't have to run the whole query. Just run the last part of the query - from the section "-- Select and join the two tables.", and it will run much faster. Only after you change indexes, or want an updated view of index usage, you need to run the whole query again.
The data shown by the query is reset every time SQL Server restarts. So if you have recently restarted SQL Server, then the query may not show you the most precise picture of how the indexes are being used over time. Also consider that some indexes may only ever be used for example at end of the month / end of fiscal year, etc. So just because the query shows that a certain index was not used since SQL Server was last restarted, then this index may still be required for specific jobs.
Lars Lohndorf-Larsen (Lohndorf)Escalation Engineer
One of the top customer questions we receive in the Fixed Assets area is about starting to use Fixed Assests instead of General Ledger.
For example, a company wants to start using the Fixed Assets module from the 1st of January 2002. Acquisitions and depreciations have been posted in the general ledger until the 31st of December 2001. Fixed assets are created in the following way:
- Henrik Sonne
The nature of a deadlock is that two processes lock resources in different orders. Deadlocks can in theory be eliminated by ensuring that all processes always lock resources in the same order. This document describes how to determine the locking order of a process in Microsoft Dynamics NAV.
Note, that I mention locking of "resources". For most of the time, this means placing locks on a table, but it could as well mean placing locks on different parts of the same table, or of different indexes in the same, or two different tables. But then we are into micro-tuning, so for this post, when I say "Resource", I mean "Table".
What is a deadlock:A deadlock will return an error message which specifically says that your activity was deadlocked. The deadlock error-message will read:
"Your activity was deadlocked with another user modifying the [xyz] table.Start again."
A blocking chain is a situation where a client hangs (becomes unresponsive) until a resource becomes available. In common language you could describe such a situation as a deadlock situation. But technically, this is not a deadlock, since the situation will be resolved eventually. In other words, to any user, an infinite block may look like a deadlock. But if you have deadlocks, then by definition, your users will have deadlock-error messages like the one shown above. One of the first steps in troubleshooting any kind of locking or blocking is to identify exactly what type of problem you have. So always make sure to first find out whether you are dealing with blocking chains, deadlocks, or other issues, since selecting the best troubleshooting methods depend on what exact issue you are looking at.
This post shows how to determine the locking order of a process in NAV. Knowing the locking order of various processes can help identifying potential deadlocks. It can also help in cases where you know that two or more different processes are causing deadlocks, by showing the locking order of each of these processes.
Pre-requisites:The tool described here is part of the Performance Troubleshooting Guide, which is available on PartnerSource (partner login required). The tools described here require a NAV Partner license.
The SQL Server Performance Troubleshooting Guide, includes an object called "Client Monitor.fob". To begin, import this into the database which contain the processes that you are troubleshooting. It does not have to be a live database - a stand-alone copy of a live database is all you need. In this scenario you are examining locking orders of processes which can be done as well in your office, on a copy or test-version of a database, as in a production environment.
To use "Client Monitor.fob", follow these steps, after importing it into the database:
1. Start Client Monitor (Tools -> Client monitor -> Start). Before starting it, go to the Options tab and de-select "Include Object table activity", and select all the options under "Advanced".2. Run the processes for which you want to detect the locking order.3. Stop "Client Monitor".4. Run form 150025 "Transactions" which was imported in "Client MOnitor.fob". This will take a while, while it collects the information that was collected by the Client Monitor. When it opens, it will show you one line for each transaction.5. For each of the lines shown in this form, click on Transaction -> "Locking order" to see the locking order of each transaction.
This shows you not just the locking order of each transaction, but you will also see the C/AL code that places the lock. If you compare this information with knowledge of what tables and processes are involved in the deadlocks you are troubleshooting, it can help you decide where locking orders need to be changed.
What locks a record?NAV will automatically place a lock as soon as any update command is used (INSERT, MODIFY, DELETE), even if the C/AL command LOCKTABLE is not used. On SQL Server, Navision will only lock the record (and potentially adjacent records - see this post).
The C/AL command LOCKTABLE in itself does not lock anything. It only puts NAV into locking-mode. Navision will then lock any records that it accesses. This is why the places in C/AL code that place locks are typically not LOCKTABLE-commands, but for example a FIND('-')-command that comes after a LOCKTABLE command.
What to do next:Once you have determined that two different processes have different locking orders, you must decide on a locking order. There are no complete guides to which order should be used, but if you look for the C/AL command RECORDLEVELLOCKING in codeunit 80, you can see that this codeunit locks a number of tables in a certain order. At least for tables that are included in this order, it makes sense to stick to this order. For tables which are not included here, you must make your own decisions. Once you have decided on a locking order, you can use one of the following tactics to change it:
Avoid locking:Some times it is not necessary for a process to access certain tables. For example, the tool may show that table 5765 – “Warehouse Request” is being locked. If a customer is not using warehousing, then the code that locks this table can some times be remarked.
Lock sooner or later:One of the most common ways to change locking order is to move a lock up so that it happens sooner in the process. For example, lets say you have this line of code: SalesOrder.INSERT;And you know that later on in the code, you will be locking one of your own tables. To change the locking order of these two tables, add: YourOwnTale.LOCKTABLE;Before the line where you lock the Sales Order table.
Master lock (semaphore):You can decide that all processes must lock a certain record before they lock anything else. In this case, only 1 process will run at the time. This will effectively eliminate any deadlocking, but it will also reduce concurrency, and can lead to blocking chains instead.
As you can see, two of the tactics above (lock sooner, and Master lock) may reduce the risk of deadlocks. But on the other hand they can also increase blocks, because (more) resource(s) get locked sooner. So always make a total assessment of the problem that deadlocks cause. Some times it may be better to have a weekly deadlock, but optimistic locking, than begin to rearrange locks and cause more blocks.
In an effort to continue to provide our customers and partners with a stronger set of tools for their businesses, the Jobs area of Microsoft Dynamics NAV 5.0 was redesigned and many new features were added to the module. Some really exciting functionality to help with productivity and flexibility such as an entire new budget structure and features like fixed item pricing, foreign currency functionality, copy job, calculate remaining usage, journal improvements and better integration with item tracking, costing and service management have been very well received.
However, it’s been brought to our attention that some customers and partners are having an issue with the new functionality. In Microsoft Dynamics NAV 5.0, the purchasing process is initiated in the purchase invoice when some customers business processes dictate that they start the process in the purchase order. Technically there is little change in the underlying functionality between releases – in both 4.0 and 5.0 ledger entries for the job were not created until the invoice is posted. This said, we can completely understand the issue and a fix allowing users to start the process in the purchase order will be in Microsoft Dynamics NAV "6.0" and we are currently investigating the issue with 5.0. For the time being, if you upgrade 5.0 and are dependent on purchase order functionality with the Jobs area, you will experience an issue.
Some customers have also had an issue with dimensions on WIP (work in progress) in the Jobs module in 5.0. A fix for job WIP dimensions will be shipped in SP1 and is already included in the Microsoft Dynamics NAV "6.0" code.
We are making a site on both Partner and CustomerSource that will link you to a short whitepaper outlining the change in functionality from 4.0 to 5.0 in more detail. I will post again when I have links are available to these sites.
- Selena Breann Jensen
If you are designing a system for high throughput and want to minimize blocking, it's important to know in advance exactly what kind of locking behaviour to expect.
On SQL Server, Microsoft Dynamics NAV uses record-level locking, as opposed to table-level locking on the native database server. In reality, SQL Server can lock a little bit more than the individual records you want to lock: It can also lock the next and the previous records. This happens when SQL Server applies a range-lock.
This post describes how and when a range-lock can occur. It applies to a normal lock, regardless of the "Always Rowlock"-setting in NAV, and lock escalation where SQL Server can escalate a record-level lock to a table-lock - these are seperate issues.
Here are some examples:
Table 7 "Standard Text", contains the following records:Code DescriptionMD Monthly DepreciationSC Shipping ChargeSUC Sale under ContractTE Travel Expenses
Table 7 is used here, because it is the simplest possible table.
The following C/AL code in NAV will lock just one record (SC - Shipping Charge):
IF NOT CONFIRM('Continue?') THEN ERROR('Stopped.');
You can confirm this by running it from one client, then open another client and try to update the records in the table.
Now, consider this C/AL code:
You might expect that it will lock the two records that fall within this filter (S*): SC and SUC. But this is where SQL Server locks a bit more: It will also lock the records around this filter (MD and TE). This is because as soon as the lock covers a range and not just one individual record, SQL Server has to protect not only the locked records, but also the range itself. In this case, the range covers 2 records, and SQL Server prevents anyone else from inserting new records in this range. It is SQL Server's way to guarantee that the range stays at these 2 records.
Depending on your C/AL code, some times SQL Server protects just the beginning, and some times just the end of the range, and some times both the beginning and the end. So when you lock a range, you should assume that that both the record just before, and just after, will also get locked.
Here is an example from a more real situation. Imagine you have a process to update sales order 2002 (posting / releasing or any other process). You may have C/AL code to lock the sales lines, like this:
SalesLine.SETRANGE("Document Type",SalesLine."Document Type"::Order);SalesLine.SETRANGE("Document No.",'2002');SalesLine.LOCKTABLE;SalesLine.FINDSET;
In this case, also the lines for order 2003 will get locked! So even when users are working on their own documents, they can still end up blocking each other.
When this is causing problems, what methods can be used?
The simple answer is, to make sure that users don't work on records that are just next to each others. One way would be to insert "ghost" records between each normal record. Another way, is to change the sorting. In the example above, if you had added this line at the beginning:SalesLine.SETCURRENTKEY("Document Type","Sell-to Customer No.");
Then order 2003 will not get locked, because with this sorting, it is not the next record.Or you can design a solution that works in more random areas of a table. Using Number Series means that active documents are often in the same area of the table. If you use random numbers, or GUID as primary keys, then the active documents would be spread through the whole table, and the risk of users updating records next to each other, would be smaller.
These are just some methods to consider, but the main aim would be to spread activity on a contented table to avoid hot-spots in that table.
Locking the last record is used in a number of places in the NAV application, including the posting routines, which normally lock the last record in the entry table. When the record you are locking is the last one in the table, then the "next record" is anywhere between the last record and infinity. So in this case, the range that is locked can become much larger.
If we go back to the Standard Text table and run this C/AL code:StandardText.LOCKTABLE;StandardText.FINDLAST;
Then the locked range will include any possible record after the last one (TE - Travel Expenses), so it will prevent from inserting any new records that would come after TE.
If designing an application from scratch, then you may want to take this behaviour into consideration. If changing an existing application, then in some cases it will require major re-design. So always also consider other ways to improve performance. There may be easier ways to resolve blocking problems.