2年SQL Server DBA調優方面總結

2年SQL Server DBA調優方面總結

當2年dba 我覺得，有些東西需要和大家分享探討，先書單。

書單

1.《深入解析SQL Server 2008 系列》 這個就是mssql 2005 的技術內幕系列。2012版的也出了有興趣可以看看，技術內幕系列是我接觸最早的書，裡面內容涵蓋量很大，但是都是點到為止。所以很多都是可以細細品味，回頭再看的。

2.《Troubleshooting SQL Server A Guide for the Accidental DBA》 這本書是我接觸最早的關於效能調優的書。連結已經給出可以去下載，不過需要註冊SQLServerCenter ，這個網站是SQL Server 方面比較出名的網站。很多國外大牛。

3.《聯機文檔》也就是sql server 裝機後內建的協助文檔，內容全面的嚇人，幾乎包含了技術內幕系列的所有內容。

4.《The.Gurus.Guide.To.SQL.Server.Architecture.And.Internals》這本書是將sql server 2000的核心，從軟體開發的角度來看SQL Server 2000，很深入作者也十分的出名，可惜死的太早。對sql server架構理解主要來源於這本書，可惜沒有中文版。

5.《SQL Server 2008 核心剖析和故障排除》接觸的第二本關於效能調優的書，真本書比較絕的地方時，先將原理再講調優。全書分為2部分第一部分就是原理，第二部分是效能調優。也是不錯的一本，書中對擴充事件的功能做了比較詳細的解釋。我在其他書上是沒看到過的。

該書的2012英文原版已經出了。

6.《Microsoft SQL Server企業級平台管理實踐》是一本少見的國產好書，書的編寫很符合中國人心理，直指問題本身，很適合當工具書。其中有關於效能跟蹤調整，從捕獲到處理講的很實際。

7.《SQLSERVER求生秘籍》和《The.Gurus.Guide.To.SQL.Server.Architecture.And.Internals》是同一個作者，這本書主要是針對SQL Server 2005和上一本一樣對個別點講的很深入，缺點講到的東西太少。

8.《SQL Server 2008查詢效能調優》這本書比較實用的一本書，講了各個瓶頸的發現，效能基準的簡曆，從查詢，預存程序角度出發，分析效能，講解可能出現效能問題的點。

9.《Pro SQL Server 2008 Service Broker》 講解關於Service Broker，非同步訊息處理常式，很多比較大的公司會使用，我知道的是新蛋是使用這個的，全書圍繞一個大例子比較清晰，容易接受。

10.《Pro SQL Server 2008 Policy-Based Management》關於策略管理方面的知識，個人覺得比較雞肋。

 

安全性

樓主是小公司的DBA所以關於安全性使用的比較少，就管理一些許可權和密碼

可用性

到SQL Server 2012實現了多種可用性方案，1.記錄傳送，2.資料庫複寫，3.資料庫鏡像，4.alwaysonline。

1.記錄傳送，樓主覺得是資料庫鏡像的雛形。沒有資料庫鏡像那樣試試的傳送和redo日誌

2.資料庫複寫，資料庫複寫有比較多的分類：快照，事務，合并。事務複製是被應用最廣的，從sql server 2000到sql server 2005事務複製被改進了很對具體可以看聯機文檔。

3.資料庫鏡像，我對於不需要讀寫分離的資料庫中，資料庫鏡像是被應用最廣的可用性方案，資料庫鏡像和其他的比最突出的優點是切換方便。

高效能

DBA的大頭應該是效能調優。效能的調優大頭是索引，最求更高的效能索引是必不可少的。一個效能主要體現的執行時間上，執行時間= 已耗用時間+等待時間。這個公式我覺得很經典。當你沒有頭緒的時候能幫你梳理清楚應該怎麼排查問題。做效能調優一定要對效能的指標十分熟悉。

 

效能基準

當你剛剛入職一家公司，對公司資料庫現在的負載一無所知，那麼一開始要做的事情就是建立一個資料庫效能基準。有人會問基準能用來幹什麼，很多人感覺沒用，我剛入職時我也覺得沒用。但是效能基準是一個效能調優，監控的開始。

 

