使用優化實用工具來優化SQL Server效能

　在Microsoft SQL Server 2008中提供了一些優化實用工具，使用這些工具可以有效地提高資料庫的使用效率。在一般人的眼裡，“優化”可能算得上是一種高階技能，但真正做起來也不是很難，您也可以成為這方面的高手，不信您就試一試。本文從十二個方面來談如何優化SQL Server 2008，使其獲得較高效能。優化資料庫可在生產資料庫上執行，獲得最佳效能收益的三個操作包括：
　　◆ 備份和還原操作。
　　◆ 將資料大容量複製到表中。
　　◆ 執行資料庫控制檯命令(DBCC)操作。
　　一般情況下，不需要優化這些操作。然而，在效能很關鍵的情形中，可採用以下一些技巧來優化效能。
　　
　　優化備份和還原效能
　　
　　SQL Server 2008提供幾種方法以提高備份及還原操作的速度：
　　◆ 使用多個備份裝置使得可以將備份並行寫入所有裝置。同樣，可以將備份並行從多個裝置還原。備份裝置的速度是備份吞吐量的一個潛在瓶頸，使用多個裝置可以按使用的裝置數成比例提高吞吐量。
　　◆ 使用資料庫備份、差異資料庫備份和事務日誌備份的組合，可以將故障恢復所需的時間減到最少。差異資料庫備份可以減少必須應用於恢復資料庫操作的事務日誌量。這種方法通常比建立完整資料庫備份快。
　　◆ 使用日誌記錄和最小日誌記錄大容量的複製操作。
　　
　　優化資料庫、差異資料庫和檔案備份效能
　　
　　建立資料庫備份包含兩個步驟：
　　（1）將資料從資料庫檔案複製到備份裝置。
　　（2） 將事務日誌中用於將資料庫前滾到一致狀態的那部分複製到相同的備份裝置。
　　與建立資料庫備份一樣，建立差異資料庫備份也包括上面兩步，但只複製已更改的資料（儘管需要讀取所有資料庫頁以確定資料是否更改）。備份資料庫檔案只需一步，將資料從資料庫檔案複製到備份裝置。
　　用於儲存資料庫的資料庫檔案按磁碟裝置排序，並給每個裝置指派讀取器執行緒，給每個備份裝置指派寫入器執行緒。該讀取器執行緒從資料庫檔案中讀取資料，寫入器執行緒將資料寫入備份裝置。通過在更多的邏輯驅動器中分佈資料庫檔案可以增加並行讀取操作。同樣，通過使用更多的備份裝置可以增加並行寫操作。
　　一般情況下，瓶頸是資料庫檔案或備份裝置。如果讀取吞吐總量比備份裝置吞吐總量大，則瓶頸在備份裝置這一側。新增更多的備份裝置（如果必要還需新增SCSI控制器）可以提高效能。然而，如果備份吞吐總量比讀取吞吐總量大，則應在裝置上新增更多的資料庫檔案或者在RAID（獨立磁碟冗餘陣列）裝置內使用更多磁碟，以便增加讀取吞吐量。
　　
　　優化事務日誌備份效能
　　
　　建立事務日誌備份只包含單個步驟，將日誌中尚未備份的部分複製到備份裝置。雖然可能有多個事務日誌檔案，但事務日誌在邏輯上是某個執行緒按順序讀取的一個流。
　　給每個備份裝置指派讀取器/寫入器執行緒。通過新增更多的備份裝置可以獲得更高的效能。
　　瓶頸可能是包含事務日誌檔案的磁碟裝置或者是備份裝置，具體取決於它們的相對速度和使用的備份裝置數。新增更多備份裝置將提高線性比例，直到達到包含事務日誌檔案的磁碟裝置最大容量，此後如果不通過使用磁碟條帶化等方法提高包含事務日誌的磁碟裝置的速度，將不可能獲得更多效能收益。
　　
　　優化還原效能
　　
　　還原資料庫備份或差異資料庫備份包含四個步驟：
　　（1）建立資料庫和事務日誌檔案，如果二者還沒有存在。
　　（2）將資料從備份裝置複製到資料庫檔案。
　　（3）從事務日誌檔案複製事務日誌。
　　（4）前滾事務日誌，然後（如果需要）重新啟動恢復。
　　應用事務日誌備份包含兩個步驟：
　　（1）將資料從備份裝置複製到事務日誌檔案。
　　（2）前滾事務日誌。
　　還原資料庫檔案包含兩個步驟：
　　（1）建立任何丟失的資料庫檔案。
　　（2）將資料從備份裝置複製到資料庫檔案。
　　如果資料庫和事務日誌檔案還沒有存在，必須先建立它們才能將資料還原到其中。建立資料庫和事務日誌檔案並將檔案內容初始化為零。使用單獨的工作執行緒並行建立和初始化檔案。按磁碟裝置排序資料庫和事務日誌檔案，並給每個磁碟裝置指派單獨的工作執行緒。建立和初始化檔案需要很大的吞吐量，因此在可用的邏輯驅動器中均勻分佈檔案能產生最佳效能。
　　通過讀取器/寫入器執行緒將資料和事務日誌從備份裝置複製到資料庫和事務日誌檔案，給每個備份裝置指派一個執行緒。複製效能受備份裝置傳送資料的能力或資料庫和事務日誌檔案接收資料的能力的限制。因此，複製效能隨新增的備份裝置數線性地提高，直到達到資料庫或事務日誌檔案接收資料能力的極限。
　　前滾事務日誌操作的效能已經固定，除使用更快的計算機外不能進一步優化。
　　
　　優化磁帶備份裝置效能
　　
　　有四個變數影響磁帶備份裝置的效能，並使SQL Server備份及還原效能操作得以在大體上隨新增更多磁帶裝置而提高線性比例。
　　◆ 軟體資料塊大小。
　　◆ 共享小型計算機系統介面(SCSI)匯流排的磁帶裝置數。
　　◆ 磁帶裝置型別。
　　軟體資料塊大小是由SQL Server為最佳效能計算的，不應更改。
　　許多高速磁帶驅動器如果對每個所使用的磁帶驅動器有專用SCSI匯流排，將執行得更好。本機傳輸速率超過SCSI匯流排速度的50%的驅動器必須在專用SCSI匯流排上。
　　注意：永遠不要將磁帶驅動器與磁碟或CD-ROM驅動器放置在同一個SCSI匯流排上。對這些裝置的錯誤處理操作互不相容。
　　
　　優化磁碟備份裝置效能
　　
　　磁碟備份裝置的原始I/O速度影響磁碟備份裝置效能，並使SQL Server備份及還原效能操作得以在大體上隨新增多個磁碟裝置而線性提高。
　　在對磁碟備份裝置使用RAID（獨立磁碟冗餘陣列）時需認真考慮。例如，RAID5的寫入效能低，大致與單個磁碟的速度相同（由於必須維護奇偶資訊）。另外，將資料追加到檔案的原始速度明顯比原始裝置寫入速度慢。
　　如果將備份裝置高度條帶化，以使對備份裝置的最大寫入速度遠遠超過備份裝置將資料追加到檔案的速度，則在相同的條帶集上放置幾個邏輯備份裝置會比較合適。換句話說，可以通過在相同的邏輯驅動器上放置幾個備份媒體家族來提高備份效能。然而，需要採用經驗方法確定這對每個環境是收益還是損失。通常情況下，最好將每個備份裝置放置在單獨的磁碟裝置上。
　　一般在SCSI匯流排上只有少數幾個磁碟可以以最大速度執行，但Ultra-wide和Ultra-2匯流排可以處理更多磁碟。不過，很可能需要認真配置硬體以獲得最佳效能。
　　
　　資料壓縮
　　
　　如今的磁帶驅動器有內建的硬體資料壓縮，可顯著提高將資料傳送到驅動器的有效傳送速率。資料壓縮可以提高將資料傳送到磁帶驅動器的有效傳送速率，該速率超過通過禁用硬體壓縮所達到的速率。資料庫內實資料的可壓縮性取決於資料本身和所使用的磁帶驅動器。對於大範圍的資料庫，典型的資料壓縮率是從1.2:1到2:1。該壓縮率對於在多種業務應用程式中使用的資料是典型的，但有些資料庫可能有更高或更低的壓縮率。例如，主要包含已壓縮影像的資料庫將不能再由磁帶驅動器進一步壓縮。有關資料壓縮的更多資訊，請參見磁帶驅動器的供應商文件。
　　預設情況下SQL Server支援硬體壓縮，但可以使用3205跟蹤標記禁用硬體壓縮。在極少數情況下，禁用硬體壓縮可以提高備份效能。例如，如果資料已經完全壓縮，禁用硬體壓縮可防止磁帶驅動器浪費時間試圖進一步壓縮資料。
　　
　　傳送到磁帶的資料量
　　
　　建立資料庫備份只捕獲資料庫中包含實際資料的部分，而不備份未使用的空間，其結果將使備份操作的速度更快。
　　雖然可以根據需要將SQL Server 2008資料庫配置為自動增長，但可繼續保留資料庫內的空間以保證該空間可用。保留資料庫內的空間對備份吞吐量和備份資料庫所需的總時間沒有負面影響。
　　
　　優化DBCC效能
　　
　　資料庫控制檯命令(DBCC)往往大量佔用CPU及磁碟，因為DBCC必須讀取每個資料頁，而這需要從磁碟到記憶體全都檢查一遍（除非資料頁已快取記憶體到記憶體中）。當系統上有許多活動（如大量的查詢處理）而執行DBCC時，可用記憶體減少，而且SQL Server 2008被迫將資料頁傳送到tempdb資料庫中進行假離線處理，DBCC的效能由此削弱。因此，如果使更多記憶體可用於DBCC處理，將可以快取記憶體資料庫中的更多內容，從而使DBCC語句執行得更快。
　　tempdb資料庫駐留在磁碟上，因此在將資料寫入或寫出磁碟時，來自I/O操作的瓶頸將削弱效能。對大型資料庫（相對於可用記憶體的大小而言），執行DBCC會導致送到tempdb資料庫進行假離線處理，與系統活動無關。因此，建議將tempdb資料庫放置在與使用者資料庫分開的一個或多個快速磁碟上，如RAID（獨立磁碟冗餘陣列）。
　　說明：執行DBCC CHECKDB時將對資料庫內的每個表自動執行DBCC CHECKTABLE和DBCC CHECKALLOC，因而不必單獨執行這兩個語句。
　　
　　優化大容量複製效能
　　
　　為儘可能快地大容量複製資料，可使用下列選項指定如何使用bcp實用工具或BULK INSERT語句將資料大容量複製到SQL Server 2008內：
　　◆ 使用有日誌記錄和無日誌記錄的大容量複製。
　　◆ 對並行資料裝載使用bcp實用工具。
　　◆ 控制鎖定行為。
　　◆ 使用批處理。
　　◆ 排序資料檔案。
　　說明：如果可能，使用BULK INSERT語句而不是bcp實用工具將資料大容量複製到SQL Server內。BULK INSERT語句比bcp實用工具快。
　　有兩個因素決定可以或應該使用哪個選項以提高大容量複製操作效能：
　　（1）表內現有的資料量相對於要複製到表內的資料量。
　　（2）表上索引的數目和型別。
　　另外，這些因素還取決於是從單個客戶端還是並行從多個客戶端將資料大容量複製到表內。
　　
　　將資料從單個客戶端裝載到空表內

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-520857/，如需轉載，請註明出處，否則將追究法律責任。