SQL SERVER效能分析--死結檢測資料庫阻塞語句

標籤：des   blog   http   使用   os   io   檔案   資料   

SQL SERVER效能分析--死結檢測資料庫阻塞語句

工作中資料庫經常出現記憶體,找了篇文章

 

參照CSDN，中國風(Roy)一篇死結文章 阻塞：其中一個事務阻塞，其它事務等待對方釋放它們的鎖,同時會導致死結問題。

整理人：中國風(Roy) 參照Roy_88的部落格

http://blog.csdn.net/roy_88/archive/2008/07/21/2682044.aspx

日期:2008.07.20 ************************************************************************************************************************/

產生測試表--產生測試表Ta if not object_id("Ta") is null drop table Ta go create table Ta(ID int Primary key,Col1 int,Col2 nvarchar(10)) insert Ta select 1,101,"A" union all select 2,102,"B" union all select 3,103,"C" go

 

產生資料:

/* 表Ta ID Col1 Col2 ----------- ----------- ---------- 1 101 A 2 102 B 3 103 C (3 行受影響) */

 

1、將處理阻塞減到最少: 2、不要在事務中請求使用者輸入 3、在讀資料考慮便用行版本管理 4、在事務中盡量訪問最少量的資料 5、儘可能地使用低的交易隔離等級 阻塞1(事務):

 

事務阻塞--測試單表 -----------------------串連視窗1(update\insert\delete)------------------------------ begin tran --update update ta set col2="BB" where ID=2 --或insert --begin tran -- insert Ta values(4,104,"D") --或delete --begin tran -- delete ta where ID=1 --rollback tran -------------------------串連視窗2（查詢表）--------------------------------------------- begin tran select * from ta --rollback tran --- --分析-------------------------------------------------- -->SQL SERVER 2005查詢死結進程 select request_session_id as spid, resource_type, db_name(resource_database_id) as dbName, resource_description, resource_associated_entity_id, request_mode as mode, request_status as Status from sys.dm_tran_locks --Result: /* 進程ID 資源類型 資料庫 資源描述 資源關鏈ID 鎖類型 進程狀態 ----------- ------------- ------ -------------------- ----------------------------- ----- ------ 59 DATABASE Gepro 0 S GRANT 58 DATABASE Gepro 0 S GRANT 57 DATABASE Gepro 0 S GRANT 56 DATABASE Gepro 0 S GRANT 58 PAGE Gepro 1:1904 72057594039435264 IS GRANT 57 PAGE Gepro 1:1904 72057594039435264 IX GRANT 58 OBJECT              Gepro 853578079 IS GRANT 57 OBJECT Gepro 853578079 IX GRANT 57 KEY Gepro (020068e8b274) 72057594039435264     X      GRANT 58 KEY Gepro (020068e8b274) 72057594039435264 S      WAIT (9 行受影響) */

 

-->SQL SERVER 2000查詢死結進程

代碼SELECT DISTINCT "進程ID" = STR(a.spid, 4) , "進程ID狀態" = CONVERT(CHAR(10), a.status) , "死結進程ID" = STR(a.blocked, 2) , "工作站名稱" = CONVERT(CHAR(10), a.hostname) , "執行命令的使用者" = CONVERT(CHAR(10), SUSER_NAME(a.uid)) , "資料庫名" = CONVERT(CHAR(10), DB_NAME(a.dbid)) , "應用程式名稱" = CONVERT(CHAR(10), a.program_name) , "正在執行的命令" = CONVERT(CHAR(16), a.cmd) , "登入名稱" = a.loginame , "執行語句" = b.text FROM master..sysprocesses a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b WHERE a.blocked IN ( SELECT blocked FROM master..sysprocesses ) -- and blocked <> 0 ORDER BY STR(spid, 4) --Result /* 進程ID  進程ID   狀態  死結進程ID  工作站名稱 執行命令的使用者 資料庫名 應用程式名稱 正在執行的命令 登入名稱 執行語句 ---- ---------- ------ ---------- ---------- ---------- ---------- ---------------- ---------------------------------------------------------------------- ------------------------- 56 sleeping 0 DC91229126 sa Gepro Microsoft AWAITING COMMAND DC91229126FF442\Administrator SET STATISTICS XML OFF 57 sleeping 0 DC91229126 sa Gepro Microsoft AWAITING COMMAND DC91229126FF442\Administrator SET STATISTICS XML OFF 58 suspended 57 DC91229126 sa Gepro Microsoft SELECT DC91229126FF442\Administrator begin tran select * from ta 59 runnable 0 DC91229126 sa Gepro Microsoft SELECT DC91229126FF442\Administrator SELECT DISTINCT 60 sleeping 0 DC91229126 sa Gepro Toad for S AWAITING COMMAND DC91229126FF442\Administrator SET FMTONLY OFF; 62 sleeping 0 DC91229126 sa Gepro Toad for S AWAITING COMMAND DC91229126FF442\Administrator */ --查串連住資訊(spid:57、58) select connect_time,last_read,last_write,most_recent_sql_handle from sys.dm_exec_connections where session_id in(57,58) --查看會話資訊 select login_time,host_name,program_name,login_name,last_request_start_time,last_request_end_time from sys.dm_exec_sessions where session_id in(57,58) --查看阻塞正在執行的請求 select session_id,blocking_session_id,wait_type,wait_time,wait_resource from sys.dm_exec_requests where blocking_session_id>0--正在阻塞請求的會話的 ID。如果此列是 NULL，則不會阻塞請求 /* session_id,blocking_session_id,wait_type,wait_time,wait_resource 58 57 LCK_M_S 2116437 KEY: 6:72057594039435264 (020068e8b274) */ --查看正在執行的SQL語句 select a.session_id,sql.text,a.most_recent_sql_handle from sys.dm_exec_connections a cross apply sys.dm_exec_sql_text(a.most_recent_sql_handle) as SQL --也可用函數fn_get_sql通過most_recent_sql_handle得到執行語句 where a.Session_id in(57,58) /* session_id text ----------- ----------------------------------------------- 57 SET STATISTICS XML OFF 58 begin tran select * from ta */

 

