Guided Troubleshooting¶

🏠 Home > 🔧 Troubleshooting > 🧭 Guided Troubleshooting

🧭 Interactive Diagnostic Guide Follow this step-by-step decision tree to diagnose and resolve common Azure Synapse Analytics issues quickly and effectively.

🎯 Quick Issue Identification¶

Start by identifying your issue category:

🔑 Authentication Issues¶

Decision Tree¶

graph TD
    A[Authentication Error] --> B{Error Type?}
    B -->|Access Denied| C{Using MSI?}
    B -->|Login Failed| D{Authentication Method?}
    B -->|Permission Error| E{Resource Type?}

    C -->|Yes| F[Check MSI Permissions]
    C -->|No| G[Check Service Principal]

    D -->|Azure AD| H[Verify Azure AD Setup]
    D -->|SQL Auth| I[Check SQL Credentials]
    D -->|Key/Token| J[Validate API Keys]

    E -->|Storage| K[Check RBAC Roles]
    E -->|Synapse| L[Check Synapse RBAC]
    E -->|Database| M[Check Database Permissions]

    F --> N[Solution: Grant Required Roles]
    G --> O[Solution: Update SP Credentials]
    H --> P[Solution: Azure AD Configuration]
    I --> Q[Solution: Reset SQL Password]
    J --> R[Solution: Regenerate Keys]
    K --> S[Solution: Assign Storage Roles]
    L --> T[Solution: Assign Synapse Roles]
    M --> U[Solution: Grant DB Permissions]

Diagnostic Steps¶

Step 1: Identify Authentication Method¶

Question: What authentication method are you using?

• Azure AD (Recommended) → Go to Step 2A
• Managed Identity → Go to Step 2B
• Service Principal → Go to Step 2C
• SQL Authentication → Go to Step 2D

Step 2A: Azure AD Authentication¶

Symptoms: AADSTS* error codes, token expiration, consent errors

Diagnostic Actions:

1. Verify Azure AD registration:

az ad app show --id <application-id>

1. Check token validity:

az account get-access-token --resource https://database.windows.net/

1. Verify user permissions:

az role assignment list --assignee <user-id> --scope <resource-id>

Solutions:

• Error AADSTS50105 (User not assigned): Assign user to application
• Error AADSTS50126 (Invalid credentials): Reset password or credentials
• Error AADSTS65001 (Consent required): Grant admin consent

📚 Related: Authentication Troubleshooting

Step 2B: Managed Identity¶

Symptoms: Access denied with MSI, identity not found

Diagnostic Actions:

1. Verify MSI is enabled:

az synapse workspace show --name <workspace-name> --resource-group <rg-name> --query identity

1. Check MSI role assignments:

az role assignment list --assignee <principal-id>

Solutions:

• Enable managed identity on Synapse workspace
• Assign required roles: Storage Blob Data Contributor, Synapse Contributor
• Wait up to 5 minutes for role propagation

📚 Related: Security Best Practices

Step 2C: Service Principal¶

Symptoms: Client credential flow errors, secret expiration

Diagnostic Actions:

1. Verify service principal exists:

az ad sp show --id <app-id>

1. Check secret expiration:

az ad app credential list --id <app-id>

Solutions:

• Regenerate expired secrets
• Update application credentials in Key Vault
• Verify client ID and tenant ID are correct

📚 Related: Reference Security

Step 2D: SQL Authentication¶

Symptoms: Login failed for user, password policy errors

Diagnostic Actions:

1. Test SQL connection:

SELECT SYSTEM_USER, ORIGINAL_LOGIN();

1. Check login exists:

SELECT name, type_desc FROM sys.server_principals WHERE name = '<username>';

Solutions:

• Reset SQL admin password via Azure Portal
• Ensure password meets complexity requirements
• Check if account is locked or disabled

📚 Related: SQL Performance

🌐 Connectivity Issues¶

Decision Tree¶

graph TD
    A[Connection Error] --> B{Error Message?}
    B -->|Timeout| C{Public/Private Access?}
    B -->|Connection Refused| D{Firewall Rules?}
    B -->|Host Not Found| E{DNS Resolution?}

    C -->|Public| F[Check Public Access]
    C -->|Private| G[Check Private Endpoint]

    D -->|Configured| H[Verify IP Whitelisted]
    D -->|Not Configured| I[Add Firewall Rule]

    E -->|Working| J[Check Network Path]
    E -->|Failing| K[Check DNS Config]

    F --> L[Solution: Enable Public Access]
    G --> M[Solution: Configure VNet]
    H --> N[Solution: Update Firewall]
    I --> O[Solution: Create Rule]
    J --> P[Solution: Check NSG/Route]
    K --> Q[Solution: Fix DNS]

