SQL Server 阻塞的常見原因和解決辦法
1、由于語句運行時間太長而導(dǎo)致的阻塞,語句本身在正常運行中,只須等待某些系統(tǒng)資源
解決辦法:
a)語句本身有沒有可優(yōu)化的空間
b)Sql Server 整體性能如何,是不是有資源瓶頸影響了語句執(zhí)行速度,如 內(nèi)存、硬盤 和 CPU 等
2、由于一個未按預(yù)期提交的事務(wù)導(dǎo)致的阻塞
這一類阻塞的特征,就是問題連接早就進(jìn)入了空閑狀態(tài)(sysprocesses.status='sleeping'和sysprocesses.cms='awaiting command'),但是,如果檢查 sysprocesses.open_tran,就會發(fā)現(xiàn)它不為0,以及事務(wù)沒有提交。這類問題很多都是因為應(yīng)用端遇到了一個執(zhí)行超時,或者其他原因,當(dāng)時執(zhí)行的語句倍提前終止了,但是連接還保留著。應(yīng)用沒有跟隨發(fā)來的事務(wù)提交或回滾指令,導(dǎo)致一個事務(wù)被遺留在 Sql Server 里。
解決辦法:
應(yīng)用程序本身必須意識到任何語句都有可能遇到意外終止的情況,做好錯誤處理工作。這些工作包括:
● 在做 Sql Server 調(diào)用的時候,須加上錯誤捕捉和處理語句:If @@Trancount>0 RollBack Tran;(在程序中設(shè)置If @@Error<>0 Rollback Tran; 并不總是能執(zhí)行到該語句)
● 設(shè)置連接屬性"Set XACT_ABORT ON"。如果沒有辦法很規(guī)范應(yīng)用程序的錯誤撲捉和處理語句,一個最快的方法就是在每個連接建立以后,或是容易出問題的存儲過程開頭,運行 "Set XACT_ABORT ON"
● 考慮是否要關(guān)閉連接池。發(fā)一句 sp_reset_connection 命令清理當(dāng)前連接上次遺留下來的所有對象,包括回滾未提交的事務(wù)。
3、由于客戶端沒有及時把結(jié)果集取出而導(dǎo)致的語句長時間運行
語句在 Sql Server 內(nèi)執(zhí)行總時間不僅包含 Sql Server 的執(zhí)行時間,還包含把結(jié)果集發(fā)給客戶端的時間。如果結(jié)果集比較大,Sql Server 會分幾次打包發(fā)出,沒發(fā)一次,都要等待客戶端的確認(rèn)。只有確認(rèn)以后,Sql Server 才會發(fā)送下一個結(jié)果集包。所有結(jié)果都發(fā)完以后,Sql Server才認(rèn)為語句執(zhí)行完畢,釋放執(zhí)行申請的資源(包括鎖資源)。如果出于某種原因,客戶端應(yīng)用處理結(jié)果非常緩慢甚至沒有響應(yīng),或者干脆不理睬 Sql Server 發(fā)送結(jié)果集的請求,則 Sql Server 會耐心的等待,銀次會導(dǎo)致語句長時間執(zhí)行而產(chǎn)生阻塞。
解決辦法:
a)慎重返回大結(jié)果集
b)如果a短期內(nèi)不能實現(xiàn),則嘗試大結(jié)果集的連接使用 Read Uncommitted 事務(wù)隔離級別,這樣查詢就不會申請 S 鎖了