數(shù)據(jù)庫系統(tǒng)和操作系統(tǒng)一樣，在計算機上安裝成功后，還需要進一步配置和優(yōu)化，從而使其具有更強大的功能并運行在最佳狀態(tài)。如果在設(shè)計階段因為各種因素沒有進行較為合理的配置和計劃，那么就應(yīng)在后期對數(shù)據(jù)庫系統(tǒng)進行優(yōu)化。
在Nficrosoft SQL Server 2000中提供了一些優(yōu)化實用工具，使用這些工具可以有效地提高數(shù)據(jù)庫的使用效率。在一般人的眼里，“優(yōu)化”可能算得上是一種高級技能，但真正做起來也不是很難，您也可以成為這方面的高手。本文從十二個方面來談如何優(yōu)化SQL Server2000，使其獲得較高性能。優(yōu)化數(shù)據(jù)庫可在生產(chǎn)數(shù)據(jù)庫上執(zhí)行，獲得最佳性能收益的三個操作包括：①備份和還原操作；②將數(shù)據(jù)大容量復(fù)制到表中；③執(zhí)行數(shù)據(jù)庫控制臺命令(DBCC)操作。
一般情況下，不需要優(yōu)化這些操作。然而，在性能很關(guān)鍵的情形中，可采用以下一些技巧來優(yōu)化性能。
1．優(yōu)化備份和還原性能
SQL Server 2000提供幾種方法以提高備份及還原操作的速度。
(1)使用多個備份設(shè)備使得可以將備份并行寫入所有設(shè)備。同樣，可以將備份并行從多個設(shè)備還原。備份設(shè)備的速度是備份吞吐量的一個潛在瓶頸，使用多個設(shè)備可以按使用的設(shè)備數(shù)成比例提高吞吐量。
(2)使用數(shù)據(jù)庫備份、差異數(shù)據(jù)庫備份和事務(wù)日志備份的組合，可以將故障恢復(fù)所需的時間減到最少。差異數(shù)據(jù)庫備份可以減少必須應(yīng)用于恢復(fù)數(shù)據(jù)庫操作的事務(wù)日志量。這種方法通常比創(chuàng)建完整數(shù)據(jù)庫備份快。
(3)使用日志記錄和最小日志記錄大容量的復(fù)制操作。
2．優(yōu)化數(shù)據(jù)庫、差異數(shù)據(jù)庫和文件備份性能
創(chuàng)建數(shù)據(jù)庫備份包含兩個步驟：①將數(shù)據(jù)從數(shù)據(jù)庫文件復(fù)制到備份設(shè)備。②將事務(wù)日志中用于將數(shù)據(jù)庫前滾到一致狀態(tài)的那部分復(fù)制到相同的備份設(shè)備。
與創(chuàng)建數(shù)據(jù)庫備份一樣，創(chuàng)建差異數(shù)據(jù)庫備份也包括上面兩步，但只復(fù)制已更改的數(shù)據(jù)(盡管需要讀取所有數(shù)據(jù)庫頁以確定數(shù)據(jù)是否更改)。備份數(shù)據(jù)庫文件只需一步，將數(shù)據(jù)從數(shù)據(jù)庫文件復(fù)制到備份設(shè)備。
用于存儲數(shù)據(jù)庫的數(shù)據(jù)庫文件按磁盤設(shè)備排序，并給每個設(shè)備指派讀取器線程，給每個備份設(shè)備指派寫入器線程。該讀取器線程從數(shù)據(jù)庫文件中讀取數(shù)據(jù)，寫入器線程將數(shù)據(jù)寫入備份設(shè)備。通過在更多的邏輯驅(qū)動器中分布數(shù)據(jù)庫文件可以增加并行讀取操作。同樣，通過使用更多的備份設(shè)備可以增加并行寫操作。
一般情況下，瓶頸是數(shù)據(jù)庫文件或備份設(shè)備。如果讀取吞吐總量比備份設(shè)備吞吐總量大，則瓶頸在備份設(shè)備這一側(cè)。添加更多的備份設(shè)備(如果必要還需添加SCSI控制器)可以提高性能。然而，如果備份吞吐總量比讀取吞吐總量大，則應(yīng)在設(shè)備上添加更多的數(shù)據(jù)庫文件或者在RAID(獨立磁盤冗余陣列)設(shè)備內(nèi)使用更多磁盤，以便增加讀取吞吐量。
3．優(yōu)化事務(wù)日志備份性能
創(chuàng)建事務(wù)日志備份只包含單個步驟，將日志中尚未備份的部分復(fù)制到備份設(shè)備。雖然可能有多個事務(wù)日志文件，但事務(wù)日志在邏輯上是某個線程按順序讀取的一個流。
給每個備份設(shè)備指派讀取器／寫入器線程。通過添加更多的備份設(shè)備可以獲得更高的性能。
瓶頸可能是包含事務(wù)日志文件的磁盤設(shè)備或者是備份設(shè)備，具體取決于它們的相對速度和使用的備份設(shè)備數(shù)。添加更多備份設(shè)備將提高線性比例，直到達到包含事務(wù)日志文件的磁盤設(shè)備最大容量，此后如果不通過使用磁盤條帶化等方法提高包含事務(wù)日志的磁盤設(shè)備的速度，將不可能獲得更多性能收益。
4．優(yōu)化還原性能
還原數(shù)據(jù)庫備份或差異數(shù)據(jù)庫備份包含四個步驟：
1)創(chuàng)建數(shù)據(jù)庫和事務(wù)日志文件，如果二者還不存在。
2)將數(shù)據(jù)從備份設(shè)備復(fù)制到數(shù)據(jù)庫文件。
3)從事務(wù)日志文件復(fù)制事務(wù)日志。
4)前滾事務(wù)日志，然后(如果需要)重新啟動恢復(fù)。
應(yīng)用事務(wù)日志備份包含兩個步驟：
1)將數(shù)據(jù)從備份設(shè)備復(fù)制到事務(wù)日志文件。
2)前滾事務(wù)日志。
還原數(shù)據(jù)庫文件包含兩個步驟：
1)創(chuàng)建任何丟失的數(shù)據(jù)庫文件。
2)將數(shù)據(jù)從備份設(shè)備復(fù)制到數(shù)據(jù)庫文件。
如果數(shù)據(jù)庫和事務(wù)日志文件還不存在，必須先創(chuàng)建它們才能將數(shù)據(jù)還原到其中。創(chuàng)建數(shù)據(jù)庫和事務(wù)日志文件并將文件內(nèi)容初始化為零。使用單獨的工作線程并行創(chuàng)建和初始化文件。按磁盤設(shè)備排序數(shù)據(jù)庫和事務(wù)日志文件，并給每個磁盤設(shè)備指派單獨的工作線程。創(chuàng)建和初始化文件需要很大的吞吐量，因此在可用的邏輯驅(qū)動器中均勻分布文件能產(chǎn)生最佳性能。
通過讀取器／寫入器線程將數(shù)據(jù)和事務(wù)日志從備份設(shè)備復(fù)制到數(shù)據(jù)庫和事務(wù)日志文件，給每個備份設(shè)備指派一個線程。復(fù)制性能受備份設(shè)備傳送數(shù)據(jù)的能力或數(shù)據(jù)庫和事務(wù)日志文件接收數(shù)據(jù)的能力的限制。因此，復(fù)制性能隨添加的備份設(shè)備數(shù)線性地提高，直到達到數(shù)據(jù)庫或事務(wù)日志文件接收數(shù)據(jù)能力的極限。
前滾事務(wù)日志操作的性能已經(jīng)固定，除使用更快的計算機外不能進一步優(yōu)化。
5．優(yōu)化磁帶備份設(shè)備性能
有四個變量影響磁帶備份設(shè)備的性能，并使SQL Server備份及還原性能操作得以在大體上隨添加更多磁帶設(shè)備而提高線性比例。①軟件數(shù)據(jù)塊大小。②共享小型計算機系統(tǒng)接口(SCSI)總線的磁帶設(shè)備數(shù)。③磁帶設(shè)備類型。
軟件數(shù)據(jù)塊大小是由SQL Server為最佳性能計算的，不應(yīng)更改。
許多高速磁帶驅(qū)動器如果對每個所使用的磁帶驅(qū)動器有專用SCSI總線，將運行得更好。本機傳輸速率超過SCSI總線速度的50％的驅(qū)動器必須在專用SCSI總線上。
注意，永遠不要將磁帶驅(qū)動器與磁盤或CD-ROM驅(qū)動器放置在同一個SCSI總線上。對這些設(shè)備的錯誤處理操作互不兼容。
6．優(yōu)化磁盤備份設(shè)備性能
磁盤備份設(shè)備的原始I／O速度影響磁盤備份設(shè)備性能，并使SQL Server備份及還原性能操作得以在大體上隨添加多個磁盤設(shè)備而線性提高。
在對磁盤備份設(shè)備使用RAID(獨立磁盤冗余陣列)時需認真考慮。例如，RAID5的寫入性能低，大致與單個磁盤的速度相同(由于必須維護奇偶信息)。另外，將數(shù)據(jù)追加到文件的原始速度明顯比原始設(shè)備寫入速度慢。
如果將備份設(shè)備高度條帶化，以使對備份設(shè)備的最大寫入速度遠遠超過備份設(shè)備將數(shù)據(jù)追加到文件的速度，則在相同的條帶集上放置幾個邏輯備份設(shè)備會比較合適。換句話說，可以通過在相同的邏輯驅(qū)動器上放置幾個備份媒體家族來提高備份性能。然而，需要采用經(jīng)驗方法確定這對每個環(huán)境是收益還是損失。通常情況下，最好將每個備份設(shè)備放置在單獨的磁盤設(shè)備上。
一般在SCSI總線上只有少數(shù)幾個磁盤可以以最大速度運行，但Ultra-wide和Ultra-2總線可以處理更多磁盤。不過，很可能需要認真配置硬件以獲得最佳性能。
7．數(shù)據(jù)壓縮
如今的磁帶驅(qū)動器有內(nèi)置的硬件數(shù)據(jù)壓縮，可顯著提高將數(shù)據(jù)傳送到驅(qū)動器的有效傳送速率。數(shù)據(jù)壓縮可以提高將數(shù)據(jù)傳送到磁帶驅(qū)動器的有效傳送速率，該速率超過通過禁用硬件壓縮所達到的速率。數(shù)據(jù)庫內(nèi)實數(shù)據(jù)的可壓縮性取決于數(shù)據(jù)本身和所使用的磁帶驅(qū)動器。對于大范圍的數(shù)據(jù)庫，典型的數(shù)據(jù)壓縮率是從1．2∶1到2∶1。該壓縮率對于在多種業(yè)務(wù)應(yīng)用程序中使用的數(shù)據(jù)是典型的，但有些數(shù)據(jù)庫可能有更高或更低的壓縮率。例如，主要包含已壓縮圖像的數(shù)據(jù)庫將不能再由磁帶驅(qū)動器進一步壓縮。有關(guān)數(shù)據(jù)壓縮的更多信息，請參見磁帶驅(qū)動器的供應(yīng)商文檔。
默認情況下SQL Server支持硬件壓縮，但可以使用3205跟蹤標記禁用硬件壓縮。在極少數(shù)情況下，禁用硬件壓縮可以提高備份性能。例如，如果數(shù)據(jù)已經(jīng)完全壓縮，禁用硬件壓縮可防止磁帶驅(qū)動器浪費時間試圖進一步壓縮數(shù)據(jù)。
8．傳送到磁帶的數(shù)據(jù)量
創(chuàng)建數(shù)據(jù)庫備份只捕獲數(shù)據(jù)庫中包含實際數(shù)據(jù)的部分，而不備份未使用的空間，其結(jié)果將使備份操作的速度更快。
雖然可以根據(jù)需要將SQL Server 2000數(shù)據(jù)庫配置為自動增長，但可繼續(xù)保留數(shù)據(jù)庫內(nèi)的空間以保證該空間可用。保留數(shù)據(jù)庫內(nèi)的空間對備份吞吐量和備份數(shù)據(jù)庫所需的總時間沒有負面影響。
9．優(yōu)化DBCC性能
數(shù)據(jù)庫控制臺命令(DBCC)往往大量占用CPU及磁盤，因為DBCC必須讀取每個數(shù)據(jù)頁，而這需要從磁盤到內(nèi)存全都檢查一遍(除非數(shù)據(jù)頁已高速緩存到內(nèi)存中)。當系統(tǒng)上有許多活動(如大量的查詢處理)而運行DBCC時，可用內(nèi)存減少，而且SQL Server 2000被迫將數(shù)據(jù)頁發(fā)送到tempdb數(shù)據(jù)庫中進行假脫機處理，DBCC的性能由此削弱。因此，如果使更多內(nèi)存可用于DBCC處理，將可以高速緩存數(shù)據(jù)庫中的更多內(nèi)容，從而使DBCC語句執(zhí)行得更快。
tempdb數(shù)據(jù)庫駐留在磁盤上，因此在將數(shù)據(jù)寫入或?qū)懗龃疟P時，來自I/O操作的瓶頸將削弱性能。對大型數(shù)據(jù)庫(相對于可用內(nèi)存的大小而言)，運行DBCC會導(dǎo)致送到tempdb數(shù)據(jù)庫進行假脫機處理，與系統(tǒng)活動無關(guān)。因此，建議將tempdb數(shù)據(jù)庫放置在與用戶數(shù)據(jù)庫分開的一個或多個快速磁盤上，如RAID(獨立磁盤冗余陣列)。
說明：執(zhí)行DBCC CHECKDB時將對數(shù)據(jù)庫內(nèi)的每個表白動執(zhí)行DBCC CHECKTABLE和DBCC CHECKALLOC，因而不必單獨運行這兩個語句。
10．優(yōu)化大容量復(fù)制性能
為盡可能快地大容量復(fù)制數(shù)據(jù)，可使用下列選項指定如何使用bcp實用工具或BULK INSERT語句將數(shù)據(jù)大容量復(fù)制到SQL Server 2000內(nèi)：
(1)使用有日志記錄和無日志記錄的大容量復(fù)制。
(2)對并行數(shù)據(jù)裝載使用bcp實用工具。
(3)控制鎖定行為。
(4)使用批處理。
(5)排序數(shù)據(jù)文件。
說明：如果可能，使用BULK INSERT語句而不是bcp實用工具將數(shù)據(jù)大容量復(fù)制到SQL Server內(nèi)。BULK INSERT語句比bcp實用工具快。
有兩個因素決定可以或應(yīng)該使用哪個選項以提高大容量復(fù)制操作性能：
(1)表內(nèi)現(xiàn)有的數(shù)據(jù)量相對于要復(fù)制到表內(nèi)的數(shù)據(jù)量。
(2)表上索引的數(shù)目和類型。
另外，這些因素還取決于是從單個客戶端還是并行從多個客戶端將數(shù)據(jù)大容量復(fù)制到表內(nèi)。
11．將數(shù)據(jù)從單個客戶端裝載到空表內(nèi)
當將數(shù)據(jù)從單個客戶端裝載到空表內(nèi)時，建議：
(1)指定TABLOCK提示，這使得在大容量復(fù)制操作過程中使用表級鎖。
(2)使用ROWS_PER_BATCH提示指定大的批處理大小，使用單個批處理代表整個文件的大小。
(3)指定無日志記錄的大容量復(fù)制操作，不應(yīng)在執(zhí)行無日志記錄操作之后創(chuàng)建事務(wù)日志備份。
另外，如果表有聚集索引并對數(shù)據(jù)文件內(nèi)的數(shù)據(jù)排序以匹配聚集索引鍵列，則將數(shù)據(jù)大容量復(fù)制到已經(jīng)有聚集索引鍵的表內(nèi)并指定ORDER提示。這明顯比在將數(shù)據(jù)復(fù)制到表內(nèi)之后創(chuàng)建聚集索引快。
如果表上還存在非聚集索引，則在將數(shù)據(jù)復(fù)制到表內(nèi)之前除去這些索引。若將數(shù)據(jù)大容量復(fù)制到?jīng)]有非聚集索引的表內(nèi)，然后重新創(chuàng)建非聚集索引，則一般比將數(shù)據(jù)大容量復(fù)制到已經(jīng)有非聚集索引的表內(nèi)快。
12．從多個客戶端并行裝載數(shù)據(jù)
如果SQL Server運行在有多個處理器的計算機上，并且可以將要大容量復(fù)制到表內(nèi)的數(shù)據(jù)分區(qū)成單獨的數(shù)據(jù)文件，則建議從多個客戶端將數(shù)據(jù)并行裝載到同一個表內(nèi)，從而提高大容量復(fù)制操作的性能。例如，將大容量復(fù)制從八個客戶端裝載到一個表里，每一個客戶端必須有一個包含分區(qū)數(shù)據(jù)的部分。為獲得最大性能，每個客戶端的批處理大小應(yīng)與客戶端數(shù)據(jù)文件相同。
從多個客戶端將數(shù)據(jù)復(fù)制到表內(nèi)時，應(yīng)考慮下列因素：
(1)必須先將表上的所有索引除去，然后在表上重新創(chuàng)建索引。考慮通過同時從單獨的客戶端創(chuàng)建每個輔助索引來并行重新創(chuàng)建輔助索引。
(2)在裝載時聚集索引不存在，因此使用已排序的數(shù)據(jù)和ORDER提示不會影響性能。
(3)數(shù)據(jù)必須分為多個輸入文件，每個客戶端一個文件。
與從單個客戶端的大容量復(fù)制操作一樣，建議：
(1)指定TABLOCK提示。這使得在大容量復(fù)制操作過程中使用表級鎖。
(2)使用ROWS_PER_BATCH提示指定大的批處理大小。建議對每個客戶端，使用單個批處理代表整個客戶端文件的大小。 (3)將select into／bulkcopy選項設(shè)置為真以啟用無日志記錄的操作。
