熱點推薦:
您现在的位置: 電腦知識網 >> 操作系統 >> Windows系統管理 >> 正文

tempdb 和索引創建

2013-11-11 21:37:32  來源: Windows系統管理 

  創建索引時可以指定 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則目的文件組中必須有足夠的可用空間存儲最終表包括所有索引結構如果有更多的可用空間則可以提高表和索引擴展盤區的連續性

From:http://tw.wingwit.com/Article/os/xtgl/201311/8838.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.