创建索引时可以指定 WITH SORT_IN_TEMPDB 选项该选项指示数据引擎使用 tempdb 存储用于生成索引的中间排序结果虽然该选项会增加用于创建索引的磁盘空间量但是当 tempdb 与用户数据库位于不同的磁盘集上时该选项可减少创建索引所需的时间
数据库引擎在生成索引时经历下面两个阶段
首先数据库引擎扫描数据页以检索键值并为每个数据行生成索引叶行当内部排序缓沖区被叶索引项填满时这些项被排序并作为中间排序进程写入磁盘然后数据库引擎继续扫描数据页直到排序缓沖区再次被填满这种先扫描多个数据页然后排序并写入排序进程的模式继续进行直到处理完基表中的所有行在聚集索引中索引的叶行是表的数据行因此中间排序进程包含所有的数据行在非聚集索引中叶行不包含非键列的值因此通常较小然而如果索引键很大则非聚集的排序进程也可能很大
数据库引擎将排序的索引叶行进程合并为单个的排序流引擎的排序合并组件从每个排序进程的第一页开始在所有的页中找出最小的键并将那个叶行传递到索引创建组件然后处理下一个最小的键随后再处理下一个依此类推当将最后一个叶索引行从排序进程页中析取出来时该进程从此排序进程切换到下一页当处理完某个排序进程扩展盘区中的所有页时释放该扩展盘区每个叶索引行在传递到索引创建组件时均被放置在缓沖区的叶索引页中每个叶页在填充时被写入当写入叶页时数据库引擎还会生成该索引的上级每个上级索引页在填充时被写入
如果在已经有非聚集索引的表上创建聚集索引一般过程是
重新分配非聚集索引但保留索引的定义空间直到包含 CREATE INDEX 语句的事务结束时才可用因而如果在事务回滚期间必须恢复旧索引页则这些旧索引页仍是可用的
创建聚集索引
重新创建非聚集索引
如果未指定 SORT_IN_TEMPDB则排序进程将存储在目的文件组中在创建索引的第一阶段基表页的交替读取和排序进程的写入使读写磁头从磁盘的一个区域移动到另一个区域当扫描数据页时磁头位于数据页区域当填充排序缓沖区并且当前的排序进程必须写入磁盘时读写磁头移动到某个可用空间区域然后当继续扫描表页时移回数据页区域在第二阶段读写磁头的移动频率较高这时排序进程一般正在从各排序进程区域交替读取目的文件组中将生成排序进程和新的索引页这意味着数据库引擎在各排序进程间分布读取的同时还必须定期跳到索引扩展盘区以便在填充索引页时写入新的索引页
如果指定了 SORT_IN_TEMPDB 选项并且 tempdb 与目的文件组位于不同的磁盘集上那么在第一阶段对数据页的读取与对 tempdb 中排序工作区的写入会发生在不同的磁盘上这意味着对数据键的磁盘读取在整个磁盘上会趋于更加连续并且对 tempdb 磁盘的写入也趋于连续正如生成最终索引时的写入操作一样即使其他用户正在使用数据库并且正在访问不同的磁盘地址指定 SORT_IN_TEMPDB 选项时的总体读写模式的效率也比没有指定时要高
SORT_IN_TEMPDB 选项可能会提高索引扩展盘区的邻接尤其当不是并行处理 CREATE INDEX 时排序工作区扩展盘区在数据库中的释放位置方面有些随机如果排序工作区包含在目的文件组中则释放排序工作区扩展盘区时可通过请求来获取它们以使扩展盘区在生成时容纳索引结构这在某种程度上使索引扩展盘区的位置随机化如果在 tempdb 中单独容纳排序扩展盘区则它们的释放顺序与索引扩展盘区的位置无关另外当中间排序进程存储在 tempdb 中而不是目的文件组中时目的文件组中将有更多的可用空间从而增加了索引扩展盘区邻接的机会
SORT_IN_TEMPDB 选项只影响当前的语句没有任何元数据记录索引是否存储在 tempdb 中例如如果使用 SORT_IN_TEMPDB 选项创建了某个非聚集索引后来在没有指定该选项的情况下创建了某个聚集索引则当数据库引擎重新创建那个非聚集索引时将不使用该选项
可用空间要求
如果指定了 SORT_IN_TEMPDB 选项则 tempdb 中必须有足够的可用空间容纳中间排序进程而且目的文件组中必须有足够的可用空间容纳新的索引如果没有足够的可用空间并且由于某种原因数据库不能自动增长以获得更多的空间（如磁盘上没有剩余空间或关闭了自动增长功能）CREATE INDEX 语句将失败
如果没有指定 SORT_IN_TEMPDB目的文件组中的可用空间必须大约等于最终索引的大小在第一阶段生成排序进程并要求可用空间量大约等于最终索引的大小在第二阶段处理每个排序进程扩展盘区后将其释放这意味着释放排序进程扩展盘区的速度与获取扩展盘区以容纳最终索引页的速度差不多相同因此总的空间要求并没有显着超过最终索引的大小这样的一个副作用就是如果可用空间量非常接近最终索引的大小则数据库引擎倾向于在排序进程扩展盘区释放后立即重新使用它们因为排序进程扩展盘区的释放方式有些随机所以在这种情形中将降低索引扩展盘区的连续性如果没有指定 SORT_IN_TEMPDB那么如果目的文件组中有足够的可用空间则可以从邻接的池而不是从刚刚重新分配的排序进程扩展盘区分配索引扩展盘区这将提高索引扩展盘区的连续性
执行 CREATE INDEX 语句时必须有足够的可用空间
创建非聚集索引时
如果指定了 SORT_IN_TEMPDB则 tempdb 中必须有足够的可用空间存储排序进程而且目的文件组中必须有足够的可用空间存储最终索引结构排序进程包含索引的叶行
如果没有指定 SORT_IN_TEMPDB目的文件组中必须有足够的可用空间存储最终索引结构如果有更多的可用空间则可以提高索引扩展盘区的连续性
在没有非聚集索引的表上创建聚集索引时
如果指定了 SORT_IN_TEMPDB则 tempdb 中必须有足够的可用空间存储排序进程包括表的数据行目的文件组中必须有足够的可用空间存储最终的索引结构包括表的数据行和索引 B 树粗略的估计值是原始表大小的 倍但可能需要根据不同的因素调整估计值如键的大小很大或填充因子的值很低
如果没有指定 SORT_IN_TEMPDB则目的文件组中必须有足够的可用空间存储最终表包括索引结构如果有更多的可用空间则可以提高表和索引扩展盘区的连续性
在有非聚集索引的表上创建聚集索引时
如果指定了 SORT_IN_TEMPDB则 tempdb 中必须有足够的可用空间存储最大索引（一般为聚集索引）的排序进程集合而且目的文件组中必须有足够的空间存储所有索引的最终结构包括表的数据行所在的聚集索引
如果没有指定 SORT_IN_TEMPDB则目的文件组中必须有足够的可用空间存储最终表包括所有索引结构如果有更多的可用空间则可以提高表和索引扩展盘区的连续性