一般比較正規的公司，一個業務上線前會通過壓力測試預計這個伺服器的效能邊境在哪裡，到達效能邊境之後各個效能指標的表現是如何的。如果如果效能基準接近了效能邊界，到了這個時候，那麼就要考慮換伺服器或者加伺服器了。這個是效能基準的一個用處。

 

拿到一個伺服器我先會做一下效能基準，效能基準也就是伺服器在正常運轉的時候資料庫的效能指標的表現。我會抓取24小時的效能指標作為效能基準（可以看我相關的文章：SQL Server 效能基準和監控，SQL Server 效能調優(效能基準)）。

 

以下是我使用的抓取的指標

cpu：

　　　　\Processor(_Total)\% Processor Time
　　　　\Processor(_Total)\% Privileged Time

　　　　\SQLServer:SQL
Statistics\Batch Requests/sec
　　　　\SQLServer:SQL Statistics\SQL Compilations/sec
　　　　\SQLServer:SQL Statistics\SQL Re-Compilations/sec
　　　　\System\Processor Queue Length
　　　　\System\Context Switches/sec

　　Memory:

　　　　\Memory\Available
Bytes
　　　　\Memory\Pages/sec
　　　　\Memory\Page Faults/sec
　　　　\Memory\Pages Input/sec
　　　　\Memory\Pages Output/sec
　　　　\Process(sqlservr)\Private Bytes
　　　　\SQLServer:Buffer Manager\Buffer cache hit ratio
　　　　\SQLServer:Buffer Manager\Page life expectancy
　　　　\SQLServer:Buffer Manager\Lazy writes/sec
　　　　\SQLServer:Memory Manager\Memory Grants Pending
　　　　\SQLServer:Memory Manager\Target Server Memory (KB)
　　　　\SQLServer:Memory Manager\Total Server Memory (KB)

　　Disk:

　　　　\PhysicalDisk(_Total)\%
Disk Time
　　　　\PhysicalDisk(_Total)\Current Disk Queue Length
　　　　\PhysicalDisk(_Total)\Avg. Disk Queue Length
　　　　\PhysicalDisk(_Total)\Disk Transfers/sec
　　　　\PhysicalDisk(_Total)\Disk Bytes/sec
　　　　\PhysicalDisk(_Total)\Avg. Disk sec/Read
　　　　\PhysicalDisk(_Total)\Avg. Disk sec/Write

　　SQL Server:

　　　　\SQLServer:Access
Methods\FreeSpace Scans/sec
　　　　\SQLServer:Access Methods\Full Scans/sec
　　　　\SQLServer:Access Methods\Table Lock Escalations/sec
　　　　\SQLServer:Access Methods\Worktables Created/sec
　　　　\SQLServer:General Statistics\Processes blocked
　　　　\SQLServer:General Statistics\User Connections
　　　　\SQLServer:Latches\Total Latch Wait Time (ms)
　　　　\SQLServer:Locks(_Total)\Lock Timeouts (timeout > 0)/sec
　　　　\SQLServer:Locks(_Total)\Lock Wait Time (ms)
　　　　\SQLServer:Locks(_Total)\Number of Deadlocks/sec
　　　　\SQLServer:SQL Statistics\Batch Requests/sec
　　　　\SQLServer:SQL Statistics\SQL Re-Compilations/sec

指標代表啥意思我就不解釋了，你可以開perfmon，挨個看說明。

假設你現在已經有了效能指標了，那麼你就可以根據效能基準簡曆警示了，以前的文章（SQL Server 效能基準和監控）中我已經提供了使用powershell如何監控效能。

 

效能運行效能問題分析：

基準建好了監控也建好了，出現警示了。按講關於調優的書上就會開始分開，分為CPU瓶頸，IO瓶頸，還是記憶體瓶頸講。關於這些瓶頸的確認我這裡就沒必要說了，在以前的文章SQL Server 效能調優（io），SQL Server 效能調優（cpu），SQL Server 效能調優（記憶體）都有講到。如何確認各個瓶頸。

 

其實這些辨認瓶頸的方法都是不夠全面的，瓶頸確認需要經驗，因為往往出現效能問題了，不是一個指標，而是一批指標都有問題，比如當你索引沒建好，導致了全表掃描，io變大，cpu飆高，記憶體出現分頁，所以有時候十分難判斷。

 