Diagnostic Steps¶

Step 1: Test Basic Connectivity¶

Question: Can you reach the endpoint?

# Test DNS resolution
nslookup <workspace-name>.sql.azuresynapse.net

# Test port connectivity
Test-NetConnection -ComputerName <workspace-name>.sql.azuresynapse.net -Port 1433

# Test HTTPS endpoint
curl https://<workspace-name>.dev.azuresynapse.net

Results:

• ✅ Success → Go to Step 2A: Azure AD Authentication
• ❌ DNS Failed → Go to DNS Issues
• ❌ Port Blocked → Go to Firewall Issues
• ❌ HTTPS Failed → Go to Certificate Issues

DNS Issues¶

Diagnostic Actions:

1. Verify DNS settings:

ipconfig /all

1. Check private DNS zone:

az network private-dns zone show --name privatelink.sql.azuresynapse.net --resource-group <rg-name>

1. Verify DNS record:

az network private-dns record-set a list --zone-name privatelink.sql.azuresynapse.net --resource-group <rg-name>

Solutions:

• Create private DNS zone if using Private Link
• Link DNS zone to VNet
• Flush DNS cache: ipconfig /flushdns

📚 Related: Connectivity Troubleshooting

Firewall Issues¶

Diagnostic Actions:

1. Check workspace firewall rules:

az synapse workspace firewall-rule list --workspace-name <workspace-name> --resource-group <rg-name>

1. Verify client IP:

curl ifconfig.me

Solutions:

• Add client IP to firewall rules
• Enable "Allow Azure services and resources to access this workspace"
• For private endpoints, disable public network access

📚 Related: Network Security

Certificate Issues¶

Symptoms: SSL/TLS errors, certificate validation failures

Solutions:

• Update root certificates on client machine
• Add connection string parameter: TrustServerCertificate=True (development only)
• Verify server certificate is valid

⚡ Performance Issues¶

Decision Tree¶

graph TD
    A[Slow Performance] --> B{Component?}
    B -->|Query| C{Query Type?}
    B -->|Pipeline| D{Pipeline Stage?}
    B -->|Spark Job| E{Job Phase?}

    C -->|SELECT| F[Check Query Plan]
    C -->|INSERT/UPDATE| G[Check Table Design]
    C -->|JOIN| H[Check Statistics]

    D -->|Copy Activity| I[Check Throughput]
    D -->|Data Flow| J[Check Partitioning]
    D -->|Lookup| K[Check Query Opt]

    E -->|Stage Time| L[Check Shuffle]
    E -->|Task Skew| M[Check Partitioning]
    E -->|Memory| N[Check Config]

    F --> O[Solution: Optimize Query]
    G --> P[Solution: Add Indexes]
    H --> Q[Solution: Update Stats]
    I --> R[Solution: Increase DIUs]
    J --> S[Solution: Repartition]
    K --> T[Solution: Use Parameters]
    L --> U[Solution: Broadcast Join]
    M --> V[Solution: Repartition Data]
    N --> W[Solution: Tune Memory]

Diagnostic Steps¶

Step 1: Identify Performance Bottleneck¶

Question: What type of operation is slow?

• SQL Query → SQL Performance Diagnostics
• Spark Job → Spark Performance Diagnostics
• Pipeline → Pipeline Performance Diagnostics

SQL Performance Diagnostics¶

Collect Query Metrics:

-- Get query execution plan
SET STATISTICS PROFILE ON;
<your-query>
SET STATISTICS PROFILE OFF;

-- Check query statistics
SELECT
    query_id,
    start_time,
    end_time,
    total_elapsed_time_ms,
    row_count
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;

-- Identify expensive operations
SELECT TOP 10
    text,
    total_worker_time/execution_count AS avg_cpu_time,
    total_elapsed_time/execution_count AS avg_elapsed_time
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY avg_elapsed_time DESC;

Common Issues:

📚 Related: SQL Performance Best Practices

Spark Performance Diagnostics¶

Collect Job Metrics:

1. Access Spark UI: https://<workspace-name>.dev.azuresynapse.net/sparkui
2. Review job timeline and stages
3. Check executor metrics

Common Issues:

📚 Related: Spark Performance Best Practices

Pipeline Performance Diagnostics¶

Check Pipeline Metrics:

1. Navigate to Monitor → Pipeline runs
2. Review activity durations
3. Check data integration units (DIUs)

Common Issues:

📚 Related: Pipeline Optimization

🏞️ Delta Lake Issues¶

Common Scenarios¶

Scenario 1: Table Not Found¶

Symptoms: Table or view not found errors

Diagnostic Steps:

# Check if table exists in metastore
spark.sql("SHOW TABLES IN <database>").display()

# Check Delta Lake path
dbutils.fs.ls("/mnt/delta/<table-path>")

# Verify Delta log
dbutils.fs.ls("/mnt/delta/<table-path>/_delta_log")

Solutions:

• Refresh table metadata: REFRESH TABLE <table_name>
• Repair table: MSCK REPAIR TABLE <table_name>
• Recreate table if corrupted

📚 Related: Delta Lake Troubleshooting

Scenario 2: Concurrent Write Conflicts¶

Symptoms: ConcurrentAppendException, ConcurrentDeleteException

Solutions:

• Implement optimistic concurrency control
• Use merge operations instead of separate update/insert
• Enable auto-optimize for Delta tables

# Enable optimized writes
spark.conf.set("spark.databricks.delta.optimizeWrite.enabled", "true")

# Use merge for upserts
deltaTable.alias("target").merge(
    source.alias("source"),
    "target.id = source.id"
).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

Scenario 3: Performance Degradation¶

Symptoms: Slow reads, increasing query time

Diagnostic Steps:

# Check table statistics
spark.sql("DESCRIBE DETAIL <table_name>").display()

# Check file sizes
spark.sql("DESCRIBE DETAIL <table_name>").select("numFiles", "sizeInBytes").display()

Solutions:

• Run OPTIMIZE: OPTIMIZE <table_name>
• Z-ORDER on frequently filtered columns: OPTIMIZE <table_name> ZORDER BY (column1, column2)
• Vacuum old files: VACUUM <table_name> RETAIN 168 HOURS

📚 Related: Delta Lake Optimization

🔄 Pipeline Issues¶

Common Failure Patterns¶

Pattern 1: Copy Activity Failures¶

Diagnostic Questions:

1. What's the error message?
2. Is it source or sink failure?
3. Is it data-related or connectivity-related?

Common Errors:

Pattern 2: Data Flow Failures¶

Diagnostic Steps:

1. Enable debug mode in Data Flow
2. Review data preview at each transformation
3. Check cluster logs

Common Issues:

• Schema drift not handled → Enable "Allow schema drift"
• Null values causing errors → Add derived column with null handling
• Memory errors → Increase cluster size or optimize transformations

📚 Related: Pipeline Troubleshooting

☁️ Serverless SQL Issues¶

Common Problems¶

Problem 1: File Format Errors¶

Error: Failed to read parquet file or CSV parsing error

Solutions:

-- Test file format with OPENROWSET
SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://<storage>.dfs.core.windows.net/container/file.parquet',
    FORMAT = 'PARQUET'
) AS [result];

-- Handle CSV with custom settings
SELECT *
FROM OPENROWSET(
    BULK 'https://<storage>.dfs.core.windows.net/container/file.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
) AS [result];

Problem 2: Performance Issues¶

Symptoms: Slow queries on data lake files

Solutions:

• Create external tables instead of OPENROWSET
• Use partitioned data sources
• Create statistics on external tables
• Use CETAS to cache results

📚 Related: Serverless SQL Troubleshooting

🔥 Spark Issues¶

Common Job Failures¶

Failure Type 1: Out of Memory¶

Symptoms: java.lang.OutOfMemoryError, executor failures

Diagnostic Steps:

# Check executor memory configuration
spark.conf.get("spark.executor.memory")
spark.conf.get("spark.driver.memory")

# Monitor memory usage
spark.sparkContext.statusTracker().getExecutorInfos()

Solutions:

• Increase executor memory: spark.conf.set("spark.executor.memory", "8g")
• Increase driver memory: spark.conf.set("spark.driver.memory", "4g")
• Optimize data structures (use arrays instead of lists)
• Process data in smaller batches

Failure Type 2: Shuffle Failures¶

Symptoms: Fetch failed, shuffle read errors

Solutions:

• Increase shuffle partitions: spark.conf.set("spark.sql.shuffle.partitions", "200")
• Enable adaptive query execution: spark.conf.set("spark.sql.adaptive.enabled", "true")
• Use broadcast joins for small tables

📚 Related: Spark Troubleshooting

🆘 Emergency Checklist¶

When all else fails, work through this checklist:

• Check Azure Service Health
• Review Azure Monitor alerts
• Check recent changes in deployment history
• Verify quota limits haven't been reached
• Review activity logs for error patterns
• Test from different network location
• Compare working vs. non-working configuration
• Create minimal reproduction case
• Gather diagnostic information
• Open Azure support ticket

📚 Additional Resources¶

💡 Pro Tip: Keep a troubleshooting journal documenting issues and resolutions. Patterns often emerge that help prevent future problems.

Last Updated: January 2025