APS AU7 Performance Enhancements

Appliance Update 7 (AU7) for Microsoft's Analytic Platform System (APS) brings with it a couple of much anticipated performance enhancements.

The first is through new functionality by enabling AUTO UPDATE and AUTO CREATE statistics for appliance level stats objects. We always had auto stats on the compute nodes for the local SQL instances to utilize, however the process of aggregating the stats to the CTL node to be used by the MPP Query optimizer could only be triggered by a manual update or create, until now. All new databases created will have both AUTO CREATE and AUTO UPDATE stats on by default. These features can be enabled for existing databases through ALTER DATABASE commands.

Historically the number one root cause for performance issues is inaccurate statistics objects causing the optimizer to make poor cardinality estimations and therefore expensive or even unnecessary data movement operations. We recommend auto create and auto update statistics be enabled for both new and pre-existing databases. If performance is or becomes a concern, the first remediation step should be to enable auto create and auto update statistics on the database, clear the procedure cache and re-run the workload. If there is no benefit realized or in the rare case of performance regression, the statistics objects created can be dropped through T-SQL commands.

The second performance enhancement is transparent. In the process of working with many customers after APS 2016 (AU6) deployments, we identified opportunities to streamline our internal process of authorization and plan generation. The gains will be most noticeable on workloads which contain many small queries that run in several seconds or less. Testing has shown performance benefits form minimal to significant depending on the workload and number of statements executed. If your workload includes many short lived queries we recommend upgrading to AU7 to take advantage of the increased efficiencies.