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.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
ORDERBYSTR(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_indid
from master..syslockinfo a leftjoin #t b on a.req_spid=b.req_spid

 

----查看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 <>0OR a.loginame='sa'
ORDERBY spid

主要动态管理视图：

sys.sysprocesses（兼容sql2k）

sys.dm_exec_connections

sys.dm_exec_sessions

sys.dm_exec_requests

 

来源：http://www.cnblogs.com/ilovexiao/archive/2010/05/21/1740645.html