sql-server – 何时在重建索引时使用sort_in_tempdb？

我们正在讨论是否对DW表使用SORT_IN_TEMPDB选项.我的理解是,使用此选项时有更多写入,尽管它们更顺序.我们有一个SAN(有时候出了名的很慢),所以在我们的例子中我们希望尽可能地限制写入次数.我相信tempdb在一个单独的LUN(磁盘集)上.

我们的数据文件和tempdb文件中有足够的磁盘空间.在这种情况下,我们可以从使用SORT_IN_TEMPDB中受益吗？

令我印象深刻的是这篇关于Answer的评论

When rebuilding an index you would need twice the space of the index +
20% for the sorting. So in general to rebuild every index in your db
you only need 120% of your biggest index in your DB. If you use
SORT_IN_TEMPDB,you only win 20%,you still need an aditional 100% in
your data file. Further more,using sort in tempdb increases your IO
load drastically,since instead of Writing the index one time to the
datafile,you now write it one time to the tempdb and then write it to
the data file. So that is not always ideal.

我们绝对不希望通过缓慢/可能配置错误的SAN来增加IO负载.

测试这个的最佳方法是什么？通过简单地使用和不使用选项重建表并记录时间？

编辑：我们有8个tempdb文件,每个15GB.我们确实设置了TF 1117/1118标志并启用了IFI.我们目前使用sort_in_tempdb选项进行重建,没有它.

谢谢！

SQL Server 2012 Enterprise

解决方法

当tempdb位于来自用户数据库的不同磁盘集(LUN)上时,它为您提供了更好的优势.

从SORT_IN_TEMPDB Option – BOL开始：

If the SORT_IN_TEMPDB option is set to ON and tempdb is on a separate set of disks from the destination filegroup,during the first phase,the reads of the data pages occur on a different disk from the writes to the sort work area in tempdb. This means the disk reads of the data keys generally continue more serially across the disk,and the writes to the tempdb disk also are generally serial,as do the writes to build the final index. Even if other users are using the database and accessing separate disk addresses,the overall pattern of reads and writes are more efficient when SORT_IN_TEMPDB is specified than when it is not.

请务必阅读disk space requirements when SORT_IN_TEMPDB is ON.

slow/possibly misconfigured SAN

你知道痛点.为什么不与SAN管理员一起修复它？配置错误或缓慢的SAN将导致所有类型的problems like slowness.

需要注意的一些要点：

>使用MAXDOP=1 when doing an online index rebuild – 否则会导致碎片增加.
> Intelligently reorg/rebuild your indexs基于碎片级别以及页面碎片.
>阅读SQLFool Creating a 60 GB Index的经验
>确保已正确配置tempdb – TF 1117 (GROW ALL FILES IN A FILEGROUP EQUALLY) and 1118 (FULL EXTENTS ONLY)并拥有more tempdb datafiles.

What would be the best way to test this?

是的,当您使用和不使用SORT_IN_TEMPDB重建索引时,必须在analyzing the waitstats之前对其进行测试.测量运行时间以及在PROD中进行测量时,请确保在维护窗口或更少的服务器活动期间执行此操作.也是check your read/write data and log latency.

我不确定你是否拥有Instant file initialization,但在恢复,数据文件自动增长和创建新数据库(仅提及完整性)时,它将受益.

（编辑：汕尾站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!