求助：执行Ola Hallengren索引维护脚本后索引碎片无变化

Let’s break down the most likely reasons your index fragmentation isn’t improving after running the IndexOptimize script, along with actionable checks to diagnose each issue:

1. Verify the script actually processed your target indexes #

Ola’s script logs all maintenance actions to the CommandLog table (created when you installed the core scripts). Start by checking if your indexes were even selected for rebuild/reorganize:

SELECT 
    DatabaseName, 
    SchemaName, 
    ObjectName AS TableName, 
    IndexName, 
    CommandType, 
    StartTime, 
    EndTime, 
    ErrorNumber, 
    ErrorMessage
FROM dbo.CommandLog
WHERE CommandType IN ('INDEX_REBUILD', 'INDEX_REORGANIZE')
ORDER BY StartTime DESC;

• If you don’t see your database/indexes here:
  • Double-check if @Databases = 'USER_DATABASES' includes your database. The script uses the dbo.Databases table to define user databases—verify your database is marked as IsUserDatabase = 1 there.
  • Test with a specific database name instead (e.g., @Databases = 'YourDatabaseName') to rule out database selection issues.

2. Confirm your fragmentation measurement matches the script’s logic #

The script uses sys.dm_db_index_physical_stats with the LIMITED scan mode by default. If you’re using a different tool/scan mode to check fragmentation, results can vary drastically. Use this query to mirror the script’s fragmentation calculation:

SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN 
    sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE 
    ips.avg_fragmentation_in_percent > 5 -- Matches your @FragmentationLevel1
ORDER BY 
    ips.avg_fragmentation_in_percent DESC;

• Note: The script skips indexes on small tables (default threshold: <1000 pages) because rebuilding/reorganizing them provides little benefit, and fragmentation stats for small tables are often unreliable. If you need to process small tables, add @PageCountLevel = 100 (or lower) to your execution command.

3. Check for index types that can’t be rebuilt online (or at all) #

Your command specifies INDEX_REBUILD_ONLINE first, but not all index types support online rebuilds:

• Indexes on tables with LOB columns (e.g., TEXT, NTEXT, IMAGE, VARBINARY(MAX)), XML indexes, spatial indexes, or non-unique non-clustered indexes on system tables don’t support online rebuilds in most SQL Server versions.
• If you’re on SQL Server Standard Edition pre-2016, online rebuilds aren’t supported for clustered indexes either.

If the script fails to rebuild an index online, it should fall back to offline rebuild—check the ErrorMessage column in CommandLog for failures. You can also test rebuilding a problematic index manually to confirm:

-- Test online rebuild
ALTER INDEX [YourIndexName] ON [YourSchema].[YourTable] REBUILD WITH (ONLINE = ON);

-- If that fails, try offline rebuild
ALTER INDEX [YourIndexName] ON [YourSchema].[YourTable] REBUILD;

4. Rule out blocking or script interruptions #

If the script’s execution was blocked or interrupted mid-run, some indexes might not have been processed. Check:

• The EndTime in CommandLog—if it’s NULL, the command didn’t complete.
• SQL Server’s error log for any events that could have terminated the script (e.g., server restarts, resource limits).

5. Ensure you’re checking the right indexes #

Double-check that you’re looking at the correct indexes (e.g., clustered vs non-clustered) and that the fragmentation stats you’re viewing are up-to-date. Refresh your fragmentation query after running the script to avoid stale results.

内容的提问来源于stack exchange，提问作者Peter