SQL 問答集：微調以達最佳效能
重複的索引、取消的回復作業和 I/O 暴增可能會導致效能問題，但是您可以採取因應措施。
保羅 · S. Randal
重複的索引
**問：**SQL Server，似乎讓我創建是完全一樣的在同一表中的索引。 這是如何説明我工作負載的性能？ 不同的查詢將使用同一索引的不同副本嗎？
**答：**不幸的是 SQL Server 允許重複的索引，是因為它們提供任何沒有任何好處。 事實上，重複的索引可以是不利的在很多方面。
索引鍵是完全相同的順序和同一 ASC 或 DESC 規範與指定的另一個索引相同時，將出現重複的索引。 包含的列 （如果有） 也是一樣 （儘管可以按任意順序指定包含的列）。
SQL Server 將只使用重複的索引的查詢，幫，但它必須保持在過程中的一個表的所有索引插入、 更新和刪除操作。 這意味著每次插入或刪除的表，它必須在所有索引中反映出來。 也是索引的如此的更新，如果正在更新的列的一部分。
此索引維護使用的額外資源，並生成額外的事務日誌記錄 — — 所有的都是本質上是毫無用處的索引。 這些重複的索引會額外的磁碟空間和備份中的額外空間 — — 和頁面所需的索引維護太採取額外的空間，在記憶體中。
重複的索引很可能會變成碎片。 他們也需要常規索引碎片刪除期間的額外資源。 額外的事務日誌記錄從索引維護和碎片去除還會導致較低的高可用性 (HA) 功能，如資料庫鏡像和異動複寫的性能。
SQL Server 為您不提供了任何警告您剛才創建重複的索引，所以舉證責任是對您避免這樣做。 檢查是否已有重複的索引是不小的問題。 它涉及到了所有索引定義和手動比較他們還是廣泛方案解析系統目錄的腳本。 去年，特裡普金巴厘張貼這一問題的完整解決方案。
提防回滾
**問：**最近取消長時間運行更新。 回滾操作後下, 一步的日常事務日誌備份是巨大的。 我指望它會很小，因為在資料庫中並沒有任何變化。 你能解釋一下這個地區嗎？
**答：**這是一個相當常見的誤解。 如果回滾一個大型的操作，則下一個差異備份應該右小，嗎？ 錯！
任何 SQL Server 資料庫，對進行了更改的時間發生兩件事。 首先，它將生成描述更改的事務日誌記錄。 第二，對於由變化修改的任何資料檔案頁，相應的位設置差異點陣圖中。 這表示這些頁面應將下一個差異備份備份。
時回滾操作，SQL Server 會，撤銷所做的更改進行操作。 這意味著它會檢查操作的部分向前由生成的所有事務日誌記錄。 它必須以相反的順序撤銷這些更改。 每個事務日誌記錄描述單個操作的一部分作為資料庫更改。 要回滾該更改，您有另一個更改的資料庫，否定了最初的更改的影響。 例如，您將回滾記錄插入通過刪除該記錄。 淨效果是記錄不存在。
這是最令人費解的部分： 在回滾過程中執行的每個更改是真的只是另一個資料庫更改 （儘管是特殊的一個）。 資料庫的每次更改，必須有事務日誌記錄。 因此，即使在回滾所做的更改必須正確登錄。 這意味著大操作回滾將生成的不只有事務日誌記錄為前部的操作，而且也為回滾。 事務日誌備份將備份的所有這些事務日誌記錄，會計大的事務日誌備份。
當操作的部分向前導致差異點陣圖，有位因為資料庫的部分已更改的設置時，您不能清除差異點陣圖中的位再次因為資料庫已更改。 更改最終回退，也沒關係。 資料檔案頁仍 （兩次，事實上），改變了，所以必須通過備份的差異備份。
關鍵是問題的即使當操作回滾，則資料庫問題的仍然改變。 需要所有的備份，以反映這些變化。
峰值的尋找
**問：**我正在診斷問題，我們從我們的 SQL 伺服器之一的人看到了定期的 I/O 尖峰。 我也將它縮小到檢查站使用效能監視器，但我不能告訴哪個資料庫是罪魁禍首。 我如何可以進一步深入？
**答：**檢查點存在兩個原因。 第一，他們用什麼都已寫入事務日誌更新資料檔案頁。 SQL Server 使用稱為預寫日誌記錄，在將資料庫更改事務日誌中描述前正反映在資料檔案的機制。 這可以保證耐久性的撞車事件中的更改。 第二，他們減少的恒 I/O 負載只寫出更改的資料檔案頁定期，而不是每次更改後，每個資料檔案的頁。
檢查點分別發生的每個資料庫。 他們正在觸發基於多個因素，包括恢復的時間間隔 — — 這是 SQL Server 估計足夠事務日誌生成自上一個檢查點以便崩潰恢復將花費大約一分鐘 （預設）。
這個數位相當於每年有成千上萬的個人事務日誌記錄的生成。 更多的資料檔案頁更改這些事務日誌記錄，較大的必須由資料庫檢查點執行的 I/O 數量。
您可以跟蹤檢查點在 SQL Server 中使用"檢查點頁/秒"計數器：Buffer Manager 效能物件底下。 這只給聚合計數在 SQL Server 實例上的所有資料庫。 要確定哪個資料庫正在"執行檢查點操作"在任何時候，您需要使用跟蹤標誌。
如果您啟用跟蹤標誌 3502 （跟蹤列印時出現檢查點）、 3504 （跟蹤列印有關的詳細資訊，檢查點） 和 3605 （允許跟蹤列印轉到錯誤日誌），你能確定哪個資料庫占到檢查點由於 I/O 尖峰。
您可以啟用這些跟蹤標誌使用命令：
DBCC TRACEON (3502, 3504, 3605, -1)
禁用它們再次使用命令：
DBCC TRACEOFF (3502, 3504, 3605, -1)
隨後的檢查點將會產生與下麵類似的錯誤日誌中的輸出：
2011-12-30 05:07:14.390 spid17s Ckpt dbid 21 started (8) 2011-12-30 05:07:14.390 spid17s About to log Checkpoint begin. 2011-12-30 05:07:14.390 spid17s Ckpt dbid 21 phase 1 ended (8) 2011-12-30 05:07:14.830 spid17s FlushCache: cleaned up 4307 bufs with 201 writes in 441 ms (avoided 23 new dirty bufs) 2011-12-30 05:07:14.830 spid17s average throughput: 76.30 MB/sec, I/O saturation: 198, context switches 392 2011-12-30 05:07:14.830 spid17s last target outstanding: 15, avgWriteLatency 2 2011-12-30 05:07:14.830 spid17s About to log Checkpoint end. 2011-12-30 05:07:14.830 spid17s Ckpt dbid 21 complete
這允許您將看到哪個資料庫正在執行檢查點操作並從 PerfMon 匹配的資訊。 然後可以調查為什麼會有這麼多檢查點之間被更改的資料，請執行更頻繁的檢查點，以減少對 I/O 飆升，或增加 I/O 子系統的容量。
整合的擔憂
**問：**我公司已實行一項新政策，要求我們鞏固，盡可能降低硬體成本。 我也是被推來減少要保存許可證成本的 SQL Server 實例的數目。 有任何指引，以每個 SQL Server 實例的資料庫數量有意義嗎？
**答：**這個問題的答案是一個大"就看"。因素的清單包括資料庫、 他們正在運行的工作負載類型、 資料、 所需的定期維護的類型和災難恢復和醫管局要求的波動性的大小。
每個 SQL Server 實例具有有限的空間在記憶體中存儲資料檔案頁正在處理的任何時候 （這被稱為緩衝集區）。 更多的資料庫上有一個完全不同的工作負載的實例所有需要處理，會有緩衝集區空間的工作負載之間的更多競爭。
這可以導致系統失效緩衝集區記憶體。 將不斷流失，使從磁片讀取的新資料檔案頁的空間。 此外將大量的讀取 I/O 與高接受讀取延遲時間。 所有這些因素都將降低工作負載的性能。
如果各種工作負載，導致資料庫更改，亦會定期檢查點從寫入 I/O。 許多資料庫整合的單個實例，可能有多個檢查點同時發生。 這可能會導致 I/O 寫入延遲 — 放緩的檢查點操作，進一步造成工作負載的性能退化。
定期維護資料庫也將成為大量資料庫的問題。 如果每個資料庫需要索引和統計維修、 一致性檢查和備份，它可以將安排所有資料庫的所有這些操作，使他們不要相互衝突，把更多的 I/O 負載放在伺服器上的挑戰。
有多個資料庫實例上，保護他們所有使用本機的醫管局技術，SQL server 將成為的越難。 更有可能你需要某種形式的 I/O 子系統級別複製技術 — — 甚至只是從易管理的角度。 這意味著可以抵消伺服器整合帶來的成本節約的額外資本支出。
整合是一個巨大的主題。 完全做它正義是超出了單個列的範圍。 這是足夠讓你的 over-consolidating 謹慎思考。 在另一方面，可能有許多小的資料庫，您可能沒有問題的單個實例上的主機的最小工作負載。 如我之前所說，這取決於。
.jpg)
保羅 · 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。