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

by

Troubleshooting Guide: Error 9111 – UPDATE STATISTICS ON PARTITIONS Not Supported for Non-Incremental Statistics

Why This Error Happens

This error occurs because the ON PARTITIONS clause in the UPDATE STATISTICS statement is only valid when the target statistics object was created with incremental statistics enabled. SQL Server 2025 enforces this restriction because non-incremental (standard) statistics are computed as a single aggregate over the entire table, so partition-level updates are architecturally meaningless in that context. The error typically surfaces when a script written for an incremental-stats table is reused against a table where STATISTICS_INCREMENTAL = OFF, or where the statistics object was created before incremental stats were enabled on the index/table.

Step-by-Step Resolution Guide

Step 1: Confirm Whether the Table Uses Incremental Statistics

  • Run the following query to check the current setting:
  SELECT s.name AS stats_name, s.is_incremental
  FROM sys.stats s
  JOIN sys.objects o ON s.object_id = o.object_id
  WHERE o.name = 'YourTableName';
  • If is_incremental returns 0, this confirms the statistics object is non-incremental and ON PARTITIONS cannot be used.

Step 2: Enable Incremental Statistics on the Table or Index

  • If partition-level granularity is required, recreate the statistics with incremental support:
  DROP STATISTICS YourTableName.YourStatsName;
  CREATE STATISTICS YourStatsName
  ON YourTableName (YourColumnName)
  WITH INCREMENTAL = ON;
  • Note that the table must be partitioned for INCREMENTAL = ON to take effect; otherwise SQL Server silently ignores it.

Step 3: Modify the UPDATE STATISTICS Command for Non-Incremental Tables

  • If incremental statistics are not desired, remove the ON PARTITIONS clause entirely and update the full statistics object instead:
  UPDATE STATISTICS YourTableName YourStatsName
  WITH FULLSCAN;
  • This forces a full recompute across the entire table rather than targeting individual partitions.

Step 4: Audit and Update Maintenance Scripts

  • Check SQL Agent Jobs, Maintenance Plans, and Ola Hallengren–style scripts for hardcoded ON PARTITIONS (...) syntax.
  • Add a conditional check using sys.stats.is_incremental before executing partition-targeted updates to prevent recurrence across multiple tables.

Alternative or Verification Steps

  • Verify the fix by re-running the original UPDATE STATISTICS ... ON PARTITIONS (...) command; it should now complete without Error 9111 if incremental stats were successfully enabled in Step 2.
  • As an alternative, if partition-level statistics management isn’t critical, simply schedule full UPDATE STATISTICS WITH FULLSCAN jobs during low-traffic maintenance windows instead of pursuing incremental stats configuration.