淺談如何優化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性能。