3522vip-澳门新葡亰平台官网-www.3522vip.com

3522vip☞(www.rivieraquest.com)能够为大家带来真正的真钱享受,澳门新葡亰平台官网开创业内先河,注册,开户,登录开始体验不同的娱乐世界,全国第一家以娱乐产品为主体对象的专业平台,菲律宾全资子公司成立,天天免费68周周再送168。

3522vip > 网络数据库 > SQL Server 2008中SQL应用之-“阻塞(Blocking)”

原标题:SQL Server 2008中SQL应用之-“阻塞(Blocking)”

浏览次数:151 时间:2019-12-28

我知道SQL Server有很多视图和函数让我来了解SQL Server的运行状态.我还想知道SQL Server上关于来自用户或者应用的活动请求信息.怎么查询这些信息呢?

 SQL Server 2008中SQL应用系列--目录索引

 SQL Server 2008中SQL应用系列--目录索引

SQL Server的动态管理视图DMV sys.dm_exec_requests可以实现.但是它不仅仅显示了来自连接用户或应用的请求.比如,它还显示了SQL Server有非常多的后台任务.比如下面的简单查询:

无论是有意无意,如果事务在数据库中保持打开,则它会阻塞其他进程对修改后的数据进行操作。同样,对事务日志进行备份也只会截断不活动事务的那部分事务日志,所以打开的事务会导致日志变多(甚至达到物理限制),直到事务被提交或回滚。

   

要找到最早的活动事务,可以使用DBCC OPENTRAN命令。详细用法见MSDN:

当一个数据库会话中的事务正锁定一个或多个其他会话事务想要读取或修改的资源时,会产生阻塞(Blocking)。通常短时间的阻塞没有问题,且是较忙的应用程序所需要的。然而,设计糟糕的应用程序会导致长时间的阻塞,这就不必要地锁定了资源,而且阻塞了其他会话读取和更新它们。

select session_id,start_time,command
from sys.dm_exec_requests
where status='background';

给出一个示例:

在SQL Server中,一个阻塞的进程会无限期地保持阻塞,或者直到它超时(根据set lock_timeout)、服务器关闭、进程被杀死、连接完成了更新或者其他发生在原始事务上的操作导致它释放了资源上的锁。

 

CREATE TABLE T_Product(PKID int, PName Nvarchar(50));
GO

BEGIN TRAN
INSERT INTO T_Product VALUES (101, '嫦娥四号');
GO
DBCC OPENTRAN;
ROLLBACK TRAN;
GO
DROP TABLE T_Product;
GO

发生长时间阻塞的原因如下:

   

执行结果:

1、在一个没有索引的表上的过量的行锁会导致SQL Server得到一个锁,从而阻塞其他事务。

这是一个很简单的例子,在我的测试机上返回了20多个不同的会话.

/*
(1 row(s) affected)
数据库 'Testdb' 的事务信息。

最早的活动事务:
    SPID (服务器进程 ID): 54
    UID (用户 ID): -1
    名称          : user_transaction
    LSN           : (295:6687:1)
    开始时间    : 12 24 2010  2:50:15:607PM
    SID           : 0x0105000000000005150000007fe010d31cba1ab1566ac5dff4010000
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
*/

2、应用程序打开一个事务,并在事务保持打开的时候要求用户进行反馈或交互。这通常是让最终用户在GUI上输入数据而保持事务打开的时候发生。此时,事务引用的任何资源都会被占据。

www.3522vip.com 1

结果显示了最早活动日志的相关信息,包括服务器进程ID、用户ID、和事务的开始时间。关键是SPID和Start Time。
拥有这些信息后,可以使用动态管理视图(DMV)来检验正在执行的T-SQL,以及在必要时关闭这个过程
DBCC OPENTRAN对于孤立连接(在数据库中是打开的,但与应用程序或客户端已经断开的连接)是非常有用的,并能帮助我们找出遗漏了COMMIT或ROLLBACK的事务。该命令也返回在指定数据库内存在最早的活动事务和最早的分布式和非分布式复制事务。如果没有活动事务,则显示信息性消息,而不返回会话级数据。

3、事务BEGIN后查询的数据可能在事务事务开始前被调用

   

我们看一个实例:

4、查询不恰当地使用锁定提示。例如,应用程序仅使用很少的行,但却使用一个表锁提示

不过,通常我们是使用DMV来对活动会话进行故障排除.最先我们需要做的就是看哪些会话在经理等待.

