Dynamics 365 Business Central has (from a long time) a nice feature for the end user called Edit in Excel.
With the Edit in Excel action, you can make changes to Business Central records in Excel and then publish the changes back to Business Central. The Edit in Excel action requires that the Business Central add-in is installed in Excel:
Despite I personally always try to discourage customers on abusing of this feature, sometimes in certain organizations it’s used by accountants to quickly work on big journals or something similar.
What happens under the hood when you use this feature?
The Excel add-in loads the data performing a set of sql queries (select) and when you update the data in Excel, it then performs a set of insert or update queries (the most critical part for performances).
You should be aware of that, because often this is not a problem but sometimes (if you have telemetry enabled) you will see that your Long Running SQL queries (RT0005) will increase a lot.
Here is an example of that coming from a customer’s telemetry:
In red you can see the moment where the customer has used Edit in Excel feature on a large set of records.
These SELECT queries often have the SELECT WITH(UPDLOCK) clause. This clause specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. The UPDLOCK tablehint is used to impose an update lock on a resource until the transaction completes. Thus, if a transaction reads data which potentially can be updated in that transaction, and there are concurrent transactions that can try to change the same data, the UPDLOCK hint can be used while reading this data in order to avoid deadlocks.
Massive usage of Edit in Excel feature could generate performance and locking problems (at least this was my experience on some tenants).
A useful KQL query to check when and how your users are using Edit in Excel feature in Dynamics 365 Business Central is the following:
traces
| where timestamp > ago(1h)
| where customDimensions.eventId == 'RT0008'
and customDimensions.category == 'ODataV4'
| extend httpHeadersTmp = tostring( customDimensions.httpHeaders)
| extend httpHeadersJSON = parse_json(httpHeadersTmp)
| where httpHeadersJSON.['ms-dyn-useragent'] contains "DynamicsOfficeApp"
| extend httpStatusCode = customDimensions.httpStatusCode
, aadTenantId = customDimensions.aadTenantId
, environmentName = customDimensions.environmentName
, environmentType = customDimensions.environmentType
, alObjectId = customDimensions.alObjectId
, alObjectName = customDimensions.alObjectName
, alObjectType = customDimensions.alObjectType
, endpoint = customDimensions.endpoint
, totalTime = customDimensions.totalTime
| project timestamp, customDimensions, httpStatusCode, aadTenantId, environmentName, environmentType, endpoint, alObjectId, alObjectName, message, totalTime
Stefano Demiliani 