Troubleshooting practice questions

You are troubleshooting a slow-running query in Azure Synapse Analytics dedicated SQL pool. The query joins a large fact table (hash-distributed on ProductID) with a small dimension table (replicated). Upon reviewing the query plan, you see a 'ShuffleMove' operation. What is the most likely cause of the slow performance?

Trap 1: The query is running with a low resource class

Resource class affects concurrency, not shuffle.

Trap 2: Result set caching is enabled

Caching would improve performance, not cause shuffle.

Trap 3: Statistics are outdated on the dimension table

Outdated statistics may cause suboptimal plans but not necessarily shuffle.

A
The dimension table is not actually replicated due to its size exceeding the replication threshold
If the dimension table is too large to be replicated, it will be distributed and cause shuffle.
B
The query is running with a low resource class
Why wrong: Resource class affects concurrency, not shuffle.
C
Result set caching is enabled
Why wrong: Caching would improve performance, not cause shuffle.
D
Statistics are outdated on the dimension table
Why wrong: Outdated statistics may cause suboptimal plans but not necessarily shuffle.

Question 2mediummultiple choice

Your Azure Synapse Analytics dedicated SQL pool is experiencing performance degradation. Queries that previously completed in seconds now take minutes. You suspect memory pressure and concurrency issues. What should you first review to diagnose the problem?

Trap 1: sys.dm_pdw_resource_waits

Shows resource waits aggregated, not current queries

Trap 2: sys.dm_pdw_waits

Shows waits, not active queries

Trap 3: sys.dm_pdw_query_stats_xe

Shows query execution statistics, not current state

A
sys.dm_pdw_resource_waits
Why wrong: Shows resource waits aggregated, not current queries
B
sys.dm_pdw_waits
Why wrong: Shows waits, not active queries
C
sys.dm_pdw_query_stats_xe
Why wrong: Shows query execution statistics, not current state
D
sys.dm_pdw_exec_requests
Shows currently running queries with resource consumption

Question 3hardmultiple choice

You are troubleshooting a Synapse Spark notebook that fails when reading Parquet files from Azure Data Lake Storage Gen2. The error message indicates 'Permission denied'. The notebook uses a managed identity (System-assigned) for authentication. The Data Lake Storage account has a firewall enabled with 'Allow Azure services on the trusted services list' turned on. The storage account's RBAC role assignments include 'Storage Blob Data Contributor' for the managed identity. What is the most likely cause of the failure?

Trap 1: Parquet files require special permissions that are not granted by…

RBAC roles cover data operations for Parquet files.

Trap 2: The managed identity has not been granted the 'Storage Blob Data…

'Storage Blob Data Contributor' includes read permissions, so this is not the issue.

Trap 3: The notebook is using an incorrect connection string with account…

The notebook uses managed identity authentication, not account key.

A
Parquet files require special permissions that are not granted by RBAC roles
Why wrong: RBAC roles cover data operations for Parquet files.
B
The managed identity has not been granted the 'Storage Blob Data Reader' role in addition to 'Storage Blob Data Contributor'
Why wrong: 'Storage Blob Data Contributor' includes read permissions, so this is not the issue.
C
The storage account firewall does not have a 'Resource instances' exception for the managed identity
Firewall rules require explicit addition of the managed identity as a resource instance to allow access when the firewall is enabled.
D
The notebook is using an incorrect connection string with account key
Why wrong: The notebook uses managed identity authentication, not account key.

Question 4mediummultiple choice

Your team is troubleshooting slow query performance on a dedicated SQL pool in Azure Synapse Analytics. The query uses a hash-distributed fact table with 60 distributions. After reviewing the execution plan, you notice a high number of data moves. Which action would most likely reduce data movement?

Trap 1: Change the distribution type to round-robin.

Changing to round-robin places rows randomly across distributions, which would increase data movement during joins because no collocation exists.

Trap 2: Update statistics on all columns used in joins.

Updating statistics improves query optimization but does not structurally reduce data movement; the distribution scheme remains unchanged.

Trap 3: Increase the number of distributions to 120.

Increasing the number of distributions does not align the distribution key with the join column, so data movement remains high.

A
Change the distribution type to round-robin.
Why wrong: Changing to round-robin places rows randomly across distributions, which would increase data movement during joins because no collocation exists.
B
Update statistics on all columns used in joins.
Why wrong: Updating statistics improves query optimization but does not structurally reduce data movement; the distribution scheme remains unchanged.
C
Increase the number of distributions to 120.
Why wrong: Increasing the number of distributions does not align the distribution key with the join column, so data movement remains high.
D
Redistribute the fact table on the join column using hash distribution.
Redistributing the fact table on the join column using hash distribution ensures collocation of matching rows, directly reducing data movement.

Question 5hardmulti select

Which THREE measures should you implement to monitor and optimize the performance of Azure Data Lake Storage Gen2?

Trap 1: Enable Network Security Group flow logs for the storage account…

NSG flow logs are for network traffic analysis, not storage performance.

Trap 2: Configure lifecycle management policies to move cold data to…

Lifecycle management optimizes cost, not performance monitoring.

