主页 / dba / 问题 / 132293
Accepted
Geoff Patterson
Asked: 2016-03-16 07:28:55 +0800 CST

可以对 SQL Server 系统表进行碎片整理吗？

  • 772

我们有几个数据库，其中创建和删除了大量表。据我们所知，SQL Server 不会对系统基表进行任何内部维护，这意味着它们会随着时间的推移变得非常碎片化并且体积膨胀。这会给缓冲池带来不必要的压力，也会对计算数据库中所有表的大小等操作的性能产生负面影响。

有没有人建议尽量减少这些核心内部表上的碎片？一个明显的解决方案可以避免创建如此多的表（或在 tempdb 中创建所有临时表），但出于这个问题的目的，假设应用程序没有这种灵活性。

编辑：进一步的研究表明这个悬而未决的问题，它看起来密切相关，并表明某种形式的手动维护ALTER INDEX...REORGANIZE可能是一种选择。


初步研究

有关这些表的元数据可以在以下位置查看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

但是，sys.dm_db_index_physical_stats似乎不支持查看这些表的碎片：

-- 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 的脚本还包含一个考虑对is_ms_shipped = 1对象进行碎片整理的参数，但即使启用了此参数，该过程也会默默地忽略系统基表。Ola 澄清说这是预期的行为；仅msdb.dbo.backupset考虑 ms_shipped（例如）的用户表（不是系统表）。

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


额外要求的信息

我在检查系统表缓冲池使用情况下使用了 Aaron 查询的改编版本，结果发现缓冲池中有数十 GB 的系统表仅用于一个数据库，在某些情况下，大约 80% 的空间是可用空间.

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

在此处输入图像描述

sql-server sql-server-2014

2 个回答

  1. Best Answer

    您确定您已积极且准确地将这个系统表识别为“对缓冲池造成不必要的压力，并且还会对计算数据库中所有表的大小等操作的性能产生负面影响”的唯一来源吗？你确定这个系统表不是以这样的方式自我管理的（a）碎片被最小化或秘密检查或只是（b）在内存中有效管理，以便碎片整理级别真的不会影响任何东西？

    您可以看到有多少页面正在使用，并且您可以看到内存中的页面上有多少可用空间（page_free_space_percent始终NULL在分配 DMF 中，但这可从缓冲区 DMV 中获得） - 这应该会给您一些想法如果您担心的确实是您应该担心的事情：

    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;

    如果您的页面数量很少（例如系统表可能 < 10000）或可用空间“低”（不确定重组/重建的典型阈值是多少），请关注其他更有趣、容易实现的目标.

    如果您的页数很大并且可用空间“高”，那么可能我对 SQL Server 自身的自我维护给予了太多的信任。正如您从另一个问题中展示的那样，这有效......

    ALTER INDEX ALL ON sys.syscolpars REORGANIZE;

    ...并且确实减少了碎片化。虽然它可能需要提升权限（我没有尝试过作为苦工）。

    如果它让您感觉良好和/或您有任何证据表明它对您的系统有任何积极影响，也许您可​​以定期执行此操作作为您自己维护的一部分。

    • 11

  2. 根据 Aaron 的回答以及其他研究的指导，这里是我采用的方法的快速记录。

    据我所知，检查系统基表碎片的选项是有限的。我继续提交了一个 Connect 问题以提供更好的可见性，但与此同时，选项似乎包括检查缓冲池或检查每行的平均字节数。

    然后我创建了一个过程来对所有系统基表执行 `ALTER INDEX...REORGANIZE。在我们最（ab）使用的一些开发服务器上执行此过程表明，系统基表的累积大小最多减少了 50GB（系统上有大约 5MM 用户表，因此显然是一个极端情况）。

    我们的一项夜间维护任务有助于清理由各种单元测试和开发创建的许多用户表，以前需要大约 50 分钟才能完成。sp_whoisactive、sys.dm_os_waiting_tasks和的组合DBCC PAGE表明等待由系统基表上的 I/O 支配。

    重组所有系统基表后，维护任务降至约 15 分钟。仍然有一些 I/O 等待，但它们已显着减少，这可能是由于缓存中剩余的数据量更大和/或由于碎片较少而导致更多的预读。

    因此，我的结论是，将ALTER INDEX...REORGANIZE系统基表添加到维护计划中可能是一件值得考虑的事情，但只有当您遇到在数据库上创建异常数量的对象的情况下才可能这样做。

    • 9

相关问题