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

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 < 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.

StackExchange Database Administrators Q#132293, answer score: 11