原文: 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容量要平衡）。

2年SQL Server DBA調優方面總結