A
Enable Network Security Group flow logs for the storage account subnet.
Why wrong: NSG flow logs are for network traffic analysis, not storage performance.
B
Enable Storage Insights to monitor capacity and transactions.
Storage Insights provides performance metrics.
C
Configure lifecycle management policies to move cold data to archive tier.
Why wrong: Lifecycle management optimizes cost, not performance monitoring.
D
Use Azure Storage Analytics logs to analyze latency and request rate.
Storage Analytics provides performance data.
E
Enable Azure Monitor diagnostic settings to capture read and write requests.
Diagnostic logs help analyze request patterns.

Question 6hardmultiple choice

You are troubleshooting a slow-running pipeline in Azure Data Factory. The pipeline copies data from an on-premises SQL Server to Azure Synapse Analytics using a self-hosted integration runtime. The copy activity is using the 'Auto' copy method. You notice that network bandwidth is limited. Which configuration change would most likely improve performance?

Trap 1: Increase the Data Integration Units (DIU) for the copy activity

DIU applies to Azure IR, not self-hosted IR.

Trap 2: Change the copy method to 'Bulk insert'

Bulk insert is not available for on-premises sources.

Trap 3: Set the Fault Tolerance option to skip incompatible rows

This handles errors, not performance.

A
Enable staging using Azure Blob Storage and use PolyBase to load into Synapse
Staging improves performance by using parallel uploads to Blob Storage.
B
Increase the Data Integration Units (DIU) for the copy activity
Why wrong: DIU applies to Azure IR, not self-hosted IR.
C
Change the copy method to 'Bulk insert'
Why wrong: Bulk insert is not available for on-premises sources.
D
Set the Fault Tolerance option to skip incompatible rows
Why wrong: This handles errors, not performance.

Question 7mediummultiple choice

Your organization uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. You notice that queries are taking longer than expected. You need to identify which queries are consuming the most resources and optimize them. What should you do first?

Trap 1: Use query hints to optimize execution plans.

Hints require knowing which queries to optimize.

Trap 2: Enable diagnostic settings and send query logs to Log Analytics.

Logs are for historical analysis, not immediate.

Trap 3: Create statistics on all columns used in queries.

Serverless SQL pool auto-creates statistics.

A
Use query hints to optimize execution plans.
Why wrong: Hints require knowing which queries to optimize.
B
Query the sys.dm_exec_requests DMV to view running queries and their resource usage.
DMVs give real-time insight into resource consumption.
C
Enable diagnostic settings and send query logs to Log Analytics.
Why wrong: Logs are for historical analysis, not immediate.
D
Create statistics on all columns used in queries.
Why wrong: Serverless SQL pool auto-creates statistics.

Question 8hardmultiple choice

Refer to the exhibit. A Stream Analytics job shows increasing watermark delay and input deserialization errors. Which action should be taken first to troubleshoot?

Exhibit

Azure Stream Analytics job diagnostics log:

{
  "time": "2023-08-01T12:00:00Z",
  "properties": {
    "jobId": "job-123",
    "jobName": "IoTStreamJob",
    "events": [
      {
        "time": "2023-08-01T11:59:00Z",
        "type": "WatermarkDelay",
        "properties": {
          "watermarkDelaySeconds": 120,
          "maxWatermarkDelaySeconds": 300
        }
      },
      {
        "time": "2023-08-01T11:59:30Z",
        "type": "InputDeserializationError",
        "properties": {
          "source": "iothub",
          "count": 15
        }
      }
    ],
    "jobOutputWatermark": "2023-08-01T11:57:00Z"
  }
}

Trap 1: Change the output to a different sink

Output sink is unrelated to input deserialization errors.

Trap 2: Increase the number of Streaming Units (SUs)

Scaling may help throughput but does not fix deserialization issues.

Trap 3: Set the watermark delay threshold higher

That only masks the symptom; the underlying issue remains.

A
Check the input data schema and ensure it matches the query
Deserialization errors are often due to schema mismatch; fixing the data or query resolves the root cause.
B
Change the output to a different sink
Why wrong: Output sink is unrelated to input deserialization errors.
C
Increase the number of Streaming Units (SUs)
Why wrong: Scaling may help throughput but does not fix deserialization issues.
D
Set the watermark delay threshold higher
Why wrong: That only masks the symptom; the underlying issue remains.

Question 9hardmultiple choice

You are troubleshooting a slow-running Azure Synapse Pipeline that loads data from Azure Blob Storage into a dedicated SQL pool using a Copy activity. The source is a set of CSV files totaling 500 GB. The sink is a staging table with a clustered columnstore index. The pipeline takes 4 hours to complete. You need to reduce the execution time to under 1 hour. What should you do?

Trap 1: Increase the Data Integration Units (DIU) in the Copy activity to…

Increasing DIU improves parallelism but the bottleneck is often the sink; PolyBase is more effective.

Trap 2: Increase the dedicated SQL pool's DWU setting to the highest tier.

Scaling up improves query performance but does not change the copy method; the load may still be slow.

Trap 3: Partition the staging table on a date column.

Partitioning helps with query performance and partition switching, not with the initial load speed.

