LCS has functionality to update statistics on a table, but it's lacking the possibility to supply a custom sample size or run a full scan.

Having more control on the statistics sample size is crucial, especially in situations where a lot of data is loaded at once (e.g. ETL, import of data via interface, etc.) and data gets skewed. At that point, SQL is more than likely generating a bad query plan, affecting performance very badly. Updating statistics with a higher sample size has fixed several different performance issues encountered.

A good example is our ETL process, in which for a high-profile client about 30 million free-text invoices are being migrated from a legacy system. The migrated data is lacking certain data or details, leaving certain fields empty or all get the same value. Also source document lines are not being migrated, hence refrecid fields pointing to these source document line records are left empty. New data generated by D365FO will however have all that information. In the first days after a go-live, some data is clearly skewed: 27M migrated FTI vs. little to no newly generated FTI. As a result, SQL server starts making bad query plans when querying against newly generated data, hurting performance really bad. Updating statistics at that point with a higher sample size fixes the performance as SQL starts generating a correct query plan. The impact cannot be underestimated: 3min for a query vs. 0.12ms.

Typical examples of indexes that require a higher sample size (or in this case, FULL SCAN)


Requesting to add two extra parameters in LCS under Environment Monitoring > SQL Insights > Actions > Update statistics on table: Index name & sample size
Needs Votes



Another example of where this would be beneficial is for DMF incremental exports. We have had a number of incidences of incremental export performance reducing drastically (execution time going from seconds to 20mins+) due to a bad query plan.

The resolution has been to either get Microsoft to run update statistics with fullscan on the affected tables or to create a new export project definition (requiring reconfiguration of the consuming application).

Being able to run the statistics update with full scan from LCS will reduce the time taken to resolve this for the customer when these business critical exports are impacted.

Category: Lifecycle Services