在这种情况下如何避免sql server页面碎片？
我想对表进行 SQL 插入操作，以通过尽可能避免碎片来优化页面使用。 我将运行一个 .net Windows 服务，该服务每 2 小时就会从数据库中获取一些数据并对其进行优化 以便将来查询。涉及 varchar(6000) 列，但我估计它很少会超过 4000 字节。 事实上，该列通常可以在 600 到 2400 之间变化。 6000有助于避免截断错误。我仍然可以通过 .net 控制该列的大小。 永远不会有更新或删除。只需选择（并每 2 小时插入一次）。 每 2 小时大约会有 1000 个插入。
我使用的是 Sql Server 2005。据说页面大小为 8096 字节。 我需要在表中插入行。考虑到行的大小，一页可以容纳 4 到 12 行。
因此，我将从 .net 中从数据库读取数据，将其存储在内存中（也许使用某种聚类算法？），然后插入大约 1000 行。
我想知道是否有办法避免或最大限度地减少这种情况下的页面碎片。
I want to order SQL Inserts into a table to optimize page use by avoiding fragmentation as much as possible.
I will be running a .net Windows Service, which every 2 hours will take some data from a database and optimize it
for future queries. A varchar(6000) column is involved, though I estimate it will rarely go beyond 4000 bytes.
In fact, this column can vary normally between 600 and 2400.
It's 6000 to help avoiding truncating errors. Still I can control that column size through .net.
There won't ever be updates nor delete. Just selects (and inserts every 2 hours).
There will be around 1000 inserts every 2 hours.
I'm using Sql Server 2005. Page size are said to be 8096 bytes.
I need to insert rows in a table. Given the size of rows, between 4 and 12 rows could fit in a page.
So from .net I will read data from database, store it in memory, (use some clustering algorithm maybe?), and the insert around 1000 rows.
I was wondering if there is a way to avoid or minimize page fragmentation in this scenario.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
表是b树还是堆？你有聚集索引吗？如果是，那么聚集索引位于哪一列上，以及插入时如何计算列值？
为什么你首先关心碎片化？空间考虑还是预读性能？对于空间，您应该跳过 SQL 2005 并转到 SQL 2008 进行页面压缩。对于预读，值得研究一下为什么需要大量预读。
总体而言，索引碎片更像是一种大家都在谈论但很少有人真正理解的过分的说法。在碎片化成为真正的瓶颈之前，还有更多的途径可以追求。
Is the table a btree or a heap? Do you have a clustered index on it? If yes, then what column is the clustered index on, and how is the column value computed at insert?
Why do you care about fragmentation to start with? Space consideration or read ahead performance? For space, you should skip SQL 2005 and go to SQL 2008 for Page compression. For read ahead, it would be worth investigating why you need large read aheads to start with.
Overall, index fragmentation is more of an overhiped bru-ha-ha everyone talks about but very few really understand. There are many many more aveanues to pursue before fragmentation becomes the real bottleneck.