SQL2005重建索引不起作用 [英] SQL2005 Rebuild Index Not Working
问题描述
重建索引后，它仍会显示相同数量的
碎片。任何想法有什么问题？
我正在使用以下查询确定要重建的索引：
SELECT
OBJECT_NAME（i.object_id）AS TableName，
i.name AS IndexName，
ips.avg_fragmentation_in_percent
FROM sys。 dm_db_index_physical_stats（DB_ID（），NULL，NULL，NULL，
''详情''）ips
JOIN sys.indexes i ON
i .object_id = ips.object_id
AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent> 10
（我知道10％是不够的，需要完全重建，只是
想看到我的碎片）
然后我重建w /：
ALTER INDEX IX_CustomerName ON客户重建
当我重新运行第一个查询时在重建之前显示碎片量
。我会感激任何帮助。
After rebuilding an index, it still shows as the same amount of
fragmentation. ANy ideas what''s wrong?
I''m determining which indexes to rebuild using the following query:
SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,
''DETAILED'') ips
JOIN sys.indexes i ON
i.object_id = ips.object_id
AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 10
(I know 10% is not enough where a full rebuild is called for, just
wanted to see my fragmentation)
Then I rebuild w/:
ALTER INDEX IX_CustomerName ON Customers REBUILD
When I rerun the 1st query the same amount of fragmentation is shown
as before the rebuild. I''d appreciate any help.
推荐答案
您是否检查过为这些索引定义的fillfactor？
（使用sys.indexes获取此信息）
wa **** ****@yahoo.com 写道：
Have you checked the fillfactor defined for these indexes?
(use sys.indexes to get this information)
wa********@yahoo.com wrote:
重建索引后，它仍显示为相同数量的碎片。什么是错的？
我正在使用以下查询确定要重建的索引：
OBJECT_NAME（i.object_id）AS TableName，
i.name AS IndexName，
ips.avg_fragmentation_in_percent
来自sys.dm_db_index_physical_stats（DB_ID（），NULL，NULL，NULL，
''详细''）ips
JOIN sys.indexes i ON
i.object_id = ips.object_id
AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent> 10
（我知道10％是不够的，需要完全重建，只是想看到我的碎片）
然后我重建w /：
ALTER INDEX IX_CustomerName ON客户重建
当我重新运行第一个查询时，会显示与重建之前相同数量的碎片。我会感激任何帮助。
After rebuilding an index, it still shows as the same amount of
fragmentation. ANy ideas what''s wrong?
I''m determining which indexes to rebuild using the following query:
SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,
''DETAILED'') ips
JOIN sys.indexes i ON
i.object_id = ips.object_id
AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 10
(I know 10% is not enough where a full rebuild is called for, just
wanted to see my fragmentation)
Then I rebuild w/:
ALTER INDEX IX_CustomerName ON Customers REBUILD
When I rerun the 1st query the same amount of fragmentation is shown
as before the rebuild. I''d appreciate any help.
我的所有索引上的Fill_factor都是0，但我必须承认我不能
知道填充因子的真正含义。
The Fill_factor is 0 on all my indexes, however I must admit I don''t
know what fill factor really means.
填充因子 - 请在网上书籍中查看。
-------
Fill Factor - please look this up in Books online.
-------
来自BOL：
填充因子选项用于微调索引数据存储
和性能。当创建或重建索引时，填充因子
值确定每个叶级页面上的空间百分比为
填充数据，因此保留一定百分比的免费空间为
未来增长。例如，指定填充因子值80表示
每个叶级页面的20％将保留为空，为数据添加到索引扩展时提供
空间基础表。
填充因子值是从1到100的百分比。服务器范围的
默认值为0是大多数人的最佳选择情况。当
填充因子设置为0时，叶级别几乎被填充到容量，
但是至少有一个额外的索引行仍留有一些空间。使用这个
设置，叶级空间可以有效地使用，但是在页面必须拆分之前，仍有空间用于限制扩展。
注：
填充因子值0和100在所有方面都相同。
-------
不确定为什么碎片仍然显示相同。在
重建之后，您是否可以再次运行以下内容和第一个SELECT查询
查看碎片是否已更改？ （这是一个非常资源
密集型操作）。
UPDATE STATISTICS客户与FULLSCAN
wa ******** @ yahoo.com 写道：Fill_factor在我的所有索引上都是0，但是我必须承认我不喜欢'
知道填充因子的真正含义。
From BOL: The fill factor option is provided for fine-tuning index data storage
and performance. When an index is created or rebuilt, the fill factor
value determines the percentage of space on each leaf level page to be
filled with data, therefore reserving a percentage of free space for
future growth. For example, specifying a fill factor value of 80 means
that 20 percent of each leaf-level page will be left empty providing
space for index expansion as data is added to the underlying table.
The fill factor value is a percentage from 1 to 100. The server-wide
default of 0 is the optimal choice in the majority of situations. When
fill factor is set to 0, the leaf level is filled almost to capacity,
but some space remains for at least one additional index row. With this
setting, the leaf level space is used efficiently, but room remains for
limited expansion before the page must be split.
Note:
Fill factor values 0 and 100 are the same in all respects.
-------
Not sure why the fragmentation still shows the same. After the
rebuild, can you run the following and the the first SELECT query again
see if the fragmentation has changed? (This is a very resource
intensive operation).
UPDATE STATISTICS Customers WITH FULLSCAN
wa********@yahoo.com wrote: The Fill_factor is 0 on all my indexes, however I must admit I don''t
know what fill factor really means.
这篇关于SQL2005重建索引不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