SET Transaction  isolation level serializable
BEGIN TRAN

select * from T_Product

Insert into T_Product 
select 'OATest' union all
select 'OAPlay'

5、应用程序使用长时间运行的事务,在一个事务中更新了很多行或很多表(把一个大量更新的事务变成多个更新较少的事务有助于改善并发性)

   

这是一个未提交的事务,在另一个查询窗口执行如下:

一、找到并解决阻塞进程

select session_id,blocking_session_id,start_time,wait_type
from sys.dm_exec_requests
where blocking_session_id >0;  
select session_id,transaction_id,is_user_transaction,is_local 
from sys.dm_tran_session_transactions
where is_user_transaction=1

下面我们演示使用SQL Server动态管理视图sys.dm_os_waiting_tasks找出阻塞进程,该视图用于代替早期SQL Server版本中的系统存储过程sp_who

 

执行结果:

找出阻塞的进程后,我们使用sys.dm_exec_sql_text动态管理函数和sys.dm_exec_Connections(DMV)找出正在执行的查询的SQL文本,然后强制结束进程。

我们可以使用下面的2中方法确定查询是什么,以及是什么导致了阻塞:

/*返回结果
session_id    transaction_id    is_user_transaction    is_local
54    489743    1    1
*/

强制结束进程,我们使用kill命令。kill的用法,请参看MSDN:

1.如果有活动请求,我们可以使用sys.dm_exec_requests 和sys_dm_exec_sql_text(),然后把sql_handle作为参数传进去.

返回会话ID后,可以通过sys.dm_exec_connections和sys.dm_exec_sql_text来挖掘最近执行的查询的详细信息。

该命令有三个参数:

2.如果没有活动的请求,我们可以连接sys.dm_exec_commections 然后传递most_recent_sql_handle到sys.dm_exec_sql_text().

select s.text from sys.dm_exec_connections c 
cross apply sys.dm_exec_sql_text(c.most_recent_sql_Handle) s 
where session_id=54

session ID    要终止的进程的会话 ID。session ID 是在建立连接时为每个用户连接分配的唯一整数 (int)。在连接期间,会话 ID 值与该连接捆绑在一起。连接结束时,则释放该整数值,并且可以将它重新分配给新的连接。使用 KILL session ID 可终止与指定的会话 ID 关联的常规非分布式事务和分布式事务。
UOW    标识分布式事务的工作单元 (UOW) ID。UOW 是可从 sys.dm_tran_locks 动态管理视图的 request_owner_guid 列中获取的 GUID。也可从错误日志中或通过 MS DTC 监视器获取 UOW。有关监视分布式事务的详细信息,请参阅 MS DTC 文档。使用 KILL UOW 可终止孤立的分布式事务。这些事务不与任何真实的会话 ID 相关联,与虚拟的会话 ID = '-2' 相关联。可使标识孤立事务变得更为简单,其方法是查询 sys.dm_tran_locks、sys.dm_exec_sessions 或 sys.dm_exec_requests 动态管理视图中的会话 ID 列。
WITH STATUSONLY    生成由于更早的 KILL 语句而正在回滚的指定 session ID 或 UOW 的进度报告。KILL WITH STATUSONLY 不终止或回滚 session ID 或 UOW,该命令只显示当前的回滚进度。

   

这个查询返回最后执行的语句。也可以使用sys.dm_exec_requests。

在第一个查询窗口:

在这个例子中,我知道spid=53会话没有活动的请求,因为我查了sys.dm_exec_requests.我们再回过头来看看第二种方法.

www.3522vip.com,因为也从sys.dm_tran_session_transactions的第一个查询中得知事务ID,所以可以使用sys.dm_tran_active_transactions来了解更多事务本身的内容 

BEGIN TRAN
UPDATE Production.ProductInventory
SET Quantity = 400
WHERE ProductID = 1 AND
LocationID = 1

   

select transaction_begin_time,
case transaction_type 
    when 1 then 'Read/Write transaction'
    when 2 then 'Read-Only transaction'
    when 3 then 'System transaction'
    when 4 then 'Distributed transaction'
    end tran_Type,
case transaction_state
    when 0 then  'not been comoletely initaialiaed yet'
    when 1 then  'initaialiaed but ha notstarted'
    when 2 then  'active'
    when 3 then  'ended (read-only transaction)'
    when 4 then  'commit initiated for distributed transaction'
    when 5 then  'transaction prepared and waiting resolution'
    when 6 then  'commited'
    when 7 then  'being rolled back'
    when 0 then  'been rolled back'
    end transaction_state
 from 
