Diagnosing Synapse Analytics Query Errors

Dedicated pool vs serverless pool errors

Before diagnosing, identify which pool generated the error. The error messages look similar but the root causes differ.

• Dedicated SQL pool: a provisioned cluster (DW100c to DW30000c). Errors relate to resource class memory limits, statistics quality, and distribution skew.
• Serverless SQL pool: pay-per-query, used for querying data lake files. Errors relate to external table definitions, file format mismatches, and storage access.
• Apache Spark pool: errors appear in Spark driver and executor logs and relate to JVM memory, cluster configuration, and library conflicts.

In Synapse Studio, the pool name appears in the query tab header. The error message prefix also hints at the pool: dedicated pool errors include the DWU tier, while serverless errors often mention “Built-in” as the pool name.

Error: “Table does not exist” — wrong database context

Msg 208, Level 16, State 1
Invalid object name 'dbo.SalesTransactions'.

This error almost always means the query is running in the wrong database. Synapse dedicated SQL pool has a master database for administration and named user databases. External tables created in mywarehouse are not visible in master.

Check the current database context:

SELECT DB_NAME();

Switch to the correct database before running queries:

USE mywarehouse;
GO
SELECT TOP 10 * FROM dbo.SalesTransactions;

In serverless SQL pool, external tables are database-scoped. If you created the external table in myLakeDB, you must connect to myLakeDB, not to master.

List databases in the workspace:

-- Run on master
SELECT name, database_id, create_date
FROM sys.databases
ORDER BY name;

For external tables specifically, verify the table was created in the current database:

SELECT t.name, t.is_external, ds.name AS data_source
FROM sys.tables t
LEFT JOIN sys.external_tables et ON t.object_id = et.object_id
LEFT JOIN sys.external_data_sources ds ON et.data_source_id = ds.data_source_id
WHERE t.is_external = 1;

Error: “Could not allocate memory” — resource class too small

Msg 110802, Level 16, State 1
An internal DMS error occurred that caused this operation to fail.
Details: Exception: Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.MppSqlException,
Message: Could not allocate memory for the query operation.

In dedicated SQL pool, memory per query is determined by the user’s resource class. The default is smallrc, which provides the least memory but the highest concurrency.

Check the current resource class for all users:

SELECT r.name AS resource_class, m.name AS member_name
FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE r.name IN ('smallrc', 'mediumrc', 'largerc', 'xlargerc',
                 'staticrc10', 'staticrc20', 'staticrc40', 'staticrc80');

Increase the resource class for a user:

-- Add user to largerc (takes effect on next connection)
EXEC sp_addrolemember 'largerc', 'myuser';

-- Remove from smallrc
EXEC sp_droprolemember 'smallrc', 'myuser';

Approximate memory allocation by resource class at DW1000c:

For one-off large queries without permanently changing the user’s class, use workload management:

CREATE WORKLOAD GROUP BigQueryGroup
WITH (MIN_PERCENTAGE_RESOURCE = 25, CAP_PERCENTAGE_RESOURCE = 100);

CREATE WORKLOAD CLASSIFIER BigQueryClassifier
WITH (WORKLOAD_GROUP = 'BigQueryGroup', MEMBERNAME = 'myuser');

Query timeout — runaway queries and missing statistics

Long-running queries in dedicated SQL pool are almost always caused by poor query plans from missing or stale statistics. Without statistics, the optimizer guesses at row counts and may choose a disastrously inefficient join strategy.

Check statistics coverage on a table:

SELECT
  t.name AS table_name,
  c.name AS column_name,
  s.name AS stat_name,
  sp.last_updated,
  sp.rows,
  sp.rows_sampled,
  sp.modification_counter
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
LEFT JOIN sys.stats s ON t.object_id = s.object_id
LEFT JOIN sys.stats_columns sc
  ON s.object_id = sc.object_id
  AND s.stats_id = sc.stats_id
  AND c.column_id = sc.column_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE t.name = 'SalesTransactions'
ORDER BY c.column_id;

Update all statistics on a table after a bulk load:

UPDATE STATISTICS dbo.SalesTransactions;

-- Or update all user tables at once
EXEC sp_updatestats;

To find currently running long queries:

SELECT
  r.request_id,
  r.status,
  r.submit_time,
  DATEDIFF(SECOND, r.submit_time, GETDATE()) AS duration_seconds,
  r.resource_class,
  LEFT(r.command, 200) AS query_snippet
FROM sys.dm_pdw_exec_requests r
WHERE r.status NOT IN ('Completed', 'Failed', 'Cancelled')
ORDER BY r.submit_time;

Kill a runaway query by its request ID:

KILL 'QID12345';

External table errors in serverless SQL pool

External tables in serverless SQL pool query files in Azure Data Lake Storage. Three errors cover most failures.

Error 1: File path not found

External table 'dbo.SalesData' is not accessible because content of directory
cannot be listed. Error message: 'The specified path does not exist.'

The LOCATION in the external table definition must exactly match the path, including case. Check the definition:

SELECT et.name,
       ds.location AS data_source_location,
       et.location AS table_location
FROM sys.external_tables et
JOIN sys.external_data_sources ds ON et.data_source_id = ds.data_source_id
WHERE et.name = 'SalesData';

Error 2: Data format mismatch

Error converting data type NVARCHAR to numeric.

The column definition in CREATE EXTERNAL TABLE does not match the actual file data. Use OPENROWSET first to inspect the raw data before defining the table:

SELECT TOP 5 *
FROM OPENROWSET(
  BULK 'https://mydatalake.dfs.core.windows.net/raw/sales/*.parquet',
  FORMAT = 'PARQUET'
) AS raw_data;

Error 3: Storage access denied

File 'https://mydatalake.dfs.core.windows.net/raw/sales/2024/01/sales.parquet'
cannot be opened because it does not exist or you do not have permission to access it.

The managed identity used by the external data source lacks Storage Blob Data Reader. Grant the role:

STORAGE_ID=$(az storage account show \
  --name mydatalake \
  --resource-group myResourceGroup \
  --query id -o tsv)

SYNAPSE_MI=$(az synapse workspace show \
  --name myworkspace \
  --resource-group myResourceGroup \
  --query "identity.principalId" -o tsv)

az role assignment create \
  --assignee "$SYNAPSE_MI" \
  --role "Storage Blob Data Reader" \
  --scope "$STORAGE_ID"

Permission errors: column-level and row-level security

Synapse dedicated SQL pool supports column-level security (CLS) and row-level security (RLS). Both can cause unexpected query failures without obvious error messages.

Column-level security blocks access to specific columns:

Msg 230, Level 14, State 1
The SELECT permission was denied on the column 'SSN' of the object 'Customers'.

Check column-level permissions:

SELECT grantee_principal_id,
       class_desc,
       permission_name,
       state_desc,
       OBJECT_NAME(major_id) AS object_name,
       COL_NAME(major_id, minor_id) AS column_name
FROM sys.database_permissions
WHERE class = 1 AND minor_id > 0;

Row-level security does not throw an error — it silently returns fewer rows. If a query returns unexpectedly empty results, check for RLS policies:

SELECT p.name AS policy_name,
       t.name AS table_name,
       p.is_enabled
FROM sys.security_policies p
JOIN sys.tables t ON p.object_id = t.object_id;

RLS uses a predicate function to filter rows based on the current user context. If USER_NAME() does not match the predicate, zero rows are returned without any error message. Test by checking who the current user is and tracing the predicate function logic.