Updated Synapse Analyzer Report: Workload Management and ability to monitor large data warehouses

In May 2022 we announced the first version of Synapse Analyzer Report that will help you with table, index, and statistics information and report issues on these topics.

With help from Nick Salch, Fretz Nusan, and Synapse CSE team, today we have an update (version 2.0) to the Synapse Analyzer report:

• The addition of the Workload Management Report page to the existing report
• A new separate CTAS version of Synapse Analyzer Report for extra-large data warehouses
• Fixed below error during refresh in Power BI Desktop:
  • "Query 'Query Name’ (step 'Expanded All Tables') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

New report page - Workload Management Report:

The new version (v2.0) contains additional reports to detect issues in configuration of Workload Management settings in Synapse Dedicated Pool.

Workload Management

Following is a brief description of the Workload Management report page.

Workload Management:

This page provides information on workload management and related settings in Synapse dedicated SQL pool. It highlights some of the common warnings admins should be aware of when setting the resource and request percentage for effective workload governance. Please review issues and warnings on this page to ensure you are using the optimal strategy for your workload groups and classifiers. At any time you can click on the "?" icon on the visual to view more details on guidance.

WLM Issue Summary Table:

This table provides summary of all issues detected in your WLM environment. You can hover on each issue to find a brief description and/or click on an issue to find groups affected by the respective issue. You can also click on the URL link to find more information about workload management.

Group Details Table:

The table provides details for each workload group. Groups highlighted suggest that one or more issues for that group. You can hover on the groups flagged to find issues identified for that group.

• Flag for effective values suggests there is mismatch between config and effective value at request or resource level.
• Flag on request min config suggests mismatch in request min vs request max values.
• Flag on resource min config suggests deviations in resource min configurations.

Requests/Timeouts Summary:

The table provides snapshot of the requests and timeouts summary at the time when data was last refreshed/updated.

The table below provides details on issues that have been added for workload management.

CTAS version for XL data warehouses:

With this update we are also bringing a parallel copy of Synapse Analyzer report specifically targeted towards scenarios where Synapse Dedicated Pool database has greater than 20K tables and the normal refresh of synapse analyzer report times out after a long period (greater than 5 hours).

For this version of the report the Synapse Admin has to run the Create Table script on the dedicated Pool database which is being analyzed. The script will:

1. Create new schema named “SynapseAnalyzer” under same database
2. Execute Create Table as Select (CTAS) statements to create tables in SynapseAnalyzer schema and prepopulate it with details from the same database.
3. These tables are then used as source for the Power BI template file “Synapse best practice analyzer_with_CTAS.pbit”
4. The PBI template will then connect to SynapseAnalyzer schema and pull all the information from the tables that are prepopulated in above steps.

Important: You need to run the scripts in given order.

1. The “CreateTablesForSynapseAnalyzer_CTAS.sql” script creates new schema named “SynapseAnalyzer” and then creates all required tables as a part of CTAS
2. Only after step 1 above is successfully complete you can use “Synapse best practice analyzer_with_CTAS.pbit” for creating report using the PBI template file.

Fixed error during desktop refresh:

For many users, running load data through Power BI desktop, the “Load Data” used to result in following error:

While there is a details section in the Troubleshooting document on the Github location for troubleshooting this error, the overall first experience with the tool was not optimal. With a new version of Synapse Analyzer Report we have fixed this issue so you should not be getting the above error.

In future we plan to continue with more features and reports. Thank you very much for using Synapse Analyzer. As always, we would be happy to receive any feedback you may have on the report.