none
偶爾會發生select 條件含有like .order by時效能突然下降(Win2003 Server STD x64 sp2 + SQL2005 x64 sp2 in VMWare) RRS feed

  • 問題

  • 我的環境是

    Win2003 Server STD x64 sp2 + SQL2005 x64 sp2

     灌在 VMWare上(ram 12G )

    目前偶爾會發生select JOIN TABLE 的條件含有like .order by時效能突然下降卡住(平常執行該SQL不到1秒)

    當下檢查

    1.檢查DB無鎖死現象

    2.CPU吃不到15%, SQL RAM吃到3.8G左右,系統還剩4G. C槽剩餘空間5G,DB檔所在D槽剩餘空間20G

    3.like .order by條件拿掉後的JOIN TABLE速度又正常(資料各為5000筆及4000筆)

    4.其他TABLE的 ORDER BY,LIKE同樣有問題

    5.最後SQL服務重啟後恢復正常

    像是SQL在因條件組temp data時出現問題(感覺被限制住)

    請問有什麼可以選項可以校調或監控的嗎

    (目前先嘗試放大C槽處理,懷疑是VM swap file機制的問題 )

    謝謝~



    永遠的初學者

    2014年3月11日 上午 08:13

解答

  • SELECT tblA.row1,tblA.row2,tblA.row3 
    FROM tblA LEFT JOIN 
    tblB ON tblA.row1=tblB.row1 AND tblA.row2=tblB.row2
    WHERE tblA.row3 like '%123456%'

    tblA 5000多筆

    tblB 4000多筆

    因為SQL服務重啟後 執行這段SQL不到1秒就出來

    所以會朝系統設定問題去想

    給你的連結你根本沒看。

    SELECT tblA.row1,tblA.row2,tblA.row3
    FROM (SELECT * FROM tblA WHERE row3 like '%123456%') AS tblA

    LEFT JOIN tblB ON tblA.row1=tblB.row1 AND tblA.row2=tblB.row2

    另外不要把記憶體設成無上限,我的經驗是無上限容易死得莫名其妙,我習慣是把記憶體上限設在(總量 - 系統經常用量 - 2GB 保留量) ,讓 SQL Server 能觸發記憶體限制,釋放舊的快取。

    Embedded 系統我會抓空閒記憶體 1/2 ~ 2/3 作為上限。


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?


    2014年3月12日 下午 12:38
  • Run your query with order by clause and check if there's blocking with 'sp_who2 active' while your query is running, can get execution plan at same time if you choose query -> include actual execution plan in menu in ssms.
    • 已標示為解答 保持初心 2014年3月17日 上午 05:57
    2014年3月14日 下午 01:10

