Troubleshooting Guide: SQL Server Error 9836 – APPROX_PERCENTILE Function Failure

Why This Error Happens

Error 9836 occurs when the SQL Server query processor encounters an internal fault while computing the APPROX_PERCENTILE_CONT or APPROX_PERCENTILE_DISC aggregate, typically due to corrupted intermediate statistics structures used for the approximation algorithm. This can also result from insufficient tempdb space, as the function relies on temporary data structures to build percentile estimates. Additionally, outdated Query Optimizer hotfixes or a corrupted plan cache entry referencing this newer SQL Server 2025 function can trigger the fault.

Step-by-Step Resolution Guide

Step 1: Clear the Plan Cache and Retry the Query

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run DBCC FREEPROCCACHE; to remove potentially corrupted cached execution plans.
• Re-execute the query containing APPROX_PERCENTILE_CONT or APPROX_PERCENTILE_DISC to see if the error persists.

Step 2: Verify and Expand tempdb Capacity

• Navigate to SSMS > Databases > System Databases > tempdb > Properties.
• Confirm tempdb files have adequate free space and autogrowth is not restricted (recommended: set autogrowth in fixed MB increments, not percentage).
• Run the following to check current tempdb file usage:

  EXEC sp_spaceused;

• Increase initial tempdb file size if disk space allows, under File Properties.

Step 3: Apply the Latest Cumulative Update (CU)

• Check your current build version with:

  SELECT @@VERSION;

• Download and install the latest Cumulative Update for SQL Server 2025 from the official Microsoft Download Center.
• Restart the SQL Server service via Services.msc or net stop MSSQLSERVER followed by net start MSSQLSERVER.

Step 4: Rebuild Statistics on Affected Tables

• Identify the table(s) referenced in the failing query.
• Run the following to refresh statistics used by the approximation engine:

  UPDATE STATISTICS [YourTableName] WITH FULLSCAN;

• Re-run the original query to confirm the error no longer triggers.

Step 5: Enable Trace Flag for Legacy Cardinality Estimation (If Issue Persists)

• As a temporary workaround, enable Trace Flag 9481 to revert to the legacy cardinality estimator for the session:

  DBCC TRACEON(9481, -1);

• Test the query again to confirm whether the newer estimator logic is causing the conflict.

Alternative or Verification Steps

• To verify the fix, run the original APPROX_PERCENTILE query three consecutive times under load to ensure the error does not intermittently reappear.
• As an alternative, replace APPROX_PERCENTILE_CONT/DISC with the standard PERCENTILE_CONT/DISC function temporarily to confirm whether the issue is isolated specifically to the approximation algorithm versus a broader query processor fault.