什么原因会发生阻塞的现象

[AI1983]

基本情况：
1、服务器：system X3400
2、操作系统：windows server2003
3、应用程序：SQL server 标准版
4、RAID：两块SAS硬盘组成的RAID 1，4G的内存

现在SQL的响应时间时快时慢，慢的时候在企业管理器-》管理-》当前活动-》锁/进程ID中会发现图标变为红色显示正被那个进程阻塞，而利用SQL性能查看器会发现Disk Time和Avg Disk Queue Length两项的值非常高，有时会持续在峰值上走动。

我想请教一下各位到底都有那些原因会导致阻塞，是SQL语句结构的问题还是和I/O的瓶颈有关系呢

[dawugui]

大多数情况下是死锁.

[dawugui]

参考这个:

分析阻塞并处理

--阻塞      
/***********************************************************************************************************************      
阻塞：其中一个事务阻塞，其它事务等待对方释放它们的锁,同时会导致死锁问题。      
     
整理人：中国风(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      

[牙签是竹子的]

[AI1983]

上边的那个阻塞的脚本怎么用的，sys.dm_tran_locks找不到对象啊
有没有人说的详细点啊，谢谢！

[AI1983]

谢谢楼上的兄弟
不过我用地是SQL server 2000

[loveeqing]

[Quote=引用 4 楼 AI1983 的回复:]
上边的那个阻塞的脚本怎么用的，sys.dm_tran_locks找不到对象啊
有没有人说的详细点啊，谢谢！
[/Quote]

应该是ms sql2005的测试数据吧

[orochi_gao]

lz你的情况都有可能
1:sql 察看阻塞语句
--阻塞会话信息
select blocking_session_id, wait_duration_ms, session_id from
sys.dm_os_waiting_tasks
where blocking_session_id is not null
go
--查看阻塞语句
dbcc INPUTBUFFER(blocking_session_id)
2:数据文件放在RAID 1上实际I/O输出只是单盘的一半.raid 1下放系统数据库合适因为安全性高。建议数据文件放在raid 5上，安全性也可以，但I/O性能比raid 1要高！