How to fix Error 8659 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 8659 — Columnstore Index Build Memory Allocation Failure

Why This Error Happens

This error occurs when SQL Server’s columnstore index build process cannot reserve the required amount of “misc memory” per thread from the memory grant it was allocated, typically because the server’s max server memory setting is too restrictive relative to the degree of parallelism (DOP) being used. Each thread involved in building the columnstore index requires a dedicated memory buffer, and when too many threads are spawned (high MAXDOP) against a constrained memory ceiling, the total requested memory exceeds what SQL Server can safely grant. This is commonly seen on systems with large core counts but limited RAM allocated to the SQL Server instance, or when Resource Governor workload groups impose tight memory limits.

Step-by-Step Resolution Guide

Step 1: Reduce the Degree of Parallelism for the Index Build

  • Explicitly specify a lower MAXDOP value in the CREATE COLUMNSTORE INDEX statement, e.g., WITH (MAXDOP = 4).
  • This reduces the number of threads requesting misc memory simultaneously, lowering total memory pressure.
  • Test incrementally (MAXDOP 8 → 4 → 2) until the build succeeds.

Step 2: Review and Adjust Max Server Memory Configuration

  • Open SQL Server Management Studio (SSMS) and connect to the instance.
  • Run EXEC sp_configure 'max server memory (MB)'; to check the current ceiling.
  • Compare this against physical RAM on Windows Server 2025/Windows 11 (check via Task Manager > Performance > Memory).
  • If the value is too low relative to available physical memory, increase it using:
  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'max server memory (MB)', 16384;
  RECONFIGURE;

Step 3: Check Resource Governor Settings

  • Run SELECT * FROM sys.resource_governor_workload_groups; to identify if the session is bound to a restrictive workload group.
  • If a custom workload group has a low REQUEST_MAX_MEMORY_GRANT_PERCENT, increase it using:
  ALTER WORKLOAD GROUP [YourGroupName]
  WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 50);
  ALTER RESOURCE GOVERNOR RECONFIGURE;
  • Alternatively, disable Resource Governor temporarily for testing: ALTER RESOURCE GOVERNOR DISABLE;

Step 4: Rebuild the Index in Smaller Batches or Offline

  • If the table is extremely large, consider building the columnstore index using WITH (ONLINE = OFF) to reduce concurrent memory overhead.
  • Alternatively, partition the table and build columnstore indexes per-partition to reduce the per-operation memory footprint.

Alternative or Verification Steps

  • After applying changes, re-run the CREATE COLUMNSTORE INDEX statement and monitor the SQL Server Error Log and Windows Event Viewer (Applications and Services Logs > MSSQLSERVER) to confirm Error 8659 no longer appears.
  • If the issue persists, use Performance Monitor (perfmon) to track the SQLServer:Memory Manager > Memory Grants Pending counter during the index build to confirm whether memory grant contention is still the bottleneck, and consider temporarily increasing physical RAM or moving the workload to a server with more available memory.