Guidelines for moving repeated BI workloads

Review the guidelines for moving Repeated BI workloads.

  • Avoid selecting workloads that download data.
  • Ensure that the datasets are compressed Parquet tables and not in text or ORC format.
  • Select workloads that are an output of BI queries submitted from any BI tools and repeated multiple times.

    You can find the repeated queries under simple query profile analysis.

  • A small to medium-sized VW is recommended for BI workloads.
  • Redirect the BI tool to point to CDW for the workloads.
  • Check that the performance of the queries in CDW is faster.
  • Set proper values for run-time filters, EXEC_TIME_LIMIT_S property, and so on as recommended for TPC-DS queries.
  • Set query timeouts and session timeouts to avoid non-responsive queries.
  • Look at the peak spilled metrics in the query profiles and depending on the value increase the data cache size and lower the scratch space (SCRATCH_LIMIT) to cache more data for better performance.
  • Increase the value of the EXEC_SINGLE_NODE_ROWS_THRESHOLD property to at least 5000 for small query optimization.
  • Schedule weekly statistic data collection for better query performance.