主页 / dba / 问题 / 130845
Accepted
Ryan
Asked: 2016-03-01 14:21:16 +0800 CST

SQL Server索引，平均碎片和总碎片之间的区别

  • 772

使用 SQL Server，如果我导航到对象资源管理器中的一个表，转到它的索引，转到它的属性，然后查看碎片选项卡，顶部有 2 行对我来说，页面充满度百分比和总碎片百分比。

我也有一个偶尔用于索引信息的查询。修剪掉一些不相关的字段，这是查询：

SELECT t.name, ix.name, avg_fragmentation_in_percent
FROM sys.indexes AS ix
INNER JOIN sys.tables t ON t.object_id = ix.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.dm_db_index_physical_stats  (db_id(), NULL, NULL, NULL, 'DETAILED') ps ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
WHERE  ix.index_id > 0
    AND ix.name IS NOT NULL

当我运行这个查询时，我得到 3 列，前 2 列指向索引本身，对于这个查询，第三列是该索引的碎片百分比。

这 2 个数字通常非常接近，有时相同，但也可能经常不同。我遇到过差异很大的情况，在某些情况下超过 50%，但通常情况下它是 <1% 对 15-30%。查询结果几乎总是较高的百分比。

重建索引将使这两个数字完全或几乎为零（尽管不一定都为零）。所以我现在有 2 个数字用于索引上的碎片。根据sys.dm_db_index_physical_stats和Index Properties(FragmentationPage)的文档，两者都应该是索引的逻辑碎片，因此它们应该始终是相同的数字。对于应该相同的统计数据，我怎么会得到不同的数字？

sql-server fragmentation

1 个回答

  1. Best Answer

    您的查询与 UI 发送的查询（您可以使用服务器端跟踪、扩展事件等进行监视）之间存在很大差异。在你的，最后一个参数sys.dm_db_index_physical_stats是DETAILED：

    ... sys.dm_db_index_physical_stats( ..., 'DETAILED') ...

    鉴于 UI 预算有限，SAMPLED而是发送：

    ... sys.dm_db_index_physical_stats( ..., 'SAMPLED') ...

    DETAILED扫描所有页面，同时SAMPLED只扫描1%。没错，1%。显然，您为更高的准确性付出了代价，但是依靠每 100 页的信息来对索引的状态做出任何判断并不是很有用。SAMPLED如果您的目标只是匹配 UI 的无用性，您总是可以将手动查询更改为。:-)

    （公平地说，这个关于 UI 属性页面的主题应该明确记录用于确定碎片的方法。我从Microsoft 的 Pedro Lopes那里得到确认，他会在即将到来的文档修订中记住它。）

    永远不要使用或信任用户界面。别介意一次查看多个索引是一件很痛苦的事情，甚至弄清楚它在做什么也很麻烦，而且你总是不得不担心它是否还在做你以前认为它做过的事情...偶尔查找一件事而不是运行可验证、可重复的查询是不值得的...

    • 4

相关问题