如果已經確定是那部分照成的效能問題如IO，CPU，記憶體。歸根結底就只有2中方法，1.調整。2.硬體升級。

 

如果問題出現了，要急著解決問題1.使用top 10 io，top 10 cpu，來查看需要最佳化的語句根據執行計畫進行調優。還有就是通過profiler，前提是當前伺服器還能允許你使用profiler。2008之後出現了擴充事件，可能可以通過這個來處理，但是關於擴充事件做跟蹤我還沒有涉及，相關資料也不是很多。

 

那麼如何確定使用記憶體比較多的語句呢，記憶體有點特別，sql server把資料放在buffer pool裡面，大家都能用，記憶體壓力分為內部和外部，內部是sql server 自身引起的記憶體壓力，外部是其他進程照成的記憶體壓力（相關的只是可以查看sql server 2005 troubleshooting 白皮書）。

出現記憶體瓶頸也就是buffer pool滿了，要清除原先的buffer pool資料才能把新讀入的資料存放在裡面，那麼就簡單了，那個語句讀取的最多那麼哪個語句使用記憶體最多（詳細內容可以查看《Microsoft SQL Server企業級平台管理實踐》）。

 

那麼假設已經定位到了一個出問題的SQL語句，那麼接下來就是要最佳化它，裡面使用到的最關鍵的就是執行計畫。如何根據執行計畫最佳化SQL語句不同的人想法都不太一樣。最佳化方法和各有特色。所以不再升入以免以偏概全。但是已耗用時間主要還是這樣幾點：執行計畫，統計資訊，索引。

效能等待問題分析：

等待時間：鎖等待，閂鎖等候。

關於資源等待，這裡有三篇文章，《SQL Server 效能調優 Wait Event》，《SQL Server 效能調優 Wait Event （二）》，《SQL Server 2008 效能調優 session層級 wait event》作者是同一個人。通過WaitEvent的角度來調整。所以在此之前需要先瞭解關於sys.dm_os_wait_stats 中相關指標主要指的是什麼意思，關於這個SQL
Server出了一個《SQL Server 2005 Waits and Queues》很詳細的介紹了各個指標的意思。《SQL Server 2008查詢效能調優》中有個很好的關於收集堵塞情況的SQL語句。

 

當收集到堵塞如果是出現在鎖層級上的，那麼沒有其他辦法，用索引或者在select 語句上面加nolock，或者開帶快照的隔離等級，但是個人比較不贊成快照隔離等級，有朋友已經測試過，一開快照隔離等級，tempdb的負載增加十分明顯，一個問題解決導致了另外一個更棘手的問題。若是select語句，盡量使用覆蓋索引，來減少因為引用多個索引導致和update死結的情況。當然這個也看具體的系統運行環境而定。

 

如果是出現在閂上，一般比較大的指標是PAGEIOLATCH_x系列的，WRITELOG，PAGELATCH_x,tempdb上的PAGELATCH_x。

 

PAGEIOLATCH_x是在等待磁碟io時產生的，會產生磁碟io的原因也就是記憶體中沒有資料，就是記憶體不夠才會出現這種狀況，那麼就加記憶體吧。或者最佳化一下商務規則。

 

WRITELOG 是寫入日誌的時候出現了等待，日誌是順序寫的，本質就是事務多寫入時磁碟速度不夠快出現了等待，如果有問題建議1.把日誌和資料檔案分開，放到2個獨立的盤或者raid，2.換成速度更快的盤。

 

PAGELATCH_x是操作buffer pool資料頁產生的閂。如果等待過大，很簡單就是調用這個頁的session過多，那麼就減少對頁面的訪問。1.通過索引最佳化語句，盡量減少sql讀取的頁面數量。2.想辦法把頁面的資料分散多個頁面。3.考慮讀寫分離。

 

tempdb上的PAGELATCH_x主要發生在GAM，SGAM，PFS幾個頁面，因為order by，group by，暫存資料表，表變數，lazy操作符。都會使用到tempdb，會開闢一個空間。如果並發量大。那麼tempdb上的PAGELATCH_x的等待將會很大。1.減少執行計畫中sort操作符，減少lazy操作符。2.把tempdb的資料檔案擴充，上限是cpu個數（有個條件是tempdb容量要平衡）。