How to determine whether cardinality estimation issues are affecting Parallel Data Warehouse (APS) performance

Applies to: SQL Server 2012 Parallel Data Warehouse (APS)SQL Server 2008 R2 Parallel Data Warehouse


In Parallel Data Warehouse, you have a query that has one or more distributions running exponentially longer than other distributions of the same query. You have ruled out data skew and general statistics as causes of this issue. However, the problem may involve issues within the cardinality estimator (CE) itself. Because cost estimates are based on the cardinality estimates, this could lead to a poor planning decision.

To determine whether the cardinality estimator is involved in this problem, do one of the following on a compute node where the issues are occurring:
  • Compare the actual execution plan (SET STATISTICS PROFILE ON) with the estimated plan (SET SHOWPLAN_ALL ON).
  • Compare the execution plan for a fast distribution with the execution plan for a slow distribution for the same step. Specifically, compare the estimated rows vs. the actual rows that are produced by each operator.

Note You may also notice that the join order changes for certain tables (hash join only). However, that typically does not affect performance. 

More Information

If the appliance has Appliance Update 3 installed, and the issue occurs on all or most of the distributions, another potential troubleshooting step is to enable trace flag 9481. This trace flag should not be left enabled as a long-term solution, as the flag may have other performance effects on the appliance. The flag's use is not officially supported in Parallel Data Warehouse. If the issue does not occur when this trace flag is enabled, that may confirm that the issue involves the CE and that more investigation is required.