SQL 問答集：推動 SQL 效能
總是有幾種方法推 SQL Server 性能信封，並常常不花任何錢這樣做。
Paul · S. Randal
新的正常
**問：**我們將要開始將重新設計我們的資料庫架構的專案，我們擔心遠如何正常化的事情。 你有什麼建議嗎？
**答：**要謹慎推進規範化太遠。 正常化行使的通常目標是正常的第三種形式或 3NF，凡所有非鍵屬性説明定義的鍵。 有是有説明的短語，描述了此規則："的屬性定義的鍵，整個鍵和沒有什麼，但金鑰，這樣可以説明我 Codd。" (E.F. Codd 最初定義 3NF 回在 1971 年 ； 請參閱此連結的詳細資訊。 這句話基於一個發誓在法庭的誓言。）
努力推動 3NF 很多人的問題走得太遠 — — 我稱之為 over-normalizing。 當查詢要聯接許多表實現有意義的關係，這會導致荷載作用下極大的性能問題。 一連串的聯接然後是達到各實體的唯一方法。
例如，我們有一個用戶端的架構 10 年前寫的 C# 開發人員不是特別是 SQL Server 的精明。 每個可能的實體存儲在其自己的表和有一個 id。 所有實體屬性都存儲在他們自己的表，用他們自己的 Id。 其中實體屬於其他實體的關係存儲在其各自的表中。 史上的每個屬性已更改為每個實體的如何存儲在其自己的表中，有其自己的 id。
所以特定實體表可能有很多其他表的外鍵引用。 這是哪裡做幾乎任何事情需要加入 10 或更多表嚴重過度規範化的架構。
更糟糕的是，有沒有存檔策略。 有多年的產生需要處理大量的資料的連接中的資料庫中的資料。 多個事情必須要解決此問題：
- 刪除舊的資料，以減少資料的大小和實施自動化的歸檔策略，保持活動狀態的資料集小。
- 考慮使用篩選索引和查詢謂語減少資料正在進行操作的數量。
- 考慮 de-normalizing 允許一些一起坍塌，降低複雜性聯接的表的架構的部件。
這裡是設計新架構時，請牢記的黃金法則：想想 T SQL 操作有效查詢的資料和嘗試儘量減少過度聯接所需或不必要地經營上非常大的資料集。 您還應您的設計原型和工作負荷過高，看看是否有任何明顯的設計瓶頸在運行一些代表性的查詢。
一旦你已經投入生產的架構變得非常困難，很昂貴進行任何更改。 這意味著你可能會花很多時間微調查詢和索引策略來彌補的過度規範化的架構的性能影響。
性能的困惑
**問：**我們主要的表具有聚集的索引的唯一整數識別欄位上。 他們已插入並一直保持相當小的行大小後，永遠不會更新表的行。 我發現，具有較高的併發連接，查詢性能慢下來。 你有任何想法什麼問題呢？
**答：**您設計您與我們的最佳實踐準則，聚集的索引鍵應獨特、 靜態、 窄和不斷增加的聚集的索引。 這工程完全直到你進入高端的工作量。
例如，與幾個幾千個併發連接 （或也許甚至幾百個） 所有插入表，將會阻止。 如果你看看與 sys.dm_tran_locks 動態管理視圖 (DMV) 持有的鎖，它沒有顯示出任何異常。 （在一個幾頁） 的頁面 IX 鎖和 X 鍵鎖 （在這些頁上的記錄） 持有的所有線程。 事情看上去正常，但有確實有些堵下去。 只阻止不涉及的鎖。 您可以找到有關鎖和鎖定在 SQL Server 中的詳細資訊連線叢書。
如果你看一下 sys.dm_os_waiting_tasks DMV，您將看到多數執行緒正在等待等待類型為 PAGELATCH_EX 的幾頁之一。 這是因為一個鎖定並不足以能夠更改資料檔案頁面的記憶體中副本。 Sys.dm_os_waiting_tasks 是最有用的 Dmv，順便說一句，因為它顯示了"中的所有線程"SQL Server 正在都等待。
關注的人是在記憶體中的頁面拷貝真是一種資料結構，如 SQL Server。 您不能更改的資料結構具有關系的鎖。 您必須從多個執行緒訪問和更改它立刻保護資料結構的完整性。 這是基本的基本電腦科學。 您可以管理更改 SQL Server 使用稱為的閂鎖，我以前已經在這裡討論的羽量級的同步機制內的資料結構的獨佔存取權限。
由於聚集的索引是識別欄位，插入將與每個資料頁上的許多行僅追加的插入模式。 這是一件好事。 然而，這意味著多個執行緒將嘗試同時在同一資料檔案頁上插入行。 所需的鎖不會阻塞，因為每個執行緒都有相容頁鎖和行鎖在單獨的行上。 但是，所有需要獲得一個專屬的執行緒鎖住在頁面上。 同時，這不可能。 隨著您的工作負載的增加，這可能導致重大的阻塞和查詢性能問題。
有了多種解決方案，包括：
- 使用分區來插入工作量遍佈幾個分區的表 （或甚至幾個表）
- 更改為一個複合群集金鑰，將自然地插入隨展開 (的開支導致碎片） 表中的幾個問題
底線，不過，是阻塞症狀並不總是意味著問題是鎖定。
性能的思考
**問：**我們在我們的 SQL 伺服器的性能問題，我們的開發人員說，我們需要一個更強大的伺服器來運行我們的工作量。 我們經典的"非自願"Dba。 你可以給我們任何意見還有什麼可説明以外購買更多的硬體？
**答：**人們往往沒有意識到性能差是使用 SQL Server 的方式的基本問題的一個症狀。 假設是你想要得到當前硬體的容量限制，是投資于具有更多和更快的 Cpu 的伺服器的時候。 因為移動到具有多個 Cpu 的伺服器打開了更多的時間視窗，可能會導致更高的爭用，減少工作量的輸送量，這可能是一個代價高昂的假設。
我們實際上見過這種情況發生幾次因為窮 T SQL 程式設計做法。
我們已經一再表明，如何將要便宜很多，更有利於解決潛在的性能問題而不是添加硬體。 您採取新的硬體暴跌之前，有許多的東西，你可以看看，看看是否可以解決的問題。 以下是一些範例：
- 看 sys.dm_os_waiting_tasks DMV 看到時間花在等待的資源。 如果所用的時間，是等待用戶端應用程式來處理資料，加強伺服器硬體不會有所作為。 如果汽車登記處中的許多行輸出清單等待類型為 ASYNC_NETWORK_IO，您可以看到這種模式。
- 檢查 I/O 子系統的讀/寫延遲使用 sys.dm_io_virtual_file_stats DMV 看到如果 I/O 子系統驅動太難。 這可能表示需要更好地索引的戰略。 Tempdb 也可能是瓶頸，可能表明過度使用的臨時表。
- 看看缺少索引 Dmv ，看看是否缺少高影響索引。
- 看看，如果您要維護 sys.dm_db_index_usage_stats — — 但不是使用 — — 一噸的索引。
簡單的改變可以經常有重大的影響，對 SQL Server 的性能。 有時只需增加資料量，你需要更多的伺服器記憶體壓力起飛緩衝集區。 有時 I/O 子系統真的不會需要打氣。 它不是很多時候，不過，你要買一個更大、 更快的伺服器。
平行線
**問：**我們有一些他們並行運行，我們想限制，以單線程時導致出現問題的查詢。 我們如何能做的不做一切單線程通過設置伺服器 MAXDOP 選項之一？
**答：**為背景，MAXDOP 意味著"最大並行度"。這基本上是多少併發平行線程時執行一個查詢可以使用。
這是很好不是"膝關節落淚"，將一切都設置為 MAXDOP 之一，並行度是 SQL Server 的最佳性能特徵之一。 很多人這樣做，特別是基於不明智的建議，指出應通過停止並行度固定 CXPACKET 等待 （從做等待統計分析） 的流行。
使用 sp_configure 選項來禁用並行度 （除了這一事實它會影響伺服器上的所有內容） 的問題之一是具有任何許可權級別的任何人都可以使用 MAXDOP 查詢提示中其查詢重寫該設置。 您始終可以對您想要限制的所有查詢指定 MAXDOP 一個查詢提示。 這可能是不切實際的不過，具有成百上千個查詢所涉及。
兩種方法是在限制並行度的有效得多：
- 增加伺服器範圍"並行度的成本門檻"。這是在查詢編譯過程中生成任意數目。 它用於決定是否生成或不執行並行查詢計畫。 通過增加此號碼，可以有效防止一些不應該這樣做的並行運行的查詢。 不能重寫或者使用查詢提示，此設置。 您可以閱讀有關此設置的詳細資訊以及如何更改它在博客文章中的喬納森 · Kehayias 這裡。
- 您還可以使用的資源調控功能 （僅企業版） 中。 這允許您查詢投入"水桶"（稱為工作負載組），然後將 MAXDOP 分配給每個桶。 您可以重寫 sp_configure MAXDOP 選項，但不是資源調控器。 可以使用任何篩選您要決定去哪個存儲桶中的哪些查詢。 您可以使用不同的 MAXDOP 設置有多個水桶。 更多的人正在使用此方法來獲得對並行度的細細微性控制。
.jpg)
Paul · S. Randal 為 SQLskills.com、 微軟的區域主任和 SQL 伺服器 MVP 的董事總經理。 他工作在 SQL Server 存儲引擎團隊在微軟從 1999 年到 2007 年。 他寫道： 為 SQL Server 2005 的 DBCC CHECKDB/修復，SQL Server 2008 的開發過程中負責核心存儲引擎。 Randal 是災難恢復、 高可用性和維護資料庫方面的專家，也是在世界範圍內的各次會議定期簡報者。 在他的博客 SQLskills.com/blogs/paul，你可以找到他在 Twitter 上和 twitter.com/PaulRandal。