sqlserver效能調優(一)

 

SQL Server效能調優入門（圖文版） 第一步，在業務高峰期抓取樣本資料（2個小時左右）。採用的工具是sqlserver內建的profiler，也叫事件探查器，如： 進入後，點擊最左面的按鈕，建立一個新的跟蹤： 登入需要用DBO許可權，所以可以用sa登入，也可以用windows整合驗證方式（如果當前登入的就是sqlserver的話） 建立跟蹤，一共有4個tab頁進行配置，首先看第一個。跟蹤名稱不用更改，預設的即可。儲存一共有兩種方式，一是檔案，副檔名是.trc（這種方式方便你把客戶那裡的跟蹤結果發給你），其二是資料庫中的表。 為了分析方便，我們把它另存新檔表。此時sql提示你重新進行登入，這裡我們把表儲存到master中 假設表名字叫做jq（如果有重複的，系統會提示是否覆蓋） 確定後回到了剛才的第一個tab頁中： 然後切換到第二個選項卡中： 左面列出了各種事件類別（Event Class），右面是當前已有的事件類別。對於效能調優，我們不需要安全性稽核、會話資訊，點擊刪除按鈕即可： 繼續切換到第三個tab頁上，這裡的資料列預設就夠了，當然，如果你看著不順眼，可以把Appname/NT username等都刪除。 最後一個tab頁上，我們需要把系統自己產生的事件ID屏蔽掉： 把那個排除系統ID進行check即可，如： 所有項目配置好後，點擊“運行”按鈕。持續運行兩個小時左右即可（業務高峰期，能典型的反應客戶最近一段時間內的業務模式） 好了，第一步的準備工作完成了，等待一段時間後，我們開始檢查剛才自動儲存到master中的表jq。 第二步，開始尋找影響速度的地方。 開啟查詢分析器（sql analyzer），登入到master中，從 表jq裡面按照I/O倒序，讀取若干個sql。根據我的習慣，一般是讀取1000條記錄。為什麼根據I/O來找呢，而不是根據時間來找呢？原因很簡單，一句SQL執行，“穩定”的是I/O，而duration是一個不穩定的因素。我們進行sql調優的目的，就是降低I/O成本，從而提高效率。（一般而言，I/O降低了，duration自然就會降低）詳細內容， 執行完成後，我們仔細看下面的輸出。 1、 XL_TALLY_Proc04這個sp的reads最大，將近100w，duration也達到了25秒多。 2、 Erp_IM_GMBill_GetBill這個sp的I/O不算大，才7w，duration平均都在1秒多點。但是這個sp執行的次數非常多。 經過詢問客戶，XL_TALLY_Proc04這個sp執行的頻度很低，一天也就一兩次，但是Erp_IM_GMBill_GetBill大概5分鐘就要一次。這樣整體I/O就佔用的非常大。 所以這裡我們要重點分析Erp_IM_GMBill_GetBill這個sp，而不是第一個！ 總結一個原則就是： 調整的重點是客戶最關心的內容，是執行頻度最高、看起來I/O又比較大的那種。I/O最大的，不一定是我們要優先解決的內容。 第三步，開始分析剛才看到的那個語句。既然我們要分析I/O，那麼就要把I/O開啟，這樣每次調整sql，我們都能隨時看到I/O的變化情況。這句很有用處地：set statistics io on 單純看I/O變化，我們會暈倒的。因為我們不知道自己做的任何改動，對I/O是如何產生影響的。所以，還要看sql的執行計畫是怎佯的。 在查詢分析器中，我們按Ctrl+K，或者如的菜單，check上即可。 好了，準備工作都做好了，下面開始幹活了。 我們首先看sql語句的調優，假設下面這條sql語句效能低下： 上面的sql一共讀取了6636條資料，邏輯讀是1126。那麼這個I/O是否合理呢？大了還是小了？還有改進的餘地嗎？我們看執行計畫： 哦，一共4個咚咚在裡面。Index seek的成本佔了2%， index scan的佔了47%，hash match佔了51%，select最終是0%。我們應該牢記第二個原則，所有的index，儘可能的都走index seek。 我們看一下billsoflading的索引資訊： 當前索引為什麼走scan，這裡就不說了，感興趣的可以隨便找一本介紹資料庫索引的書籍來看看即可。根據我以前那篇blog的描述，我們知道應該建立一個複合索引（也叫convered index）：boldate+companyid+bolcode 然後我們重新執行sql，看看I/O變化情況： Ooh，非常cool！logical reads降低到了50。為什麼會這樣呢？我們看一下執行計畫： 原來是index scan變成了index seek，效率自然大大的提升！ Sql語句在index上調優的方法，基本就是這樣。我們繼續看sp的。 對於sp的調優，有一點是和sql調優不同的：sp內部的邏輯處理可能非常複雜。單純從查詢分析器中，我們無法得知哪一小塊的sql執行的I/O最大，我們只能看到一個總體的描述。所以，我們要知道sp內部的資訊。 首先，瞭解自己當前的spid是多少。一種方法是select @@spid，另一種方法是看查詢分析器下面的status bar的資訊。 Ooh，我的spid是101。（的最下面那個tips） 然後我重新開啟profiler（事件探查器），重建立立一個跟蹤，這裡面要修改第二個tab頁的資訊，把左面事件列“預存程序”中的SmtpCompleted加上 增加後的樣子如下： 然後修改第4個tab頁，把剛才看到的spid=101的資訊填上： 點擊運行後，這樣profiler只能抓到在查詢分析器中，spid=101那個視窗發送的sql。我們切換回查詢分析器，執行有問題的sp，執行完成後，我們再回到profiler，點停止按鈕。一個sp內部所有執行的sql，都被分開了！ 這次的結果假設儲存在了jq2表中，我們把所有執行的小片sql都列出來： 第一個是sp執行後的總體結果，I/O為62328，就是這個sp自己的。第二個是向暫存資料表中插入資料，I/O為61514，我們很容易看到，這一句佔用了整個sp的大概95%以上的成本。如果我們把這句insert into #temptable搞定，整個sp的成本自然就下來了。所以我們需要把這句insert搞出來。 但是慢著！default情況下，sqlserver的results只顯示很少的字元，第二行的sql，我們根本抓不全的，所以我們需要修改一下設定。在查詢分析器的工具-選項菜單中，切換到“結果”這個tab頁，修改每列最多字元個數為8192（這是最大的允許值），然後點擊“確定”按鈕，重新從jq2中讀取資訊。也許你會問，如果某個sql特別長，怎麼辦？其實很簡單，在你的代碼中把這句sql單獨寫到log中，或者直接修改sp，把這句print出來即可。 Ok，我們把這句insert sql抓下來後，放到查詢分析器中。因為temptable我們沒有它的結構，所以我們把insert部分注釋掉，看後面的select語句。執行後，ooh，在goodsmovement表上的成本是57834。 老辦法，我們繼續看執行計畫： 其實，現在又迴歸到了sql調優的步驟，下面的工作我就不寫啦！ 這個步驟，看起來很簡單，希望大家對於sql調優（索引部分）心中都有這麼一個概念，知道第一步作什麼，第二步作什麼。還是那句話，索引調優，基本上是最簡單的。但是貌似簡單的東西，我們越應該重視。你隨便找一個應用跟蹤一下，各種效率低下的索引，會讓你實在#￥*#（**……￥
轉自：http://blog.joycode.com/juqiang/archive/2007/01/19/91848.aspx