对 SQL 索引进行碎片整理
我一直在尝试对 SQL Server 2005 中的索引进行碎片整理，但似乎没有任何效果。我使用向导创建了多个维护计划，但作业总是失败。我已经从这个站点运行了脚本，该脚本最初来自 Microsoft：
即使我去在对象资源管理器中的特定表并选择索引文件夹并选择重建所有碎片％永远不会改变，即使它报告为成功完成。
重建索引不应该有 0% 碎片吗？如果是这样，为什么这个 sql 不起作用：
ALTER INDEX [IndexName] ON [dbo].[TableName]
REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = Off,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
这是由选定的重建索引生成的 sql。
I have been trying to defrag indexes in SQL Server 2005 and nothing seems to work. I have created multiple Maintenance Plans using the wizard but the job always fails. I have run the script from this site, which is originally from Microsoft:
Even if I go to the specific table in Object Explorer and select the Indexes folder and select Rebuild All the fragmentation % never changes, even though it reports as completing as successful.
Shouldn't a rebuilt index have 0% fragmentation? If so why would this sql not work:
ALTER INDEX [IndexName] ON [dbo].[TableName]
REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = Off,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
This is the sql generated by selected Rebuild Index.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果表中的行数不多，或者数据不消耗一页数据（8k），即使重建后，您也会注意到索引碎片。
If there are not a lot of rows in the table, or the data does not consume a page of data (8k), you will notice fragmentation of indexes even after rebuild.
尽管可能与其他响应中建议的非常小的数据库有关，但该问题更可能与 FILLFACTOR 相关。
显示的 ALTER INDEX 语句没有明确提及 FILLFACTOR，因此重建是根据当前填充因子值完成的，留下的碎片应接近该因子。 （它很少是精确匹配，因为给定的索引条目不能在两个节点之间分割，因此使每个节点可能留下比填充因子所需的更多或更少的空间；实际上在某些情况下，这将需要一小部分字节数。但我们不要偏离真正的问题...）
您可以通过查看 sys.indexes 表来查询给定索引的当前填充因子值，查询类似于
从 sys.indexes 中选择 *
哪里 object_id IN
（从 sys.objects 中选择 object_id
WHERE name = 'myTableName')
或者，如果您运行显示的 ALTER INDEX 片段的修改版本，其中
... 填充因子 = 100 ...
添加在“WITH”选项中，我怀疑报告的碎片将符合您的期望。
为了清楚起见，PAD_INDEX选项仅指示SQL在索引的中间节点中留出一些空间，因此这些不会产生任何“碎片”，但叶节点会。
这就是说......保留一定量的填充因子可能是一个好主意，以便在新数据插入表时延迟碎片的出现。
Although possibly related with a very small database as suggested in other response, the problem is more likely tied to the FILLFACTOR.
The ALTER INDEX statement shown does not explicitly mention the FILLFACTOR, and hence the rebuilt is done on the basis of the current fillfactor value, leaving a fragmentation that should approximate this factor. (It is rarely an exact match because a given index entry cannot be split between two nodes, hence making each node possibly leave more or less room than the fillfactor would require; indeed in some cases, this would require an fractional number of bytes... but let's not stray from the real issue...)
You can inquire the current fillfactor value for a given index by looking in the sys.indexes table, with a query similar to
SELECT * FROM sys.indexes
WHERE object_id IN
( SELECT object_id FROM sys.objects
WHERE name = 'myTableName')
Alternatively if you run a modified version of the ALTER INDEX snipped shown, where
... FILLFACTOR = 100 ...
is added in the "WITH" option, I suspect the fragmentation reported will match your expectation.
For sake of clarity, the PAD_INDEX option only instructs SQL to leave some space in the intermediate nodes of the index, these therefore will not incur any "fragmentation", but the leaf nodes will.
This said... It may be a good idea to leave a certain amount of fillfactor, in order to delay in onset of fragmentation as new data is inserted into the table.