處理方法: 法一：

--串連視窗2 begin tran select * from ta with (nolock)--用nolock:業務資料不斷變化中,如****查看當月時可用。

 

法二： 阻塞2(索引): 處理方法: 加索引

代碼create index IX_Ta_Col1 on Ta(Col1)--用COl1列上創索引,當更新時條件:COl1=102會用到索引IX_Ta_Col1上得到一個排它鍵的範圍鎖 ----------------------------串連視窗1 ------------------------------------------------- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE --針對會話設定了 TRANSACTION ISOLATION LEVEL --SERIALIZABLE 幻影讀、不可重複讀取和髒讀都不允許 begin tran update ta set col2="BB" where COl1=102 --rollback tran -----------------------------串連視窗2------------------------------------------------ begin tran select * from ta

 

法三：設定當前查詢隔離等級

 

-----------------------------串連視窗2------------------------------------------------ SET TRANSACTION ISOLATION LEVEL READ COMMITTED --設定會話已提交讀:指定語句不能讀取已由其他事務修改但尚未提交的資料 begin tran select * from ta

 

1、事務要盡量短

 

查看死結犧牲品--查看死結犧牲品 SELECT  "進程ID[SPID]" = STR(a.spid, 4)   , "進程狀態" = CONVERT(CHAR(10), a.status)   , "分塊進程ID" = STR(a.blocked, 2)   , "伺服器名稱" = CONVERT(CHAR(10), a.hostname)   , "執行使用者" = CONVERT(CHAR(10), SUSER_NAME(a.uid))   , "資料庫名" = CONVERT(CHAR(10), DB_NAME(a.dbid))   , "應用程式名稱" = CONVERT(CHAR(10), a.program_name)   , "正在執行的命令" = CONVERT(CHAR(16), a.cmd)   , "累計CPU時間" = STR(a.cpu, 7)   , "IO" = STR(a.physical_io, 7)   , "登入名稱" = a.loginame   , "執行sql" = b.text FROM    master..sysprocesses a CROSS APPLY     sys.dm_exec_sql_text(a.sql_handle) b WHERE   blocked <> 0 ORDER BY spid

 

 

查看進程健全狀態--查看進程健全狀態    SELECT  "進程ID" = STR(spid, 4)       , "進程ID狀態" = CONVERT(CHAR(10), status)       , "分塊進程ID" = STR(blocked, 2)       , "工作站名稱" = CONVERT(CHAR(10), hostname)       , "執行使用者" = CONVERT(CHAR(10), SUSER_NAME(uid))       , "資料庫名" = CONVERT(CHAR(10), DB_NAME(dbid))       , "應用程式名稱" = CONVERT(CHAR(10), program_name)       , "正在執行的命令" = CONVERT(CHAR(16), cmd)       , "累計CPU時間" = STR(cpu, 7)       , "IO" = STR(physical_io, 7)       , "登入名稱" = loginame FROM    master..sysprocesses     --where blocked = 0 ORDER BY spid
--blocked = 0表示沒有阻塞的進程ID；

 

 

查詢鎖類型--查詢鎖類型 select 進程id=a.req_spid   ,資料庫=db_name(rsc_dbid)   ,類型=case rsc_type when 1 then "NULL 資源（未使用）"   when 2 then "資料庫"   when 3 then "檔案"   when 4 then "索引"   when 5 then "表"   when 6 then "頁"   when 7 then "鍵"   when 8 then "擴充盤區"   when 9 then "RID（行 ID)"   when 10 then "應用程式"   end   ,對象id=rsc_objid   ,對象名=b.obj_name   ,rsc_indid from master..syslockinfo a left join #t b on a.req_spid=b.req_spid

 

 

查看SA使用者執行的SQL----查看SA使用者執行的SQL SELECT  "進程ID[SPID]" = STR(a.spid, 4)   , "進程狀態" = CONVERT(CHAR(10), a.status)   , "分塊進程ID" = STR(a.blocked, 2)   , "伺服器名稱" = CONVERT(CHAR(10), a.hostname)   , "執行使用者" = CONVERT(CHAR(10), SUSER_NAME(a.uid))   , "資料庫名" = CONVERT(CHAR(10), DB_NAME(a.dbid))   , "應用程式名稱" = CONVERT(CHAR(10), a.program_name)   , "正在執行的命令" = CONVERT(CHAR(16), a.cmd)   , "累計CPU時間" = STR(a.cpu, 7)   , "IO" = STR(a.physical_io, 7)   , "登入名稱" = a.loginame   , "執行sql" = b.text FROM    master..sysprocesses a CROSS APPLY     sys.dm_exec_sql_text(a.sql_handle) b WHERE   blocked <> 0 OR a.loginame="sa" ORDER BY spid