As tabelas de sistema do SQL Server podem ser desfragmentadas?

Temos vários bancos de dados nos quais um grande número de tabelas é criado e descartado. Pelo que podemos dizer, o SQL Server não realiza nenhuma manutenção interna nas tabelas base do sistema , o que significa que elas podem se tornar muito fragmentadas com o tempo e aumentar de tamanho. Isso coloca pressão desnecessária no pool de buffers e também afeta negativamente o desempenho de operações, como calcular o tamanho de todas as tabelas em um banco de dados.

Alguém tem sugestões para minimizar a fragmentação nessas tabelas internas principais? Uma solução óbvia seria evitar a criação de tantas tabelas (ou criar todas as tabelas transitórias no tempdb), mas para o propósito desta questão, digamos que o aplicativo não tenha essa flexibilidade.

Editar: pesquisas adicionais mostram esta pergunta sem resposta , que parece intimamente relacionada e indica que alguma forma de manutenção manual ALTER INDEX...REORGANIZEpode ser uma opção.

Pesquisa inicial

Os metadados sobre essas tabelas podem ser visualizados em 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

No entanto, sys.dm_db_index_physical_statsnão parece oferecer suporte à visualização da fragmentação dessas tabelas:

-- 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'
)

Os scripts de Ola Hallengren também contêm um parâmetro para considerar a desfragmentação de is_ms_shipped = 1objetos, mas o procedimento ignora silenciosamente as tabelas básicas do sistema mesmo com esse parâmetro ativado. Ola esclareceu que esse é o comportamento esperado; apenas tabelas de usuário (não tabelas de sistema) que são ms_shipped (por exemplo msdb.dbo.backupset, ) são consideradas.

-- 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);

Informações adicionais solicitadas

Eu usei uma adaptação da consulta de Aaron abaixo do uso do buffer pool da tabela de sistema de inspeção, e descobri que existem dezenas de GB de tabelas do sistema no buffer pool para apenas um banco de dados, com ~ 80% desse espaço sendo espaço livre em alguns casos .

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