Источник:
https://kurthatlevik.com/2019/05/31/...formance-tips/
==============
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
Источник:
https://kurthatlevik.com/2019/05/31/...formance-tips/