原文: 2年SQL Server DBA調(diào)優(yōu)方面總結(jié)

2 年 SQL Server DBA 調(diào)優(yōu)方面總結(jié)

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

書單

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

2.《 Troubleshooting SQL Server A Guide for the Accidental DBA 》 這本書是我接觸最早的關(guān)于性能調(diào)優(yōu)的書。鏈接已經(jīng)給出可以去下載，不過需要注冊SQLServerCenter ，這個網(wǎng)站是SQL Server 方面比較出名的網(wǎng)站。很多國外大牛。

3.《聯(lián)機文檔》也就是sql server 裝機后自帶的幫助文檔，內(nèi)容全面的嚇人，幾乎包含了技術(shù)內(nèi)幕系列的所有內(nèi)容。

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

5.《SQL Server 2008 內(nèi)核剖析和故障排除》接觸的第二本關(guān)于性能調(diào)優(yōu)的書，真本書比較絕的地方時，先將原理再講調(diào)優(yōu)。全書分為2部分第一部分就是原理，第二部分是性能調(diào)優(yōu)。也是不錯的一本，書中對擴展事件的功能做了比較詳細的解釋。我在其他書上是沒看到過的。

該書的2012英文原版已經(jīng)出了。

6.《Microsoft SQL Server企業(yè)級平臺管理實踐》是一本少見的國產(chǎn)好書，書的編寫很符合中國人心理，直指問題本身，很適合當工具書。其中有關(guān)于性能跟蹤調(diào)整，從捕獲到處理講的很實際。

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

8.《SQL Server 2008查詢性能調(diào)優(yōu)》這本書比較實用的一本書，講了各個瓶頸的發(fā)現(xiàn)，性能基線的簡歷，從查詢，存儲過程角度出發(fā)，分析性能，講解可能出現(xiàn)性能問題的點。

9.《Pro SQL Server 2008 Service Broker》 講解關(guān)于Service Broker，異步消息處理程序，很多比較大的公司會使用，我知道的是新蛋是使用這個的，全書圍繞一個大例子比較清晰，容易接受。

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

?

安全性

樓主是小公司的DBA所以關(guān)于安全性使用的比較少，就管理一些權(quán)限和密碼

可用性

到SQL Server 2012實現(xiàn)了多種可用性方案，1.日志傳送，2.數(shù)據(jù)庫復(fù)制，3.數(shù)據(jù)庫鏡像，4.alwaysonline。

1.日志傳送，樓主覺得是數(shù)據(jù)庫鏡像的雛形。沒有數(shù)據(jù)庫鏡像那樣試試的傳送和redo日志

2.數(shù)據(jù)庫復(fù)制，數(shù)據(jù)庫復(fù)制有比較多的分類：快照，事務(wù)，合并。事務(wù)復(fù)制是被應(yīng)用最廣的，從sql server 2000到sql server 2005事務(wù)復(fù)制被改進了很對具體可以看聯(lián)機文檔。

3.數(shù)據(jù)庫鏡像，我對于不需要讀寫分離的數(shù)據(jù)庫中，數(shù)據(jù)庫鏡像是被應(yīng)用最廣的可用性方案，數(shù)據(jù)庫鏡像和其他的比最突出的優(yōu)點是切換方便。

高性能

DBA的大頭應(yīng)該是性能調(diào)優(yōu)。性能的調(diào)優(yōu)大頭是索引，最求更高的性能索引是必不可少的。一個性能主要體現(xiàn)的執(zhí)行時間上，執(zhí)行時間= 運行時間+等待時間。這個公式我覺得很經(jīng)典。當你沒有頭緒的時候能幫你梳理清楚應(yīng)該怎么排查問題。做性能調(diào)優(yōu)一定要對性能的指標十分熟悉。

?

性能基線

當你剛剛?cè)肼氁患夜荆瑢緮?shù)據(jù)庫現(xiàn)在的負載一無所知，那么一開始要做的事情就是創(chuàng)建一個數(shù)據(jù)庫性能基線。有人會問基線能用來干什么，很多人感覺沒用，我剛?cè)肼殨r我也覺得沒用。但是性能基線是一個性能調(diào)優(yōu)，監(jiān)控的開始。

?

一般比較正規(guī)的公司，一個業(yè)務(wù)上線前會通過壓力測試預(yù)計這個服務(wù)器的性能邊境在哪里，到達性能邊境之后各個性能指標的表現(xiàn)是如何的。如果如果性能基線接近了性能邊界，到了這個時候，那么就要考慮換服務(wù)器或者加服務(wù)器了。這個是性能基線的一個用處。

?

拿到一個服務(wù)器我先會做一下性能基線，性能基線也就是服務(wù)器在正常運轉(zhuǎn)的時候數(shù)據(jù)庫的性能指標的表現(xiàn)。我會抓取24小時的性能指標作為性能基線（可以看我相關(guān)的文章： SQL Server? 性能基線和監(jiān)控 ， SQL Server? 性能調(diào)優(yōu) ( 性能基線 ) ）。

