Repeated BI workloads

You must be aware of the guidelines for moving Repeated BI workloads.

Follow these guidelines for moving Repeated BI workloads

  • Avoid selecting workloads that download data.
  • Ensure that the datasets are compressed parquet and not in text or ORC format.
  • Select workloads that are output of BI queries fired 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 as recommended for TPC - DS.
  • 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 EXEC_SINGLE_NODE_ROWS_THRESHOLD to at least 5000 for small query optimization.
  • Schedule weekly statistic data collection for better query performance.