所有回覆

  • Did you see contention in tempdb? Is tempdb on its own disk? Possible to install sql2k5 sp4? 
    2014年3月11日 下午 12:56
  • LIKE 本來就很吃效能。

    這篇先看看,調整完你的 SQL 語法後,在整句貼出來討論。

    把兩個 JOIN 表筆數也貼出來,數量過多的話,可能需要透過暫存資料表。

    [SQL] Join 的 WHERE 條件式位置


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?

    2014年3月11日 下午 04:01
  • what's the wait type/wait resouece when you run this query? If move order by, does the performance look better?


    Please Mark As Answer if it is helpful.

    2014年3月12日 上午 01:33
  • 1.當時沒注意看是否有tempdb連線,只注意卡住的幾個TABLE

    2.一樣再C槽(從一個raid5 切出 60G, C=>20G(剩5G) D=>40G(user db在此,剩25G))

    3.我會找時間升上去 再觀察

    謝謝~


    永遠的初學者

    2014年3月12日 上午 01:52
  • SELECT tblA.row1,tblA.row2,tblA.row3 
    FROM tblA LEFT JOIN 
    tblB ON tblA.row1=tblB.row1 AND tblA.row2=tblB.row2
    WHERE tblA.row3 like '%123456%'

    tblA 5000多筆

    tblB 4000多筆

    因為SQL服務重啟後 執行這段SQL不到1秒就出來

    所以會朝系統設定問題去想

    謝謝


    永遠的初學者


    2014年3月12日 上午 01:57
  • 1.下次在發生時我會QUERY一下這個項目

    2.當時測試結果是QUERY若是有 like或ORDER BY就會卡住,移掉後單純的SELECT JOIN+明確的where = '' 就正常

    謝謝


    永遠的初學者

    2014年3月12日 上午 02:08
  • Check max memory for sql. You said that sql only used 3.8gb memory while machine has 12gb memory, sounds low. Allocate more memory to sql if you can, and double check execution plan to ensure tables have proper index.
    2014年3月12日 上午 02:09
  • 記憶體部份我是設成無上限

    永遠的初學者

    2014年3月12日 上午 03:19
  • Then join 9000 rows shouldn't be big issue if tables have proper indices.
    2014年3月12日 上午 03:44
  • 或許你可以嘗試看看執行計劃或是set statistics io on,來查看是否有哪些動作或IO造成你感覺到卡住的狀況。

    以上說明若有錯誤請指教,謝謝。

    | 台灣 SQL PASS 社群 | SQL PASS Taiwan

    | 歡迎參觀我的BLOG - 積沙成塔

    2014年3月12日 上午 03:47
    版主
  • 索引就是PKEY

    主要是平常RUN都OK 1秒內就出來

    JOIN出來的結果集約10筆左右

    我是想若是索引問題 應該會常常法生此問題

    目前是是第二次發生 上一次是約3週前

    同樣的程式在其他點的主機也有

    DB是一樣的內容,資料量還有更大的。但是目前都沒問題。


    永遠的初學者

    2014年3月12日 上午 07:14
  • 目前執行解果如下:

    ---------------------------------

    SQL Server 剖析與編譯時間: 
       CPU 時間 = 31 ms,經過時間 = 38 ms。

    (688 個資料列受到影響)
    ..........

    (1 個資料列受到影響)

    SQL Server 執行次數: 
       CPU 時間 = 62 ms,經過時間 = 463 ms。
    SQL Server 剖析與編譯時間: 
       CPU 時間 = 0 ms,經過時間 = 1 ms。

    ---------------------------------

    但是有問題時怕會直接卡住 

    結果會出不來


    永遠的初學者

    2014年3月12日 上午 07:40
  • SELECT tblA.row1,tblA.row2,tblA.row3 
    FROM tblA LEFT JOIN 
    tblB ON tblA.row1=tblB.row1 AND tblA.row2=tblB.row2
    WHERE tblA.row3 like '%123456%'

    tblA 5000多筆

    tblB 4000多筆

    因為SQL服務重啟後 執行這段SQL不到1秒就出來

    所以會朝系統設定問題去想

    給你的連結你根本沒看。

    SELECT tblA.row1,tblA.row2,tblA.row3
    FROM (SELECT * FROM tblA WHERE row3 like '%123456%') AS tblA

    LEFT JOIN tblB ON tblA.row1=tblB.row1 AND tblA.row2=tblB.row2

    另外不要把記憶體設成無上限,我的經驗是無上限容易死得莫名其妙,我習慣是把記憶體上限設在(總量 - 系統經常用量 - 2GB 保留量) ,讓 SQL Server 能觸發記憶體限制,釋放舊的快取。

    Embedded 系統我會抓空閒記憶體 1/2 ~ 2/3 作為上限。


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?


    2014年3月12日 下午 12:38
  • 目前執行解果如下:

    ---------------------------------

    SQL Server 剖析與編譯時間: 
       CPU 時間 = 31 ms,經過時間 = 38 ms。

    (688 個資料列受到影響)
    ..........

    (1 個資料列受到影響)

    SQL Server 執行次數: 
       CPU 時間 = 62 ms,經過時間 = 463 ms。
    SQL Server 剖析與編譯時間: 
       CPU 時間 = 0 ms,經過時間 = 1 ms。

    ---------------------------------

    但是有問題時怕會直接卡住 

    結果會出不來


    永遠的初學者

    This is not execution plan. Optimizer generates plan based on statistics so tables should have proper index with up to date stats. How often you update stats by the way? From what you said, looks like optimizer sometimes picked wrong plan with out of date stats.
    2014年3月12日 下午 12:56
  • 一開始我發文就提了,可能我寫得不夠清楚

    3.like .order by條件拿掉後的JOIN TABLE速度又正常(資料各為5000筆及4000筆)

    4.其他TABLE的 ORDER BY,LIKE同樣有問題

    你的LINK我有看,只是那串SQL只是舉個例子(同事當時查資料用的SQL),並不是程式這樣寫

    問題發生的時候光其他TABLE做SELECT或SELECT JOIN 都OK加入ORDER BY

    LIKE就會有問題

    例如我自己用簡單的 Select * from tblA order by tblA.rowA 測也會卡

    拿掉order by就過了

    所以我會朝系統問題去想

    這台VM主機問題發生後我已先做了

    1.放大C槽

    2.SQL記憶體限制在8G

    目前問題沒再次發生

    後續要再觀察一段時間(不過一次調了數個設定 最後就算WORK也不能確定是哪個起作用)



    永遠的初學者

    2014年3月13日 下午 12:51
  • 只是回應網友的建議(set statistics io on)做的測試

    目前我們部沒有定期針對各TABLE去做這項校調

    只有針對特定程式在執行效能有問題時

    才會針對其DB行為錄trc檔再用database engine tuning advisor分析

    再參考其建議做調整 


    永遠的初學者

    2014年3月13日 下午 01:00
  • 若硬碟為 SATA ,開個新資料庫,把資料匯進去,重跑看看,SQL Server 很傷磁碟。

    rowA 應建立索引,Order 才會快,已有建立索引可砍掉重建,避免索引錯誤造成的問題。


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?

    2014年3月13日 下午 01:53
  • 只是回應網友的建議(set statistics io on)做的測試

    目前我們部沒有定期針對各TABLE去做這項校調

    只有針對特定程式在執行效能有問題時

    才會針對其DB行為錄trc檔再用database engine tuning advisor分析

    再參考其建議做調整 


    永遠的初學者

    You can get estimated and/or actual execution plan in ssms, don't need profiler trace.
    2014年3月13日 下午 02:19
  • SQL RAM吃到3.8G左右?
    算什麼....

    我這邊Windows剛重開時 SQL RAM大約吃4GB
    不到1個月就吃到快過 5GB 了
    因為也沒什麼錯誤訊息可以查, 到底問題出在哪裡
    只能定期重新開機了

    like  或是 left join
    我也拼命在用
    有的查詢得跑上約30~60秒才會出來
    明明是實體主機, CPU有2顆 (核心12, 邏輯處理器24個)
    RAM 使用中 16.3GB 可用的 11.5GB
    SQL Server還是跑這麼慢
    我也無言啊

    player

    2014年3月13日 下午 03:21
  • Most of slow queries are due to missing index, that's why you should check execution plan as first step.
    2014年3月13日 下午 04:01
  • 其實是USER反應使用系統有資料讀取問題(卡住)

    同事當時他是隨手組的該程式TABLE相關的SQL去RUN發現有問題

    在我被告知DB有問題進去看的時候做了測試

    其實不只他試的那一個有問題 是都卡住

    我簡單對"其他"TABLE做select =>ok ,但只要一加 like就卡 加order by也掛

    但當時 硬體的資源是充足的 

    所以推測 是SQL系統設定的問題

    當然對table做index優化可以提升效能較不易落入軟硬體效能瓶頸

    但我比較疑惑的是 為何SQL SERVER"有錢不會花 卻要餓肚子"

    這個會是我想要優先解決的問題

    謝謝


    永遠的初學者

    2014年3月14日 上午 02:26
  • 是SAS RAID5+VMWare

    其實是USER反應使用系統有資料讀取問題(卡住)

    同事當時他是隨手組的該程式TABLE相關的SQL去RUN發現有問題

    在我被告知DB有問題進去看的時候做了測試

    其實不只他試的那一個有問題 是都卡住

    我簡單對"其他"TABLE做select =>ok ,但只要一加 like就卡 加order by也掛

    但當時 硬體的資源是充足的 

    所以推測 是SQL系統設定的問題

    當然對table做index優化可以提升效能較不易落入軟硬體效能瓶頸

    但我比較疑惑的是 為何SQL SERVER"有錢不會花 卻要餓肚子"

    這個會是我想要優先解決的問題

    謝謝


    2014年3月14日 上午 02:30
  • Did you see blocking in the db?
    2014年3月14日 上午 03:17
  • 是看sql 活動監視器 上有無 keylock....之類的嗎

    當下是沒有

    還是其他的?


    永遠的初學者

    2014年3月14日 上午 03:48
  • Run your query with order by clause and check if there's blocking with 'sp_who2 active' while your query is running, can get execution plan at same time if you choose query -> include actual execution plan in menu in ssms.
    • 已標示為解答 保持初心 2014年3月17日 上午 05:57
    2014年3月14日 下午 01:10
  • LIKE 演算法本來就是所有比較裡面最慢的,所以把 SQL 語法最佳化,只比較必要的項目,調整 WHERE 欄位順序。

    這是基本觀念,還沒進正規化就要有的觀念。


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?

    2014年3月14日 下午 01:39
  • Alternate of LIKE, try with full text index. Much faster for string search. 
    2014年3月14日 下午 03:10
  • 謝謝

    下次發生時我會這樣試


    永遠的初學者

    2014年3月17日 上午 02:19