?

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

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，挨個看說明。

假設(shè)你現(xiàn)在已經(jīng)有了性能指標了，那么你就可以根據(jù)性能基線簡歷告警了，以前的文章（ SQL Server? 性能基線和監(jiān)控 ）中我已經(jīng)提供了使用powershell如何監(jiān)控性能。

?

性能運行性能問題分析：

基線建好了監(jiān)控也建好了，出現(xiàn)告警了。按講關(guān)于調(diào)優(yōu)的書上就會開始分開，分為CPU瓶頸，IO瓶頸，還是內(nèi)存瓶頸講。關(guān)于這些瓶頸的確認我這里就沒必要說了，在以前的文章 SQL Server? 性能調(diào)優(yōu)（ io ） ， SQL Server? 性能調(diào)優(yōu)（ cpu ） ， SQL Server? 性能調(diào)優(yōu)（內(nèi)存） 都有講到。如何確認各個瓶頸。

?

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

?

如果已經(jīng)確定是那部分照成的性能問題如IO，CPU，內(nèi)存。歸根結(jié)底就只有2中方法，1.調(diào)整。2.硬件升級。

?

如果問題出現(xiàn)了，要急著解決問題1.使用top 10 io，top 10 cpu，來查看需要優(yōu)化的語句根據(jù)執(zhí)行計劃進行調(diào)優(yōu)。還有就是通過profiler，前提是當前服務(wù)器還能允許你使用profiler。2008之后出現(xiàn)了擴展事件，可能可以通過這個來處理，但是關(guān)于擴展事件做跟蹤我還沒有涉及，相關(guān)資料也不是很多。

?

那么如何確定使用內(nèi)存比較多的語句呢，內(nèi)存有點特別，sql server把數(shù)據(jù)放在buffer pool里面，大家都能用，內(nèi)存壓力分為內(nèi)部和外部，內(nèi)部是sql server 自身引起的內(nèi)存壓力，外部是其他進程照成的內(nèi)存壓力（相關(guān)的只是可以查看sql server 2005 troubleshooting 白皮書）。

出現(xiàn)內(nèi)存瓶頸也就是buffer pool滿了，要清除原先的buffer pool數(shù)據(jù)才能把新讀入的數(shù)據(jù)存放在里面，那么就簡單了，那個語句讀取的最多那么哪個語句使用內(nèi)存最多（詳細內(nèi)容可以查看《Microsoft SQL Server企業(yè)級平臺管理實踐》）。

?

那么假設(shè)已經(jīng)定位到了一個出問題的SQL語句，那么接下來就是要優(yōu)化它，里面使用到的最關(guān)鍵的就是執(zhí)行計劃。如何根據(jù)執(zhí)行計劃優(yōu)化SQL語句不同的人想法都不太一樣。優(yōu)化方法和各有特色。所以不再升入以免以偏概全。但是運行時間主要還是這樣幾點：執(zhí)行計劃，統(tǒng)計信息，索引。

性能等待問題分析：

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

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

?

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

?

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

?

PAGEIOLATCH_x是在等待磁盤io時產(chǎn)生的，會產(chǎn)生磁盤io的原因也就是內(nèi)存中沒有數(shù)據(jù)，就是內(nèi)存不夠才會出現(xiàn)這種狀況，那么就加內(nèi)存吧。或者優(yōu)化一下業(yè)務(wù)規(guī)則。

?

WRITELOG 是寫入日志的時候出現(xiàn)了等待，日志是順序?qū)懙模举|(zhì)就是事務(wù)多寫入時磁盤速度不夠快出現(xiàn)了等待，如果有問題建議1.把日志和數(shù)據(jù)文件分開，放到2個獨立的盤或者raid，2.換成速度更快的盤。

?

PAGELATCH_x是操作buffer pool數(shù)據(jù)頁產(chǎn)生的閂。如果等待過大，很簡單就是調(diào)用這個頁的session過多，那么就減少對頁面的訪問。1.通過索引優(yōu)化語句，盡量減少sql讀取的頁面數(shù)量。2.想辦法把頁面的數(shù)據(jù)分散多個頁面。3.考慮讀寫分離。

?

tempdb上的PAGELATCH_x主要發(fā)生在GAM，SGAM，PFS幾個頁面，因為order by，group by，臨時表，表變量，lazy操作符。都會使用到tempdb，會開辟一個空間。如果并發(fā)量大。那么tempdb上的PAGELATCH_x的等待將會很大。1.減少執(zhí)行計劃中sort操作符，減少lazy操作符。2.把tempdb的數(shù)據(jù)文件擴展，上限是cpu個數(shù)（有個條件是tempdb容量要平衡）。

2年SQL Server DBA調(diào)優(yōu)方面總結(jié)