Troubleshooting practice questions

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.

Round-robin spreads rows evenly but destroys co-location, so every join must shuffle all 60 distributions, increasing data movement. Round-robin suits staging or loading tables with no join keys, not fact tables repeatedly joined on a shared column.

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

Statistics inform cardinality estimates, not distribution placement; stale stats cause poor plans but cannot eliminate data movement when joined columns are not the distribution key. Updating statistics is the right fix for skewed estimates producing bad join strategies, not for a hash-distributed table whose joins require shuffling.

Trap 3: Increase the number of distributions to 120.

Raising distributions to 120 splits each distribution's rows further, multiplying shuffle operations across more nodes without co-locating join keys. More distributions help when a single distribution exceeds compute capacity, not when joins move data because keys differ from the distribution column.

A
Change the distribution type to round-robin.
Why it fails: Round-robin spreads rows evenly but destroys co-location, so every join must shuffle all 60 distributions, increasing data movement. Round-robin suits staging or loading tables with no join keys, not fact tables repeatedly joined on a shared column.
B
Update statistics on all columns used in joins.
Why it fails: Statistics inform cardinality estimates, not distribution placement; stale stats cause poor plans but cannot eliminate data movement when joined columns are not the distribution key. Updating statistics is the right fix for skewed estimates producing bad join strategies, not for a hash-distributed table whose joins require shuffling.
C
Increase the number of distributions to 120.
Why it fails: Raising distributions to 120 splits each distribution's rows further, multiplying shuffle operations across more nodes without co-locating join keys. More distributions help when a single distribution exceeds compute capacity, not when joins move data because keys differ from the distribution column.
D
Redistribute the fact table on the join column using hash distribution.
Hash-distributing the fact table on the join column co-locates matching rows in the same distribution, so joins execute locally instead of shuffling data across the 60 distributions. This directly reduces the high data movement identified in the execution plan.

Question 2hardmultiple 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

Switching the output sink cannot resolve input deserialization errors, since failures occur while reading and parsing the source stream before any output write. Sink changes suit output throttling or write failures, not malformed inbound events.

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

Adding Streaming Units scales compute throughput for CPU-bound or partitioned workloads, but deserialization errors originate from malformed input records, which extra SUs cannot parse. SU increases suit genuine backlog from insufficient parallel processing, not format failures.

Trap 3: Set the watermark delay threshold higher

Raising the watermark threshold conceals the symptom without addressing the deserialization errors corrupting the input stream; those malformed events still fail to parse. Threshold tuning suits late-arriving-but-valid data in event-ordering scenarios, not schema or format mismatches.

A
Check the input data schema and ensure it matches the query
Deserialization errors indicate the incoming event format cannot be parsed against the declared input schema, which directly inflates watermark delay as unreadable events stall progress. Verifying that the input data schema matches the query's field definitions and serialisation format (JSON, Avro, CSV) addresses the root cause before scaling or partition tuning.
B
Change the output to a different sink
Why it fails: Switching the output sink cannot resolve input deserialization errors, since failures occur while reading and parsing the source stream before any output write. Sink changes suit output throttling or write failures, not malformed inbound events.
C
Increase the number of Streaming Units (SUs)
Why it fails: Adding Streaming Units scales compute throughput for CPU-bound or partitioned workloads, but deserialization errors originate from malformed input records, which extra SUs cannot parse. SU increases suit genuine backlog from insufficient parallel processing, not format failures.
D
Set the watermark delay threshold higher
Why it fails: Raising the watermark threshold conceals the symptom without addressing the deserialization errors corrupting the input stream; those malformed events still fail to parse. Threshold tuning suits late-arriving-but-valid data in event-ordering scenarios, not schema or format mismatches.

Question 3easymultiple 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

An incorrect storage account name produces a name-resolution or not-found error, not 403 Forbidden, which signals authenticated-but-unauthorised access. It is tempting because typos are a frequent misconfiguration, and would be correct if the job reported that the account or container could not be located.

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

