Sql-server – Can SQL Server system tables be defragmented

We have several databases in which a large number of tables are created and dropped. From what we can tell, SQL Server does not conduct any internal maintenance on the system base tables, meaning that they can become very fragmented over time and bloated in size. This puts unnecessary pressure on the buffer pool and also negatively impacts the performance of operations such as computing the size of all tables in a database.

Does anyone have suggestions for minimizing fragmentation on these core internal tables? One obvious solution could to avoid creating so many tables (or to create all transient tables in tempdb), but for the purpose of this question let's say that the application does not have that flexibility.

Edit: Further research shows this unanswered question, which looks closely related and indicates that some form of manual maintenance via ALTER INDEX...REORGANIZE may be an option.

Initial research

Metadata about these tables can be viewed in sys.dm_db_partition_stats:

-- The system base table that contains one row for every column in the system
SELECT row_count,
    (reserved_page_count * 8 * 1024.0) / row_count AS bytes_per_row, 
    reserved_page_count/128. AS space_mb
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('sys.syscolpars')
    AND index_id = 1
-- row_count:       15,600,859
-- bytes_per_row:   278.08
-- space_mb:        4,136

However, sys.dm_db_index_physical_stats does not appear to support viewing the fragmentation of these tables:

-- No fragmentation data is returned by sys.dm_db_index_physical_stats
SELECT *
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('sys.syscolpars'),
    NULL,
    NULL,
    'DETAILED'
)

Ola Hallengren's scripts also contain a parameter to consider defragmentation for is_ms_shipped = 1 objects, but the procedure silently ignores system base tables even with this parameter enabled. Ola clarified that this is the expected behavior; only user tables (not system tables) that are ms_shipped (e.g. msdb.dbo.backupset) are considered.

-- Returns code 0 (successful), but does not do any work for system base tables.
-- Instead of the expected commands to update statistics and reorganize indexes,
-- no commands are generated. The script seems to assume the target tables will
-- appear in sys.tables, but this does not appear to be a valid assumption for
-- system tables like sys.sysrowsets or sys.syscolpars.
DECLARE @result int;
EXEC @result = IndexOptimize @Databases = 'Test',
        @FragmentationLow = 'INDEX_REORGANIZE',
        @FragmentationMedium = 'INDEX_REORGANIZE',
        @FragmentationHigh = 'INDEX_REORGANIZE',
        @PageCountLevel = 0,
        @UpdateStatistics = 'ALL',
        @Indexes = '%Test.sys.sysrowsets.%',
        -- Proc works properly if targeting a non-system table instead
        --@Indexes = '%Test.dbo.Numbers.%',
        @MSShippedObjects = 'Y',
        @Execute = 'N';
PRINT(@result);

Additional requested info

I used an adaptation of Aaron's query below the inspect system table buffer pool usage, and this found that there are tens of GB of system tables in the buffer pool for just one database, with ~80% of that space being free space in some cases.

-- Compute buffer pool usage by system table
SELECT OBJECT_NAME(p.object_id),
    COUNT(b.page_id) pages,
    SUM(b.free_space_in_bytes/8192.0) free_pages
FROM sys.dm_os_buffer_descriptors b
JOIN sys.allocation_units a
    ON a.allocation_unit_id = b.allocation_unit_id
JOIN sys.partitions p
    ON p.partition_id = a.container_id
    AND p.object_id < 1000 -- A loose proxy for system tables
WHERE b.database_id = DB_ID()
GROUP BY p.object_id
ORDER BY pages DESC