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 &lt; 1000 -- A loose proxy for system tables
WHERE b.database_id = DB_ID()
GROUP BY p.object_id
ORDER BY pages DESC
Are you sure you have positively and accurately identified this system table as the sole source of "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"? Are you sure this system table isn't self-managed in such a way that (a) fragmentation is minimized or kept in check secretly or just (b) managed efficiently in memory so that defragmentation levels really don't affect anything much?

You can see how many pages are in use, and you can see how much free space is on the pages that are in memory (page_free_space_percent is always NULL in the allocations DMF, but this is available from the buffer DMV) - this should give you some idea if what you're worrying about is really something you should be worrying about:

SELECT 
  Number_of_Pages = COUNT(*), 
  Number_of_Pages_In_Memory = COUNT(b.page_id),
  Avg_Free_Space = AVG(b.free_space_in_bytes/8192.0) 
FROM sys.dm_db_database_page_allocations
(
  DB_ID(),
  OBJECT_ID(N'sys.syscolpars'),
  NULL,NULL,'DETAILED'
) AS p
LEFT OUTER JOIN sys.dm_os_buffer_descriptors AS b
ON b.database_id = DB_ID() 
AND b.page_id = p.allocated_page_page_id 
AND b.file_id = p.allocated_page_file_id;


If your number of pages is small (like probably &lt; 10000 for system tables) or if the free space is "low" (not sure what your typical thresholds are for reorg/rebuild), focus on other, more interesting, low-hanging fruit.

If your number of pages is large and the free space is "high", ok, then maybe I'm giving SQL Server too much credit for its own self-maintenance. As you showed from the other question, this works... 

ALTER INDEX ALL ON sys.syscolpars REORGANIZE;


...and does reduce fragmentation. Though it may require elevated permissions (I did not try as a peon).

Maybe you can just do this periodically as part of your own maintenance, if it makes you feel good and/or you have any evidence that it has any positive impact on your system at all.