慢插入，逻辑碎片 [英] slow insert, logical fragmentation
问题描述
我们有一张表具有50％的逻辑碎片。在重建
（默认fillfactor 0）之后，我注意到插入速度要快得多。如果
我的页面密度是100％，我不会得到更多页面拆分吗？我知道我在这里缺少一些基本的东西。有人能让我回来吗？
轨道？
桌面尺寸150万
插入尺寸70K
之前：15分钟
之后：3分钟
索引：
复合群集跨越varchar列
还有一些非聚集索引
We had a table that had logical fragmentation of 50%. After rebuilding
(with default fillfactor 0) I noticed that inserts are much faster. If
my page density is 100% wouldn''t I get more page splits? I know I am
missing something fundamental here. Could someone get me back on
track?
Table Size 1.5 million
Insert Size 70K
Before: 15 minutes
After: 3 minutes
Index:
Compound clustered across varchar columns
There are also a couple non-clustered indexes
推荐答案
插入连续数据时会发生页面拆分如果您的网页已满，则现有数据之间已经存在
。但是，如果你是插入新记录（在新的日期下），那么我看不到任何页面拆分
正在发生。
A page split will occur when you insert contigeous data between already
existing data, if your pages are already full. However if you are
inserting new records (under a new date) then I dont see any page split
happening.
Dave（da******@gmail.com）写道：
Dave (da******@gmail.com) writes:
我们有一个逻辑碎片为50％的表。重建后
（使用默认的fillfactor 0）我注意到插入更快。如果
我的页面密度是100％，我不会得到更多页面拆分吗？我知道我在这里缺少一些基本的东西。有人能让我回到
轨道吗？
We had a table that had logical fragmentation of 50%. After rebuilding
(with default fillfactor 0) I noticed that inserts are much faster. If
my page density is 100% wouldn''t I get more page splits? I know I am
missing something fundamental here. Could someone get me back on
track?
这取决于您的聚集索引在哪里。如果它在一个列上，即
是单调增加的，例如IDENTITY列或
日期时间列，默认值为getdate（），那么将会很少分割，
因为所有新数据都会进入新页面。
但是如果插入的数据在聚集索引上显得更随机，
你将获得更多的页面拆分，你的桌子将再次开始碎片
。
在这种情况下，如果重新索引到更低的填充可能更好因为提前为新行创造空间。
-
Erland Sommarskog，SQL Server MVP ， es****@sommarskog.se
SQL Server 2005联机丛书在
http：// www .microsoft.com / technet / pro ... ads / books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons/books .mspx
It depends on what your clustered index is on. If it is on a column that
is monotonically increasing, for instance an IDENTITY column or a
datetime column with the default of getdate(), then will be few splits,
because all new data goes into new pages.
But if the data inserted appears more random over your clustered index,
you will get more page splits, and your table will start to fragment
again.
In this case, if may be better to reindex to a lower fill factor than
100%, to create space for new rows in advance.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
clustered是电子邮件varchar（100）和guid的复合索引。我
可以调整索引。我只是想知道为什么在对索引进行碎片整理后插入更快？
？这对我来说没有意义。
The clustered is a compound index on email varchar(100) and a guid. I
can tune the indexes. I was just wondering why the inserts were faster
after I defragmented the index? It doesn''t make sense to me.
这篇关于慢插入，逻辑碎片的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
