SQL Server 服務器最佳化技巧淺談

1.資料和記錄檔分開存放在不同磁碟上

　　資料檔案和記錄檔的操作會產生大量的I/O。在可能的條件下，記錄檔應該存放在一個與資料和索引所在的資料檔案不同的硬碟上以分散I/O，同時還有利於資料庫的災難恢複。

　　2.tempdb資料庫單獨存放在不同磁碟上

　　tempdb資料庫是其他所有資料庫都有可能使用的臨時資料庫。當使用select into、在沒建立索引的列上執行Orderby時就會在tempdb資料庫中產生暫存資料表來儲存中間資料。由於建立和填充暫存資料表會嚴重降低系統效能，所以在儘可能的情況下應該為要排序的列建立索引。同時，tempdb資料庫是為所有的使用者和應用程式共用，所以如果一個使用者佔據了tempdb資料庫的所有空間，則其他資料庫將不能再使用。在可能的情況下，tempdb資料庫應該單獨放置在一個速度更快的硬碟或者RAID陣列上。分離tempdb資料庫的I/O操作以加快效能。tempdb資料庫應該有適當的容量，以滿足使用者的需要。應該允許tempdb資料庫的空間自動成長。如果設定為不允許自動成長，當查詢操作建立了超過tempdb資料庫容量的暫存資料表時，操作將無法完成。

　　適當設定tempdb資料庫的增長幅度，過小的增長幅度會產生更多的外部片段，會佔用更多的資源。

　　3.避免熱點資料的發生

　　在SQLServer7.0之前，對於沒有叢集索引的表(堆集表)，新插入的資料行總是放置在磁碟中表的物理結尾處。如果並發的使用者很多，同時在對錶執行插入或者更新資料的操作，這將使得十分繁忙的表的末尾有可能產生資料熱點。並發的I/O操作集中對少數頁面進行操作，將導致資料庫效能的下降。

　　在SQLServer中，新的資料行的實體儲存體空間的分配是通過PFS頁面來進行的。PFS頁面的管理演算法將插入操作進行分散來盡量避免產生資料熱點。

　　在設計應用系統和資料庫時，要避免在自然增長的列上建立主鍵，這樣有可能導致熱點資料的發生。

　　4.資料類型要少

　　在設計表時，儘可能少用資料類型。這樣一個資料頁面上可以儲存最多的資訊。資料頁面就少，檢索資料頁面的I/O操作就少，所以效率會高。

　　5.監控和整理空間片段

　　檔案空間的自動成長提高了自動管理性，但可能導致空間片段。物理空間與資料的邏輯空間不再連續。週期性監控和空間磁碟重組有利於提高I/O效能。

　　6.使用主要資料檔案和次要資料檔案

　　每個資料庫的一個主要資料檔案屬於主檔案組。對於1GB左右規模的資料庫，一個資料檔案就夠了，如果有次要資料檔案，主要資料檔案中有管理次要資料檔案的指標。

　　採用多個資料檔案時，主要資料檔案用於儲存系統對象和表，次要資料檔案用於儲存使用者資料和索引。在可能的情況下，主要資料檔案和次要資料檔案可以單獨存放在不同的磁碟上以分散I/O。

　　如果採用多個資料檔案，推薦主要資料檔案儲存體系統資料，次要資料檔案存放使用者資料和索引，這樣會有助於提高I/O效能。