A
Enable PolyBase in the Copy activity sink settings.
PolyBase provides the fastest way to load data into dedicated SQL pool by leveraging its parallel architecture.
B
Increase the Data Integration Units (DIU) in the Copy activity to the maximum.
Why wrong: Increasing DIU improves parallelism but the bottleneck is often the sink; PolyBase is more effective.
C
Increase the dedicated SQL pool's DWU setting to the highest tier.
Why wrong: Scaling up improves query performance but does not change the copy method; the load may still be slow.
D
Partition the staging table on a date column.
Why wrong: Partitioning helps with query performance and partition switching, not with the initial load speed.

Question 10mediummultiple choice

You are troubleshooting a slow-running pipeline in Azure Data Factory that uses a Copy activity to transfer data from Azure Blob Storage to Azure Synapse Analytics. The pipeline processes about 100 GB of CSV files. The copy performance is poor even though the source and sink are in the same region. What is the most likely cause?

Trap 1: The source and sink are in different Azure regions

They are in the same region.

Trap 2: The Data Integration Unit (DIU) setting is too low

DIU affects throughput but PolyBase is more impactful.

Trap 3: The source files are compressed

Compression can actually improve performance.

A
The copy activity is not using staging and PolyBase
PolyBase dramatically improves load performance.
B
The source and sink are in different Azure regions
Why wrong: They are in the same region.
C
The Data Integration Unit (DIU) setting is too low
Why wrong: DIU affects throughput but PolyBase is more impactful.
D
The source files are compressed
Why wrong: Compression can actually improve performance.

Question 11hardmultiple choice

You are troubleshooting slow COPY INTO performance in Azure Synapse Analytics dedicated SQL pool when loading Parquet files from Azure Data Lake Storage Gen2. The files are 1 GB each. What should you do to improve performance?

Trap 1: Reduce the file size to 100 MB to increase parallelism

Reducing file size to 100 MB does not directly address the parallelism issue; the problem is too few files to match the 60 distributions, not the file size.

Trap 2: Use PolyBase instead of COPY INTO

PolyBase is an older technology; COPY INTO is the recommended and more performant approach for loading Parquet files in Synapse dedicated SQL pool.

Trap 3: Disable parallel processing in the COPY command

Disabling parallel processing would reduce performance by forcing sequential execution, which is counterproductive for large data loads.

A
Reduce the file size to 100 MB to increase parallelism
Why wrong: Reducing file size to 100 MB does not directly address the parallelism issue; the problem is too few files to match the 60 distributions, not the file size.
B
Use PolyBase instead of COPY INTO
Why wrong: PolyBase is an older technology; COPY INTO is the recommended and more performant approach for loading Parquet files in Synapse dedicated SQL pool.
C
Increase the number of files to match the number of distributions
Correct. With 1 GB files, there are too few files to fully utilize all 60 distributions. Increasing the number of files to at least 60 ensures each distribution receives data, maximizing parallelism and improving performance.
D
Disable parallel processing in the COPY command
Why wrong: Disabling parallel processing would reduce performance by forcing sequential execution, which is counterproductive for large data loads.

Question 12mediummultiple choice

You are troubleshooting a slow-running Azure Data Factory pipeline that copies data from an Azure SQL Database to ADLS Gen2. The pipeline uses a copy activity with the default settings. The source table has 10 million rows. Which optimization should you apply first?

Trap 1: Set the 'parallel copies' property to 10.

Parallel copies are automatically tuned; manual setting may not yield significant improvement.

Trap 2: Replace the copy activity with a mapping data flow.

Data flows are for transformations, not simple copies; they add overhead.

Trap 3: Increase the data integration unit (DIU) to maximum.

DIU increase costs more and may not address the bottleneck from SQL DB.

A
Set the 'parallel copies' property to 10.
Why wrong: Parallel copies are automatically tuned; manual setting may not yield significant improvement.
B
Replace the copy activity with a mapping data flow.
Why wrong: Data flows are for transformations, not simple copies; they add overhead.
C
Increase the data integration unit (DIU) to maximum.
Why wrong: DIU increase costs more and may not address the bottleneck from SQL DB.
D
Enable staged copy using an Azure Blob Storage staging location.
Staging allows data to be transferred via Blob Storage, which improves throughput for SQL to ADLS copies.

Question 13easymultiple choice

You are troubleshooting an Azure Databricks job that writes data to Azure Data Lake Storage Gen2. The job fails with '403 Forbidden' error. The Databricks workspace uses a managed identity (system-assigned) for authentication. What should you verify?

Trap 1: The storage account name is correct

Would give 404, not 403

Trap 2: The storage account firewall is configured to allow Azure services

Would give network-level error, not 403

Trap 3: A private endpoint is configured between Databricks and the storage…

Not required for access

A
The storage account name is correct
Why wrong: Would give 404, not 403
B
The storage account firewall is configured to allow Azure services
Why wrong: Would give network-level error, not 403
C
A private endpoint is configured between Databricks and the storage account
Why wrong: Not required for access
D
The managed identity has 'Storage Blob Data Contributor' role assigned to the storage account
RBAC role is required for write access