Allowing Azure services permits traffic from trusted platform services, but the workspace's system-assigned managed identity still needs a role assignment granting access; the firewall alone does not authorise it. It is tempting because firewall rules commonly cause storage failures, and would be correct if the error were a network timeout.

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

A private endpoint affects network routing, not authorisation; a missing one yields timeouts or connection failures, not 403. It is tempting because private endpoints are commonly configured for secure Databricks storage access, and would be the answer if the error were a connectivity or DNS failure.

A
The storage account name is correct
Why it fails: An incorrect storage account name produces a name-resolution or not-found error, not 403 Forbidden, which signals authenticated-but-unauthorised access. It is tempting because typos are a frequent misconfiguration, and would be correct if the job reported that the account or container could not be located.
B
The storage account firewall is configured to allow Azure services
Why it fails: Allowing Azure services permits traffic from trusted platform services, but the workspace's system-assigned managed identity still needs a role assignment granting access; the firewall alone does not authorise it. It is tempting because firewall rules commonly cause storage failures, and would be correct if the error were a network timeout.
C
A private endpoint is configured between Databricks and the storage account
Why it fails: A private endpoint affects network routing, not authorisation; a missing one yields timeouts or connection failures, not 403. It is tempting because private endpoints are commonly configured for secure Databricks storage access, and would be the answer if the error were a connectivity or DNS failure.
D
The managed identity has 'Storage Blob Data Contributor' role assigned to the storage account
The 403 arises because the system-assigned managed identity lacks data-plane authorisation on the storage account. Granting Storage Blob Data Contributor at the account or container scope supplies the OAuth permissions needed for writes to Data Lake Storage Gen2.

Question 4hardmulti 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 record allowed and denied IP traffic for security auditing and troubleshooting; they capture no storage performance counters such as transactions, latency or throughput. It is tempting because flow logs are a common diagnostic for network-reachable services, and would be correct when investigating connectivity or exfiltration.

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

Lifecycle management moves blobs to cooler tiers to reduce storage cost; it does not monitor or optimise Data Lake Storage Gen2 performance metrics such as latency or throughput. It is tempting as a data-management control, and would be correct when the goal is cost reduction rather than performance tuning.

A
Enable Network Security Group flow logs for the storage account subnet.
Why it fails: NSG flow logs record allowed and denied IP traffic for security auditing and troubleshooting; they capture no storage performance counters such as transactions, latency or throughput. It is tempting because flow logs are a common diagnostic for network-reachable services, and would be correct when investigating connectivity or exfiltration.
B
Enable Storage Insights to monitor capacity and transactions.
Storage Insights provides capacity and transaction metrics for Azure Data Lake Storage Gen2, directly satisfying the requirement to monitor performance. It surfaces throttling, latency and request-volume trends through Azure Monitor workbooks, letting you detect hotspots and optimise partitioning or request patterns before throughput degrades.
C
Configure lifecycle management policies to move cold data to archive tier.
Why it fails: Lifecycle management moves blobs to cooler tiers to reduce storage cost; it does not monitor or optimise Data Lake Storage Gen2 performance metrics such as latency or throughput. It is tempting as a data-management control, and would be correct when the goal is cost reduction rather than performance tuning.
D
Use Azure Storage Analytics logs to analyze latency and request rate.
Azure Storage Analytics logs capture per-request latency and transaction metrics for Data Lake Storage Gen2, directly satisfying the requirement to monitor performance. Enabling logging on the storage account exposes request rate and end-to-end latency data, which you then analyse to identify throttling or slow operations before optimising workloads.
E
Enable Azure Monitor diagnostic settings to capture read and write requests.
Diagnostic settings route resource logs, including read and write request metrics, to Log Analytics, Storage or Event Hubs, enabling analysis of access patterns and latency. This satisfies the requirement to monitor Data Lake Storage Gen2 performance through request-level telemetry.

