SQLServer效能優化一則小例項（2010-07-22）

今天下午優化了一個儲存過程，通過sys.dm_exec_query_stats和sys.dm_exec_sql_text() 定位到的，發現執行次數雖然很少，但是每次卻長達上千萬毫秒的cpu消耗，但實際執行雖然時間比較久，卻也不過幾十分鐘而已，不知道是不是SQLServer系統效能檢視的缺陷。

既然有問題那就找吧
這是一個儲存過程，類似於
create procedure sp_exec_task
as
declare cursor cur_test for select * from tableA
begin
open cur_test
fetch cur_test into ...
WHILE @@FETCH_STATUS=0   
BEGIN
  if true
    update tableB where id=tableA.id and other_cond
  else
    update tableB where id=tableA.id and other_cond
  if true
    update tableC where id=tableA.id and other_cond
  else
    update tableC where id=tableA.id and other_cond  
  fetch cur_test into ...
end
CLOSE cur_test
DEALLOCATE cur_test
end
怎麼分析呢？
1、開始的時候是讓遊標空迴圈，發現一共1萬多條記錄，空迴圈時間基本為0
2、再次把所有的DML語句轉化為SELECT，並記錄每個步驟的執行時間和一次完整遊標的迴圈時間
最後迴圈中變為
BEGIN
  print 'step 1'+convert(varchar,109,getdate()
  select * from tableB where id=tableA.id and other_cond
  print 'step 1'+convert(varchar,109,getdate()
  select * from tableC where id=tableA.id and other_cond
END
通過上百次的迴圈測試，發現每次迴圈大概需要60毫秒，100次的花就是6秒，10000次可不就是10分鐘
3、檢查了一下游標迴圈中用的表和where條件，發現選擇性不錯，就新增索引，再次安裝上面的辦法進行測試
這次是單次迴圈0~1毫秒，100次大概是1秒，10000次還是需要1分多鐘的
4、1分多鐘是可以忍受的，乾脆直接測完吧，執行過程中，不斷發現記憶體消耗極大，很快居然耗光了記憶體
5、新增了SET NOCOUNT ON之類的，執行後還是記憶體暴增
6、後來思考了一下是不是select * from tableB的不斷重新整理導致的，直接修改為
  select top 1 @tt=tt from tableB where id=tableA.id and other_cond
這樣就不會持續重新整理螢幕了
7、執行後，果然只需要短短的4秒鐘。

總結：
其實在資料庫中與效能相關的，無論是耗cpu還是耗記憶體還是耗硬碟還是鎖的問題，分析到最後，95%以上都與SQL和索引相關
首先要找到問題，才能談到分析問題，分析問題就在於多實踐，而實踐在於儘量遮蔽與問題無關的外界因素。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6517/viewspace-668803/，如需轉載，請註明出處，否則將追究法律責任。