Why index REBUILD does not reduce index fragmentatation?

If an index is very small (I believe less than 8 pages) it will use mixed extents. Therefore, it'll appear as if there is still fragmentation remaining, as the housing extent will contain pages from multiple indexes.

Because of this, and also the fact that in such a small index that fragmentation is typically negligable, you really should only be rebuilding indexes with a certain page threshold. It is best practices to rebuild fragmented indexes that are a minimum of 1000 pages.

This can also happen with very LARGE indexes.

I had some indexes on a table with around 700m rows that I couldn't defragment below around 30%. The issue was not enough contiguous free space inside the database to arrange the index consecutively.

To work around a very large index that won't defragment, the BEST solution is to pre-size a new database and move all your objects to that DB, then recreate your indexes there.