[轉(zhuǎn)帖]從數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)查找解決阻塞問(wèn)題

jazy

　　一個(gè)大型數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)做得不好，會(huì)遇到連接到server的進(jìn)程被另一個(gè)正在讀寫(xiě)數(shù)據(jù)庫(kù)的進(jìn)程阻塞。這種阻塞數(shù)量越來(lái)越多，以至于client端好像死機(jī)。連接server越來(lái)越慢或?qū)е鲁瑫r(shí)連接不成功是引起阻塞的重要原因。通過(guò)對(duì)多個(gè)已投入運(yùn)行的數(shù)據(jù)庫(kù)應(yīng)用軟件的修改優(yōu)化，我們已能夠解決這種問(wèn)題。此方法主要從如下幾個(gè)方面入手：

　　1． 檢查所有存儲(chǔ)過(guò)程。

　　 存儲(chǔ)過(guò)程中如果有語(yǔ)句：

　　begin tran

　　 select ???from x where ???

　　 update y set ???

　　 if a條件 return（number）

　　 commit tran

　　那么a條件成立，則此存儲(chǔ)過(guò)程運(yùn)行時(shí)，對(duì)x，y表的鎖將不會(huì)釋放，從而阻塞其它進(jìn)程。這時(shí)用“sp－who”看該“spid”進(jìn)程狀態(tài)應(yīng)為“awaiting command”。應(yīng)將該存儲(chǔ)過(guò)程修改為

　　 “begin tran

　　 select ??? from x where ???

　　 update y set ???

　　 if a

　　 begin

　　 rollback tran

　　 return（number）

　　 end

　　 ???

　　 commit tran”

　　

　　2． 檢查存儲(chǔ)過(guò)程，將大事物細(xì)小化。

　　金融、電信業(yè)務(wù)中，經(jīng)常有一些批量業(yè)務(wù)，如代發(fā)工資、代扣話費(fèi)等。這種業(yè)務(wù)一般要用到“cursor”，但應(yīng)盡量避免以下這種情況：

　　 begin tran

　　 select ??? from x where ???

　　 declare x－cursor cursor

for ???

　　 fetch x－cursor into ???

　　 while @@sqlstatus!=2

　　 begin

　　 update ???

　　 insert ???

　　 fetch x－cursor into

　　 end

　　commit tran

　　因?yàn)�，事物中使用的所有表的相關(guān)頁(yè)在進(jìn)程提交前一直被鎖，并長(zhǎng)時(shí)間阻塞所有訪問(wèn)該頁(yè)的進(jìn)程。應(yīng)該為：

　　 select ??? from x where ???

　　 declare x－cursor cursor

　　 for ???

fetch x－cursor into ???

　　 while @@sqlstatus!=2

　　 begin

　　 begin tran

　　 update ???

　　 insert ???

　　 commit tran

　　 fetch x－cursor into

　　 end

　　經(jīng)過(guò)這樣修改，既可提高存儲(chǔ)過(guò)程的執(zhí)行速度，又不影響其它網(wǎng)點(diǎn)的業(yè)務(wù)，阻塞機(jī)會(huì)有非常明顯的減少。

　　3． 使用sybase數(shù)據(jù)庫(kù)設(shè)計(jì)調(diào)優(yōu)策略及數(shù)據(jù)庫(kù)應(yīng)用調(diào)優(yōu)策略。

　　sybase各種調(diào)優(yōu)策略能夠使存儲(chǔ)過(guò)程的運(yùn)行效率得到明顯提高，運(yùn)行速度大幅度提高，從而縮短阻塞時(shí)間及減少阻塞概率。如下幾個(gè)方面會(huì)對(duì)解決阻塞有明顯效果。

　　1） 數(shù)據(jù)庫(kù)應(yīng)用表的設(shè)計(jì)合理，應(yīng)盡量避免大表間跨表操作。

　　2） 索引使用優(yōu)化，提高讀寫(xiě)速度。

　　3） 盡量做到索引覆蓋查詢。

　　4） 對(duì)引起阻塞業(yè)務(wù)相應(yīng)存儲(chǔ)過(guò)程，根據(jù)其特點(diǎn)，使用其他相應(yīng)的優(yōu)化措施。

　　5） 經(jīng)常對(duì)應(yīng)用表進(jìn)行update statistic，sp－recompile，改善數(shù)據(jù)頁(yè)的充滿度，提高存儲(chǔ)過(guò)程的運(yùn)行效率。

　　4． 確定引起阻塞的存儲(chǔ)過(guò)程和表的方法。

　　有了上述解決問(wèn)題的辦法，怎樣從龐大的應(yīng)用系統(tǒng)中，尋找相應(yīng)的存儲(chǔ)過(guò)程呢？

　　首先，當(dāng)發(fā)生阻塞時(shí)要及時(shí)保留數(shù)據(jù)庫(kù)系統(tǒng)運(yùn)行的有關(guān)現(xiàn)場(chǎng)信息，包括：sp－who、sp－lock的運(yùn)行結(jié)果、master庫(kù)sysprocesses表的內(nèi)容。

　　sp－who的運(yùn)行結(jié)果可用來(lái)查出引起阻塞的進(jìn)程的spid。通過(guò)blk域的值找對(duì)應(yīng)的spid，如果blk值不為0，則該進(jìn)程被spid=blk值的進(jìn)程阻塞。這樣找下去，直至找到spid對(duì)應(yīng)的blk值為0，則此spid進(jìn)程為引起阻塞進(jìn)程。然后記錄其spid、loginname、hostname、dbname、cmd等。

　　sp－lock的運(yùn)行結(jié)果可用來(lái)查出對(duì)哪個(gè)表寫(xiě)操作引起阻塞。根據(jù)sp－who得到的spid，找sp－lock結(jié)果中對(duì)應(yīng)的locktype為ex－table－blk的一行，取table－id值，通過(guò)object－name（table－id）得到表名。

　　master庫(kù)sysprocesses表的內(nèi)容可用來(lái)查出須修改優(yōu)化的存儲(chǔ)過(guò)程。根據(jù)spid找到相應(yīng)行，記錄其status、hostname、cmd、id、linenum等通過(guò)object－name（id）得到存儲(chǔ)過(guò)程名。得到上述信息可對(duì)存儲(chǔ)過(guò)程進(jìn)行修改優(yōu)化，解決阻塞問(wèn)題（舉例過(guò)程略）。

aladdin

推薦跟蹤存儲(chǔ)過(guò)程名另一個(gè)方法:
dbcc traceon(3604)
dbcc traceoff
權(quán)限要夠哦

chenfeng825

us sp_showplan spid,null,null,null display query plan