SQL Server 2005 索引碎片
我继承的 SQL Server 2005 数据库中的几个键具有非常高的碎片百分比。当使用以下 SQL 时：
select OBJECT_NAME(object_id), avg_fragmentation_in_percent, record_count, *
from sys.dm_db_index_physical_stats (DB_ID(N'FragmentedDB'), NULL, NULL, NULL, 'Detailed') s
我看到几个表的碎片百分比在 50 到 99 之间。这些表都有超过 100,000 行，有些甚至超过 2,000,000 行。我相信这会给我们的应用程序带来严重的性能问题，因此我尝试使用以下sql重建其中一些索引：
ALTER INDEX ALL ON [dbo].[FragmentedTable]
REBUILD WITH ( FILLFACTOR = 90, ONLINE = ON )
但是，在我重建索引并再次查看碎片％之后，它们都没有变化。我有什么遗漏的吗？我已经对此主题进行了一些搜索，但到目前为止一无所获。
谢谢！
Several keys in a SQL Server 2005 database I inherited have very high fragmentation percentages. When using the following SQL:
select OBJECT_NAME(object_id), avg_fragmentation_in_percent, record_count, *
from sys.dm_db_index_physical_stats (DB_ID(N'FragmentedDB'), NULL, NULL, NULL, 'Detailed') s
I see that several tables with fragmentation % between 50 and 99. These tables all have over 100,000 rows, some with 2,000,000+. I believe this is causing significant performance problems for our application, so I have tried to rebuild some of these indexes with the following sql:
ALTER INDEX ALL ON [dbo].[FragmentedTable]
REBUILD WITH ( FILLFACTOR = 90, ONLINE = ON )
However, after I rebuild the index and look at the fragmentation % again, they are all unchanged. Is there something I am missing? I have done some searches on this topic but have come up empty so far.
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您正在使用 dm_db_index_physical_stats 的“详细”。这将显示索引的非叶级别以及叶级别。
碎片是针对叶级别 (leaf_level = 0)、针对非叶级别 (leaf_level > 0) 还是两者兼而有之？
如果碎片处于非叶级别，则问题较小，或者没有问题。
如果您仍然想消除所有碎片，请尝试添加 PAD_INDEX。
You are using 'Detailed' with dm_db_index_physical_stats. This will show the non-leaf levels as well as the leaf levels of the indexes.
Is the fragmentation for the leaf levels (leaf_level = 0), for non-leaf levels (leaf_level > 0), or both?
If the fragmentation is at the non-leaf level, this is less of a problem, or no problem.
If you still want to get rid of all of the fragmentation, try adding PAD_INDEX.
我想到了一些事情 - 首先是如果您使用多个文件来支持表中的索引，接下来是您在重建索引时看到的并行性。接下来，您提到您相信这会导致性能问题，您是否已验证情况确实如此？即，除了一些例外，相对于搜索而言，碎片通常是扫描的一个更大问题。有关碎片、如何解决、集中在何处以及不同修复方法所见差异的完整详细回顾，请参阅 本系列博客文章。
Couple of things come to mind - first would be if you are using multiple files to back the indexes in the table, next would be the parallelism you are seeing while rebuilding the index. Next, you mention you believe this is causing performance problems, have you verified this is the case? i.e. with some exceptions, fragmentation is typically a bigger problem for scans vs. seeks. For a complete detailed review of fragmentation, how to address, where to concentrate, and differences you see with different methods of repairing, see this series of blog posts.
想法..
REBUILD PARTITION = partition_numberLOB_COMPACTION = ON？Thoughts..
REBUILD PARTITION = partition_numberLOB_COMPACTION = ON?有两种消除碎片的方法。这两种方法是必要的，因为它们具有截然不同的特征。
描述 SQL Server 2005 及以上版本中三种方法之间的差异：
ALTER INDEX ... REORGANIZE（新的 DBCC INDEXDEFRAG）
ALTER INDEX ... REBUILD（新的 DBCC DBREINDEX）
ALTER INDEX ... REBUILD
了解更多信息
http://sqlnetcode.blogspot.com/2011/11 /sql-server-methods-for-removing.html
There have been two methods for removing fragmentation. The two methods are necessary because they have quite different characteristics.
Describes the differences between the three methods in SQL Server 2005 onward:
ALTER INDEX … REORGANIZE (the new DBCC INDEXDEFRAG)
ALTER INDEX … REBUILD (the new DBCC DBREINDEX)
ALTER INDEX … REBUILD
For More Info
http://sqlnetcode.blogspot.com/2011/11/sql-server-methods-for-removing.html