Skip to main content

Creating SQL index via LCS

LCS has some really great tools for checking performance. Its really a matter of trying to get used to them and knowing whats available.

Under the Environment monitoring > SQL insights > Performance metrics.

You can find some slow queries and how they are affecting the environment over time.

I found that SalesLine table was missing an index for Revenue recognition.




Warning: Do this at your own risk.
You could break the system if you haven't tested this properly.

I raised a support ticket but was asked to create a non-unique index on the table via LCS.
Go to Environment monitoring > SQL Insights > Actions
Select "Create non-unique index on a table".
Turn of the "Allow page locks".




Talking about that risk.
I got an error after I did the above with batch jobs. I had to restart the batch service in LCS to fix the problem.

This is the error I got with batch job to post the sales invoices. 

Refer to Microsoft Docs page on this. Typically you want to do this as part of your development but that would cause outage and delay the whole process. In this instance, I tried the above and got past the problem.

Note that there is a drop query. So, take note of the index name you created. Then you can delete it afterwards.

Popular posts from this blog

AX - How to use Map and MapEnumerator

Similar to Set class, Map class allows you to associate one value (the key) with another value. Both the key and value can be any valid X++ type, including objects. The types of the key and the value are specified in the declaration of the map. The way in which maps are implemented means that access to the values is very fast. Below is a sample code that sets and retrieves values from a map. static void checkItemNameAliasDuplicate(Args _args) { inventTable inventTable; Map map; MapEnumerator mapEnumerator; NameAlias nameAlias; int counter = 0; ; map = new Map(Types::String, Types::Integer); //store into map while select inventTable { nameAlias = inventTable.NameAlias; if (!map.exists(nameAlias)) { map.insert(nameAlias, 1); } else { map.insert(nameAlias, map.lookup(nameAlias) + 1); } } //retrieve fro

Dual Write - How Pricing and Inventory On-Hand works in CE?

In the recent updates of Dual Write, Microsoft released a pricing and inventory on hand integration in CE. Real time pricing calculation and inventory on hand as per FinOps business logic. In this post, I wanted to cover how it seems to magically work. As you know by now, Dual Write uses a push architecture. Where either FinOps pushes to CE or CE pushes to FinOps. Both call a web service on the other side to sync data. Any errors that occur are presented to the user in real time. However, the pricing and inventory on-hand feature is different. These two features pull limited calculated data as needed. Watch the " Prospect to cash in dual write " tech talk from Microsoft and you will see why.  You wouldn't want to integrate (replicate) inventory levels or pricing logic in CE. The reason this is done this way is because integrating this kind of data would cause performance problems or a lot of chattiness. So, what Microsoft has done is a pull the data as needed (pull archit

Import document handling (attachment) files #MSDyn365FO

Out of the box you have limited data entities for migrating attachments. If you search what is already in the AOT, you will see a few various examples. I suggest you look at the LedgerJournalAttachmentsEntity as it is the simplest and cleans to copy from. I wont go into detail but I will give a quick run down of what it looks like. Use the DocuRefEntity as your main datasource. It does most of the work for you. Set your table you want to import for as the child datasource Add the Key You will need to add the postLoad method. There is minor code to update the virtual field FileContents. Below is an export I did for the general journal attachments. The import zip structure should be the same way. It will create the usual artifacts such as the excel, manifest and package header xml files. You will see a Resources folder under that. If you drill down to the resources you will see the attachments. This is an export and it used the document GUID for uniqueness. The other thing is the extensi