执行索引重建/重组后平均碎片率未降，为何部分表仍存高碎片？

Let's break down why your PK_AccessPoint index isn't getting its fragmentation fixed, even though other indexes are working perfectly:

1. SQL Server's Built-in Threshold for Small Indexes #

SQL Server (including Azure SQL) doesn't bother defragmenting indexes with a small number of pages—typically 8 pages or fewer. Your index only has 3 pages, so the maintenance operations (rebuild/reorganize) will skip it by default. The reason? The performance gain from fixing fragmentation on tiny indexes is practically zero, while the overhead of rebuilding them isn't worth it.

2. Fragmentation Percentage Looks Worse Than It Is #

With only 3 pages, even one fragmented page translates to a 33% fragmentation rate. But in reality, this tiny amount of fragmentation won't impact your query performance at all. The percentage is misleading here because it's calculated against such a small total page count.

3. Your Maintenance Sproc Likely Filters Out Small Indexes #

Most index maintenance scripts include logic to skip indexes with low page counts to save resources. If you check the sproc's code, you'll probably find a condition like WHERE page_count > 8 that excludes your 3-page index from being processed.

What You Can Do About It #

• Ignore it (recommended): If your queries on this table are running fine, there's no need to fix this fragmentation. It's harmless.
• Force a manual rebuild: If you really want to eliminate the fragmentation, run this command (replace YourTableName with the actual table name):

  ALTER INDEX PK_AccessPoint ON YourTableName REBUILD;
  

  Keep in mind that future data modifications might reintroduce small amounts of fragmentation, but again, this won't hurt performance.
• Adjust the sproc (optional): If you want the sproc to handle small indexes, modify its code to remove or lower the page count threshold. But this isn't recommended unless you have a specific reason to do so.

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