Question 5mediummultiple 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

The stem states both source and sink sit in the same region, so cross-region egress cannot be the bottleneck. Cross-region placement is the genuine cause when a Copy activity's throughput is capped by inter-region network latency and bandwidth, which is not the case here.

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

A low DIU count limits parallel connections, but the stem gives no evidence of this setting; the question asks for the most likely cause of poor throughput on 100 GB of CSV. DIUs are the correct lever when parallelism is deliberately throttled or defaults are insufficient for large files.

Trap 3: The source files are compressed

Compressed source files reduce bytes transferred, so they cannot explain slow copy performance; if anything they improve throughput. Compression is the right consideration when diagnosing CPU-bound decompression overhead or when choosing whether to decompress before the Copy activity.

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 it fails: The stem states both source and sink sit in the same region, so cross-region egress cannot be the bottleneck. Cross-region placement is the genuine cause when a Copy activity's throughput is capped by inter-region network latency and bandwidth, which is not the case here.
C
The Data Integration Unit (DIU) setting is too low
Why it fails: A low DIU count limits parallel connections, but the stem gives no evidence of this setting; the question asks for the most likely cause of poor throughput on 100 GB of CSV. DIUs are the correct lever when parallelism is deliberately throttled or defaults are insufficient for large files.
D
The source files are compressed
Why it fails: Compressed source files reduce bytes transferred, so they cannot explain slow copy performance; if anything they improve throughput. Compression is the right consideration when diagnosing CPU-bound decompression overhead or when choosing whether to decompress before the Copy activity.

Question 6mediummultiple choice

You are troubleshooting a failed Azure Synapse Pipeline execution. The pipeline uses a Copy activity to load data from an on-premises SQL Server to Azure Data Lake Storage Gen2. The error indicates a 'Connection timeout' to the on-premises source. The Integration Runtime is Self-Hosted and has been running successfully for months. What is the most likely cause?

Trap 1: The SQL Server authentication credentials have expired.

Expired credentials produce authentication failures, not connection timeouts, because the TCP connection to the source is never established. It is tempting since credential expiry is a common pipeline failure, but it would be correct only if the error were a login failure rather than a timeout.

Trap 2: The Self-Hosted Integration Runtime is not installed.

The runtime has run successfully for months, so it is installed and registered; an absent runtime would fail immediately at pipeline start, not after months of operation. It is tempting because a missing runtime does cause connectivity failures, but the stem rules this out.

Trap 3: The Azure Storage account firewall is blocking access.

The storage firewall governs access to the Azure Data Lake Gen2 sink, not the on-premises SQL Server source, so it cannot cause a source connection timeout. It is tempting because firewall rules commonly block pipeline traffic, but here the failure occurs before data reaches storage.

A
The SQL Server authentication credentials have expired.
Why it fails: Expired credentials produce authentication failures, not connection timeouts, because the TCP connection to the source is never established. It is tempting since credential expiry is a common pipeline failure, but it would be correct only if the error were a login failure rather than a timeout.
B
The Self-Hosted Integration Runtime is not installed.
Why it fails: The runtime has run successfully for months, so it is installed and registered; an absent runtime would fail immediately at pipeline start, not after months of operation. It is tempting because a missing runtime does cause connectivity failures, but the stem rules this out.
C
The on-premises network configuration has changed.
A self-hosted integration runtime that worked for months rules out credentials and installation faults. A connection timeout to the on-premises SQL Server therefore points to changed network configuration, such as firewall rules, routing, or proxy settings, blocking the runtime's outbound path to the source.
D
The Azure Storage account firewall is blocking access.
Why it fails: The storage firewall governs access to the Azure Data Lake Gen2 sink, not the on-premises SQL Server source, so it cannot cause a source connection timeout. It is tempting because firewall rules commonly block pipeline traffic, but here the failure occurs before data reaches storage.