sys.dm_tran_active_transactions
where transaction_ID=455520

/*结果:
transaction_begin_time    tran_Type    transaction_state
2010-12-24 14:05:29.170    Read/Write transaction    active
*/

第二个窗口:

select distinct des.session_id,dst.text as 'SQL'
from sys.dm_exec_requests as DER
join sys.dm_exec_connections as DEC
on DER.blocking_session_id=DEC.session_id
cross apply sys.dm_exec_sql_text(DEC.most_recent_sql_handle) as DST;

小结:这里演示了使用DMV 排除故障和调查长时间的活动事务的一般技巧。基本步骤如下:
1、查询sys.dm_tran_session_transactions获取会话ID和事务ID之间的映射。
2、查询sys.dm_exec_connections和sys.dm_exec_sql_text查找会话最新执行的命令(most_recent_sql_Handle列)
3、最后,查询sys.dm_tran_active_transactions确定事务被打开了多少时间、事务的类型和事务的状态。
使用这个技巧可以回到应用程序去查明调用的被抛弃的事务(打开但从未提交)以及那些运行时间太长或对于应用程序来说是不必要的不恰当事务。

UPDATE Production.ProductInventory
SET Quantity = 406
WHERE ProductID = 1 AND
LocationID = 1

 

第三个窗口:

 然后我们就发现下面的请求返回了

SELECT blocking_session_id, wait_duration_ms, session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL

/*
blocking_session_id    wait_duration_ms    session_id
52    23876    54
*/

   

澳门新葡亰平台官网,可以看出是SessionID为52的会话阻塞了SessionID为54的会话。

www.3522vip.com 2

那么,52正在干啥坏事呢?在第三个窗口中执行:

   

SELECT t.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t
WHERE c.session_id = 54

/*
text
(@1 int,@2 tinyint,@3 tinyint)UPDATE [Production].[ProductInventory] set [Quantity] = @1  WHERE 
[ProductID]=@2 AND [LocationID]=@3
*/

这看起来是一个没有问题的查询,只是简单的插入,所有我们还应该更深入的看看.这时我们应该看看是否有开启的事务,如果它有活动的请求,我们可以在sys.dm_exec_requests的open_transaction_count列看到.我们这里没有看到活动请求,我们可以看看sys.dm_exec_sessions:

注意:这并不是第一个查询窗口中的原SQL语句,SQL Server进行了自动参数化计划缓存(预编译)。

   

我们强制终止会话。在第三个窗口中执行:

select session_id,open_transaction_scount
from sys_dm_exec_sessions
where open_transaction_count >0;
kill 52 

 

注意:窗口一的语句和窗口二的语句均终止。

   

提示:第三个语句中,使用sys.dm_exec_connections(DMV)返回了Session ID为53的most_recent_sql_handle列。这是SQL文本在内存中的指针。作为sys.dm_exec_sql_text动态管理函数的输入参数使用。从sys.dm_exec_sql_text返回了text列,该列显示了阻塞进程的SQL文本。如果阻塞成串,必须通过blocking_session_id和session_ID列仔细查看每一个阻塞进程,直到发现原始的阻塞进程。

我们看到了下面打开的事务,可能是随忘了提交事务.

二、配置语句等待锁释放的时长

   

如果有一个事务或语句被阻塞,意味着它在等待资源上的锁被释放。我们可以事先通过set lock_Timeout来设定需要等待的时间。

www.3522vip.com 3

语法如下:SET LOCK_TIMEOUT time_period

   

参数以毫秒为单位。超过时会返回锁定错误。示例:

获取活动的查询计划

在第一个窗口中执行:

如果有查询运行时间非常长,我们就需要看看查询计划了解为什么它会花这么长时间.有可能这个查询计划有问题. 下面的查询可以返回任何活动查询的查询计划:

USE AdventureWorks
BEGIN TRAN
UPDATE Production.ProductInventory
SET Quantity = 400
WHERE ProductID = 1 AND
LocationID = 1
select DER.session_id,DEQP.query_plan
from sys.dm_exec_requests as DER
cross apply sys.dm_exec_query_plan(DER.plan_handle) as DEQP
where not DER.status in ('background','sleeping');

在第二个窗口中执行:

 

