Sometimes the smallest thing can make a huge difference. At a customer we experienced a huge load (DTU +70% average), and the LCS shows that there was a single SQL query that was the reason for the load. The data composition here was that there was close to a half million customers in the customer table, and most of them had addresses, email and phone numbers assigned to them. Except of the customers used for retail statement processing.
In LCS environment monitoring you can see this as spikes in the overview.
The query you typical see looks like this:
(@P1 int,@P2 nvarchar(256),@P3 int,@P4 bigint)SELECT TOP 1 T1.COUNTRYREGIONCODE,T1.DESCRIPTION,T1.ISINSTANTMESSAGE,T1.ISMOBILEPHONE,T1.ISPRIMARY,T1.ISPRIVATE,T1.LOCATION,T1.LOCATOR,T1.LOCATOREXTENSION,T1.PRIVATEFORPARTY,T1.TYPE,T1.ELECTRONICADDRESSROLES,T1.MODIFIEDBY,T1.RECVERSION,T1.PARTITION,T1.RECID FROM LOGISTICSELECTRONICADDRESS T1 WHERE ((T1.PARTITION=5637144576) AND ((T1.TYPE=@P1) AND (T1.LOCATOR<>@P2))) AND EXISTS (SELECT TOP 1 ‘x’ FROM LOGISTICSLOCATION T2 WHERE ((T2.PARTITION=5637144576) AND (T2.RECID=T1.LOCATION)) AND EXISTS (SELECT TOP 1 ‘x’ FROM DIRPARTYLOCATION T3 WHERE ((T3.PARTITION=5637144576) AND (((T3.LOCATION=T2.PARENTLOCATION) AND (T3.ISPOSTALADDRESS=@P3)) AND (T3.PARTY=@P4)))))
By downloading the query plan, we see that there is a index seek on the table LOGISTICSELECTRONICADDRESS.
This results in that the indexes don’t get a good “hit” on the logisticselectronicaddess.type.
The solution was surprisingly easy. Add Phone, Email address and URL to the customers.
Then the DTU drastically goes down, and normal expected performance was achieved.
Conclusion; Remember when having many customers, to fill inn contact information.
This just must be shared
Kurt Hatlevik - Dynamics 365 Blog 
thanks Kurt very much! this might be very relevant for us.
Just to make sure I understand – for the “Retail” customers, I need to create “fake” contact details, question is –
for all types (email, fax, phone, URL) or just 1 is enough? or to the same types that OTHER customers have?
LikeLike
Jupp. Create contact information on as many customers as possible, but most important is the customer that is specified on the retail store table. This trick is actually related to sales order creation and invoicing, so it is also relevant in other scenario’s. And it is ok to just have blank email/url/phone, as the only thing that matter is that there is a record. Happy D365’ing
LikeLike
Hi Kurt,
Just wondering what you initially mentioned that customers have phone numbers, email assigned to them and later on you said to specify phone, email address and URL under contact information tab in Customer record. So where was the email, phone numbers was previously assigned to the customer record?
Thanks,
Baber.
LikeLike
Hi. The majority of customers had email/phone etc. But a small kunder of customers (used in retail to post retail statements) did not have this specified. This caused the indexes to not work as expected, and the database had to do “index seeks”. When a sales order is created/invoiced, certain of these contact informations are copied to the salesTable(sales order header), and this cased the very high DTU consumption.
LikeLiked by 1 person
Thanks Kurt for the explanation. Appreciate your prompt response.
LikeLike
good post!
LikeLike