High severityresource
SQL Server Error:
8645
What does this error mean?
SQL Server allocates a memory grant before executing any query that needs to sort rows or build a hash table. Error 8645 fires when a query queues for that grant and the wait exceeds the configured timeout — by default 25 times the query's estimated cost in seconds. In a data pipeline this surfaces as a failed ADF activity or a timed-out Power BI DirectQuery: the query never starts executing, it simply dies waiting in the memory grant queue. The root cause is always the same: more memory is requested across concurrent queries than SQL Server has available in the Buffer Pool for grants. The error becomes more frequent as pipeline parallelism increases.
Common causes
- 1Too many concurrent queries requesting grants simultaneously: ADF pipelines with high parallelism (degree of copy parallelism > 4) submit dozens of queries at once, each requesting a grant. The grant queue fills and late arrivals time out.
- 2Missing indexes force sort and hash operations: without a covering index, SQL Server must sort millions of rows in memory. A table scan on a 200M-row fact table can request 4–8 GB for a single Sort operator.
- 3Stale column statistics cause over-estimation: when statistics are out of date, the optimizer over-estimates row counts, which inflates the memory grant request far beyond what the query actually needs — wasting grant space and blocking other queries.
- 4max server memory set too low: on servers running both SQL Server and other services (SSAS, SSIS, OS), insufficient max server memory leaves too little for the Buffer Pool, reducing the total memory available for grants.
- 5Resource Governor not configured: without Resource Governor workload groups, OLAP queries (ETL loads, BI refreshes) and OLTP queries compete for the same memory pool. A single large ETL job can starve all other sessions.
- 6Adaptive Memory Grant Feedback not active: on SQL Server 2017+ and Azure SQL, Adaptive Memory Grant Feedback adjusts grants after the first execution. If compatibility level is below 140, this feature is disabled and every execution requests the same over-estimated grant.
- 7Large intermediate result sets in staging queries: CTAS-style staging patterns (INSERT INTO staging SELECT ... FROM large_table JOIN ...) with no intermediate aggregation generate enormous hash join operators that request grants proportional to the larger input side.
How to fix it
- 1Step 1 — Find queries currently waiting for a grant: SELECT session_id, requested_memory_kb, granted_memory_kb, queue_id, wait_order, is_next_candidate, SUBSTRING(st.text, (r.statement_start_offset/2)+1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS query_text FROM sys.dm_exec_query_memory_grants g CROSS APPLY sys.dm_exec_sql_text(g.sql_handle) st JOIN sys.dm_exec_requests r ON g.session_id = r.session_id WHERE granted_memory_kb IS NULL ORDER BY wait_order;
- 2Step 2 — Identify top memory consumers to find the blocking query: SELECT TOP 10 session_id, requested_memory_kb, granted_memory_kb, used_memory_kb, query_cost, SUBSTRING(st.text, 1, 200) AS query_snippet FROM sys.dm_exec_query_memory_grants g CROSS APPLY sys.dm_exec_sql_text(g.sql_handle) st ORDER BY COALESCE(granted_memory_kb, requested_memory_kb) DESC; — kill sessions blocking the queue if they are runaway ETL jobs: KILL <session_id>;
- 3Step 3 — Add indexes to eliminate Sort and Hash Match operators: open the actual execution plan in SSMS (Ctrl+M before running), locate Sort and Hash Match nodes, right-click the table scan or seek feeding them and select 'Missing Index Details'. Create the suggested index: CREATE INDEX IX_FactSales_DateKey_Include ON dbo.FactSales (DateKey) INCLUDE (Amount, CustomerId); — rerun the query and confirm the Sort node disappears.
- 4Step 4 — Update statistics on the offending tables to fix grant over-estimation: UPDATE STATISTICS dbo.FactSales WITH FULLSCAN; — check when statistics were last updated: SELECT name, stats_date(object_id, stats_id) AS last_updated FROM sys.stats WHERE object_id = OBJECT_ID('dbo.FactSales') ORDER BY last_updated;
- 5Step 5 — Raise max server memory if the server has headroom: EXEC sp_configure 'max server memory (MB)', 24576; RECONFIGURE; — verify current setting first: SELECT value_in_use FROM sys.configurations WHERE name = 'max server memory (MB)'; — leave at least 4 GB for the OS; on Azure SQL Managed Instance adjust the instance tier instead.
- 6Step 6 — Configure Resource Governor to cap memory per workload group, preventing ETL from starving BI queries: CREATE RESOURCE POOL etl_pool WITH (MAX_MEMORY_PERCENT = 40); CREATE WORKLOAD GROUP etl_group USING etl_pool; ALTER RESOURCE GOVERNOR RECONFIGURE; — route ADF login by application name using a classifier function: CREATE FUNCTION dbo.rg_classifier() RETURNS SYSNAME WITH SCHEMABINDING AS BEGIN RETURN CASE APP_NAME() WHEN 'ADF' THEN 'etl_group' ELSE 'default' END; END; ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier); ALTER RESOURCE GOVERNOR RECONFIGURE;
- 7Step 7 — For ADF copy activities, reduce parallelism: in the ADF Copy Activity → Settings tab, set 'Degree of copy parallelism' to 2–4 instead of Auto. For large tables use partitioned copy with explicit partition column ranges so each sub-query processes a smaller slice and requests a smaller grant.
Example log output
SqlException (0x80131904): A time-out occurred while waiting for memory resources to execute the query in resource pool 'default' (2). Rerun the query. (Microsoft SQL Server, Error: 8645)Activity failed: {'errorCode': '2200', 'message': 'ErrorCode=UserErrorSqlQueryFailed, Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException, Message=SQL Query failed to execute., Source=Microsoft.DataTransfer.ClientLibrary, ''Type=System.Data.SqlClient.SqlException, Message=A time-out occurred while waiting for memory resources to execute the query in resource pool \'default\' (2). Rerun the query.'}