What's new in Analytics Platform System, a scale-out MPP data warehouse

See what’s new in the latest Appliance Updates for Microsoft® Analytics Platform System (APS). APS is a scale-out on-premises appliance that hosts MPP SQL Server Parallel Data Warehouse.

APS CU7.1

Release date - July 2018

DBCC commands do not consume concurrency slots (behavior change)

APS supports a subset of the T-SQL DBCC commands such as DBCC DROPCLEANBUFFERS. Previously, these commands would consume a concurrency slot reducing the number of user loads/queries that could be executed. The DBCC commands are now run in a local queue that do not consume a user concurrency slot improving overall query execution performance.

Replaces some metadata calls with catalog objects

Using catalog objects for metadata calls instead of using SMO has shown performance improvement in APS. Starting from CU7.1, some of these metadata calls now use catalog objects by default. This behavior can be turned off by feature switch if customers using metadata queries run into any issues.

Bug fixes

We have upgraded to SQL Server 2016 SP2 CU2 with APS CU7.1. The upgrade fixes some issues described below.

Title Description
Potential tuple mover deadlock The upgrade fixes a long standing possibility of deadlock in a distributed transaction and tuple mover background thread. After installing CU7.1, customers who used TF634 to stop tuple mover as SQL Server startup parameter or global trace flag can safely remove it.
Certain lag/lead query fails Certain queries on CCI tables with nested lag/lead functions that would error is now fixed with this upgrade.

APS AU7

Release date - May 2018

APS 2016 is a prerequisite to upgrade to AU7. The following are new features in APS AU7:

Auto-create and auto-update statistics

APS AU7 creates and updates statistics automatically, by default. To update statistics settings, administrators can use a new feature switch menu item in the Configuration Manager. The feature switch controls the auto-create, auto-update, and asynchronous update behavior of statistics. You can also update statistics settings with the ALTER DATABASE (Parallel Data Warehouse) statement.

T-SQL

Select @var is now supported. For more information, see select local variable

Query hints HASH and ORDER GROUP are now supported. For more information, see Hints(Transact-SQL) - Query

Feature Switch

APS AU7 introduces Feature Switch in Configuration Manager. AutoStatsEnabled and DmsProcessStopMessageTimeoutInSeconds are now configurable options that can be changed by Administrators.

Known Issues

With APS AU7 software, an Intel BIOS update is provided which fixes a problem described as speculative execution side-channel attacks. The attacks aim to exploit what are called Spectre and Meltdown vulnerabilities. Although packaged together with APS, the BIOS update is installed manually, and not as part of the APS AU7 software install.

Microsoft advises all customers to install the BIOS updated. Microsoft has measured the effect of Kernel Virtual Address Shadowing (KVAS), Kernel Page Table Indirection (KPTI) and Indirect Branch Prediction mitigation (IBP) on various SQL workloads in various environments. The measurements found significant degradation on some workloads. Based on the results, the recommendation is that you test the performance effect of enabling BIOS update before you deploy them in a production environment. See SQL Server guidance here.

APS 2016

This section described the new features for APS 2016-AU6.

SQL Server 2016

APS AU6 runs on the latest SQL Server 2016 release, and uses the default database compatibility level 130. SQL Server 2016 enables support for new features such as:

  • Secondary indexes for clustered columnstore indexes.
  • Kerberos for PolyBase.

T-SQL

APS AU6 supports these T-SQL compatibility improvements. These additional language elements make it easier to migrate from SQL Server and other data sources.

Data types

Window functions

Security functions

Additional functions

PolyBase/Hadoop enhancements

  • Compatibility with Hortonworks HDP 2.4 and HDP 2.5
  • Kerberos support via database scoped credentials
  • Credential support with Azure Storage Blobs

Install and upgrade enhancements

Enterprise architecture updates Upgrading your existing appliance to APS AU6 installs the latest firmware and driver updates, which include security fixes.

A new appliance from HPE or DELL includes all the latest updates plus:

  • Latest generation processor support (Broadwell)
  • Update to DDR4 DIMMs
  • Improved DIMM throughput

Integration

  • Fully Qualified Domain Name (FQDN) support makes it possible to setup a Domain trust to the appliance.
  • To use FQDN, you need to do a full upgrade and opt-in during the upgrade.

Reduced downtime Installing or upgrading to APS AU6 is faster and requires less downtime than previous releases. To reduce downtime, the install or upgrade:

  • Streamlines applying WSUS updates by using an image that contains all the updates through June 2016
  • Applies security updates with the driver and firmware updates
  • Places the latest hotfixes and the appliance verification utility (PAV) on your appliance so they are ready to install with no need to download them.