USE AdventureWorks
SET LOCK_TIMEOUT 1000
UPDATE Production.ProductInventory
SET Quantity = 406
WHERE ProductID = 1 AND
LocationID = 1

/*
1秒后的执行结果
Msg 1222, Level 16, State 51, Line 3
Lock request time out period exceeded.
The statement has been terminated.
*/

   

解析:在这个示例中,我们设置了锁超时时间为1000毫秒,即1秒。这个设置不会影响资源被进程占有的时间,只会影响等待另一个进程释放资源访问的时间。

注:sys.dm_exec_query_plan是一个表值函数,它接收cross apply左侧的表传递的参数,每行记录计算一次,生成一个新表,然后与左表内连接. 下面链接解释的比较详细.

https://www.cnblogs.com/xbf321/archive/2011/08/14/apply-in-sql-server.html

   

cross apply更详细的解释,3种使用情况:

   

我们查到有下面的2条活动请求的查询计划:

www.3522vip.com 4

   

这里我在52号session中执行我们的查询,因此我们看看53号session. 如果使用SQL Server management studio的话,我们只需要点击查询计划的XML就可以可视化的查看查询计划.

   

www.3522vip.com 5

   

获取活动查询的完成百分比

我们能从sys.dm_exec_requests中找到的非常有用一列信息是"完成百分比".比如,我想知道DBCC check现在执行到哪里了,我们基于它执行一个简单的查询获取所需的信息. 我们知道它是它是DBCC TABLE CHECK,下面是我的查询子句:

   

select session_id,start_time,status,database_id,percent_complete
from sys.dm_exec_requests
where command='DBCC TABLE CHECK';

 

我们看到现在完成了11%

   

www.3522vip.com 6

   

很显然,这可以用来检查长查询的执行情况.

   

对指定的数据库获取所有活动请求

   

很多时候我们希望获取某一数据库上执行的所有操作.我们也可以是使用sys.dm_exec_requests来查询.这里我们连接sys.database使用数据库名来过滤.如果你已经知道数据库ID,你就不需要做这个join.你也可以使用DB_ID()这个函数,用来把数据库名翻译成数据库ID.然后,我还想知道谁连接了数据库,它是怎么连接的(使用什么应用连接的),我还需要连接sys.dm_exec_session.下面是我的查询,使用数据库名Test作为过滤条件.

select DER.session_id,DES.login_name,DES.program_name
from sys.dm_exec_requests as DER
join sys.databases as DB
on DER.database_id=DB.database_id
join sys.dm_exec_sessions as DES
on DER.session_id=DES.session_id
where DB.name='Test';

   

当我们执行这个查询的时候,我们可以获得下面2条活动会话:

www.3522vip.com 7

   

因为这是针对sys.dm_exec_requests DMV的,我们知道这是针对Test数据库的.如果我们尝试针对特定数据库进行性能故障排除,这是一个好的突破方向.很显然,我们可以结合这个查询和上个查询获取实际的查询计划.

   

   

查看所有活动等待事件计数信息

   

有些时候我们诊断一个问题是,我们需要查询所有等待类型情况.我们也可以使用sys.dm_exec_requests,因为这个视图也显示了当前等待类型. 因此我们过滤掉后台任务或者sleeping任务时,我们可以了解到这些活动请求的等待情况,看看是否有什么问题.下面是查询:

select coalesce(wait_type,'None') as wait_type,count(*) as Total
from sys.dm_exec_requests
where not status in('Background','Sleeping')
group by wait_type
order by Total DESC;

 

下面是查询结果:

www.3522vip.com 8

   

   

我们可以看到我们有2个LCK_M_S这种等待类型.这种等待类型是当我们等待获取共享锁时发生的等待.然后我们可以继续查询sys.dm_tran_locks来确定具体这个请求尝试获取的锁是什么.

   

select L.request_session_id,L.resource_type,
L.resource_subtype,L.request_mode,L.request_type
from sys.dm_tran_locks as L
join sys.dm_exec_requests as DER
on L.request_session_id=DER.session_id
where DER.wait_type='LCK_M_S';

 

   

然后我们获取到了这2个会话的全部信息列表:

www.3522vip.com 9

   

故障排除方面我们还可以做更多,但是到此为止我们已经了解到了sys.dm_exec_requests的强大.

本文由3522vip发布于网络数据库,转载请注明出处:SQL Server 2008中SQL应用之-“阻塞(Blocking)”

关键词: 3522vip

上一篇:多表查询

下一篇:没有了