SQL SERVER效能分析

標籤：

--阻塞   /***********************************************************************************************************************   阻塞：其中一個事務阻塞，其它事務等待對方釋放它們的鎖,同時會導致死結問題。     整理人：中國風(Roy)     日期: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、儘可能地使用低的交易隔離等級     go   阻塞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     --------------分析-----------------------   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   /*   spid        resource_type dbName resource_description resource_associated_entity_id mode  Status   ----------- ------------- ------ -------------------- ----------------------------- ----- ------   55          DATABASE      Test   0                    S                             GRANT NULL  54          DATABASE      Test   0                    S                             GRANT NULL  53          DATABASE      Test   0                    S                             GRANT NULL  55          PAGE          Test   1:201                72057594040483840             IS    GRANT  54          PAGE          Test   1:201                72057594040483840             IX    GRANT  55          OBJECT        Test   1774629365           IS                            GRANT NULL  54          OBJECT        Test   1774629365           IX                            GRANT NULL  54          KEY           Test   (020068e8b274)       72057594040483840             X     GRANT --(spID:54請求了排它鎖)   55          KEY           Test   (020068e8b274)       72057594040483840             S     WAIT  --(spID:55共用鎖定+等待狀態)   (9 行受影響)   */     --查串連住資訊(spid:54、55)   select connect_time,last_read,last_write,most_recent_sql_handle     from sys.dm_exec_connections where session_id in(54,55)     --查看會話資訊   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(54,55)     --查看阻塞正在執行的請求   select        session_id,blocking_session_id,wait_type,wait_time,wait_resource   from        sys.dm_exec_requests   where      blocking_session_id>0--正在阻塞請求的會話的 ID。如果此列是 NULL，則不會阻塞請求     --查看正在執行的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(54,55)   /*   session_id  text   ----------- -----------------------------------------------   54          begin tran   update ta set col2=‘BB‘ where ID=2   55          begin tran   select * from ta   */     處理方法:   --串連視窗2   begin tran       select * from ta with (nolock)--用nolock:業務資料不斷變化中,如銷售查看當月時可用。             阻塞2(索引):     -----------------------串連視窗1   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE    --針對會話設定了 TRANSACTION ISOLATION LEVEL   begin tran       update ta set col2=‘BB‘ where COl1=102     --rollback tran         ------------------------串連視窗2   insert into ta(ID,Col1,Col2) values(5,105,‘E‘)         處理方法:     create index IX_Ta_Col1 on Ta(Col1)--用COl1列上創索引,當更新時條件:COl1=102會用到索引IX_Ta_Col1上得到一個排它鍵的範圍鎖         阻塞3(會話設定):     -------------------------------串連視窗1     begin tran   --update       update ta set col2=‘BB‘ where ID=2       select col2 from ta where ID=2     --rollback tran     --------------------------------串連視窗2     SET TRANSACTION ISOLATION LEVEL READ COMMITTED --設定會話已提交讀:指定語句不能讀取已由其他事務修改但尚未提交的資料   begin tran       select * from ta          處理方法:   --------------------------------串連視窗2(善用會話設定:業務資料不斷變化中,如銷售查看當月時可用)     SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --設定會話未提交讀:指定語句可以讀取已由其他事務修改但尚未提交的行   begin tran       select * from ta   <pre></pre>

[轉自]：http://blog.csdn.net/roy_88/article/details/2682044
二、死結檢測資料庫阻塞語句

--查看死結情況SELECTDISTINCT‘進程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.textFROM master..sysprocesses a CROSS APPLYsys.dm_exec_sql_text(a.sql_handle) bWHERE a.blocked IN ( SELECT blockedFROM master..sysprocesses )-- and blocked <> 0ORDERBYSTR(spid, 4)--查串連住資訊(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) --查詢鎖類型select 進程id=a.req_spid  ,資料庫=db_name(rsc_dbid)  ,類型=case rsc_type when1then‘NULL 資源（未使用）‘  when2then‘資料庫‘  when3then‘檔案‘  when4then‘索引‘  when5then‘表‘  when6then‘頁‘  when7then‘鍵‘  when8then‘擴充盤區‘  when9then‘RID（行 ID)‘  when10then‘應用程式‘  end  ,對象id=rsc_objid  ,對象名=b.obj_name  ,rsc_indidfrom master..syslockinfo a leftjoin #t b on a.req_spid=b.req_spid ----查看SA使用者執行的SQLSELECT  ‘進程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.textFROM    master..sysprocesses a CROSS APPLY    sys.dm_exec_sql_text(a.sql_handle) bWHERE   blocked <>0OR a.loginame=‘sa‘ORDERBY spid主要動態管理檢視：sys.sysprocesses（相容sql2k）sys.dm_exec_connectionssys.dm_exec_sessionssys.dm_exec_requests 來源：http://www.cnblogs.com/ilovexiao/archive/2010/05/21/1740645.html

 

SQL SERVER效能分析