1. 阻塞
除了内存、CPU、I/O这些系统资源以外,阻塞和死锁是影响数据库应用性能的另一大因素。
所谓的「阻塞」,是指当一个数据库会话中的事务,正在锁定其他会话事务想要读取或修改的资源,造成这些会话发出的请求进入等待的状态。SQL Server 默认会让被阻塞的请求无限期地一直等待,直到原来的事务释放相关的锁,或直到它超时、服务器关闭、进程被杀死。一般的系统中,偶尔有短时间的阻塞是正常且合理的;但若设计不良的程序,就可能导致长时间的阻塞,这样就不必要地锁定了资源,而且阻塞了其他会话欲读取或更新的需求。遇到这种情况,可能就需要手工排除阻塞的状态。
2.阻塞和死锁可能带来的问题
(1)并发用户少的时候,一切还都正常。但是随着并发用户的增加,性能越来越慢。
(2)应用程序运行很慢,但是SQL Server 这个CPU和磁盘利用率很低。
(3)客户端经常受到以下错误。
Error 1222--Lock request time out period exceeded.
Error 1205--Your transaction(process ID #XX) was deadlocked on resources with another process and has been chosen as the deadlock victim. Return your transaction.
超时错误--Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
(4)有些查询能够进行,但是有些特定的查询或修改总是不能返回。
(5)重启SQL Server就能解决。但是有可能跑一段时间以后又会出现问题。
3.阻塞的检查
3.1 主要的系统表或函数
Sys.SysProcesses 系统表是一个很重要的系统视图,主要用来定位与解决Sql Server的阻塞和死锁。主要字段1.Spid:Sql Servr 会话ID 2.Blocked:正在阻塞求情的会话 ID。如果此列为 Null,则标识请求未被阻塞 3. Program_name:应用程序的名称,就是 连接字符串中配的 Application Name 4. Hostname:建立链接的客户端工作站的名称。
sys.dm_exec_requests、sys.dm_exec_sql_text返回指定SPIDer的 SQL 查询文本。
DBCC INPUTBUFFER 显示从客户端发送到 Microsoft SQL Server 实例的最后一个语句。
sp_lock 系统存储过程,报告有关锁的信息。
3.2 Check逻辑
对应的存储为dblockcheck(job为DB_Lockcheck),主要Check逻辑如下:
3.3 保存的数据
所收集的数据保存dblock_information中,主要包含信息如截图,定期的统计分析可获得经常被阻塞和引起阻塞SQL语句和Table,这些信息是进行数据库优化的一个角度。
select top 100* from dblock_information
order by TransDateTime desc
4.代码实现
4.1 Table的创建脚本
CREATE TABLE [dbo].[dblock_information](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Message] [nvarchar](300) NULL,
[LockType] [char](1) NULL,
[SPID1] [int] NULL,
[SPID2] [int] NULL,
[EventType] [nvarchar](100) NULL,
[Parameters] [nvarchar](10) NULL,
[EventInfo] [nvarchar](3000) NULL,
[IndividualQuery] [nvarchar](1000) NULL,
[TransDateTime] [datetime] NULL CONSTRAINT [DF_dblock_information_TransDateTime] DEFAULT (getdate()),
[AppName] [varchar](50) NULL,
[HostName] [varchar](50) NULL,
CONSTRAINT [PK_dblock_information] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
4.2 存储的创建脚本
Create procedure [dbo].[dblockcheck]
@AlarmThreshold as tinyint=10
as
set nocount on
--------------------------------------------------------------------------------------------------------
--*Program*: <dblock check for job>
--*Programer*:<>
--*Date*:<>
--*Description*:<Query SQL Locking Process>
--*Unify*:<UA>
--########## Parameter Description Begin ##########
--########## Parameter Description End # ##########
--##########Update Log Begin ###################
--##########Update Log End # ###################
--------------------------------------------------------------------------------------------------------
declare @SQL as varchar(200)
declare @Subject as varchar(200)
declare @Body as nvarchar(max)
declare @SPName as nvarchar(max)
declare @Message as nvarchar(200)
declare @DBname varchar(15)
declare @IP varchar(20)
declare @CNT as int
declare @cnt2 int
declare @IndividualQuery nvarchar(1000)
declare @HostName varchar(50)
declare @AppName varchar(50)
SET @DBname=DB_NAME()
SELECT @IP='XXX.XXX.XXX.XXX'
----不手动定义IP也可通过以下函数来实现
Declare @ServerIP NVARCHAR(30)='', @SERVERNAME NVARCHAR(60)=''
SELECT top 1 @SERVERNAME = @@SERVERNAME ,@ServerIP=LOCAL_NET_ADDRESS
FROM SYS.DM_EXEC_CONNECTIONS where LOCAL_NET_ADDRESS is not null
--------
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int
create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
create table #tmp_lock_information (
id int identity(1,1),
Message nvarchar(200),
LockType char(1),
SPID1 int,
SPID2 int,
EventType nvarchar(100),
Parameters nvarchar(10),
EventInfo nvarchar(max),
IndividualQuery nvarchar(1000),
AppName varchar(50),
HostName varchar(50)
)
IF @@ERROR<>0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl)
select 0 ,blocked
from (select * from master..sysprocesses where blocked>0 ) a
where not exists(select * from (select * from master..sysprocesses where blocked>0 ) b
where a.blocked=spid)
union
select spid,blocked from master..sysprocesses where blocked>0
IF @@ERROR<>0 RETURN @@ERROR
-- 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if @intCountProperties=0
select N'现在没有阻塞信息!' as message
-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
SELECT @IndividualQuery= SUBSTRING (qr.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qr.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
FROM SYS.DM_EXEC_REQUESTS qs OUTER APPLY SYS.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qr
WHERE qr.text is not null and qs.session_id=@bl
select @HostName=left(HostName,50),@AppName=Left(Program_Name,50)
from master..sysprocesses With(nolock) Where SPID=@bl
set @SQL='DBCC INPUTBUFFER ('+cast(@bl as char(20))+')'
if @spid =0
begin
select @Message=N'引起数据库阻塞的是: '+ CAST(@bl AS NVARCHAR(100)) + N'进程号,其执行的SQL语法如下'
--set @SQL='DBCC INPUTBUFFER ('+cast(@bl as char(20))+')'
insert into #tmp_lock_information(EventType,Parameters,EventInfo) exec(@sql)
update #tmp_lock_information set LockType='1',SPID1=@bl,SPID2=@bl,IndividualQuery=@IndividualQuery,[Message]=@Message,AppName=@APPName,HostName=@HostName where [Message] is null
end
else
begin
select @Message=N'进程号SPID:'+ CAST(@spid AS NVARCHAR(100))+ N'被' + N'进程号SPID:'+ CAST(@bl AS NVARCHAR(10)) +N'阻塞,其当前进程执行的SQL语法如下'
insert into #tmp_lock_information(EventType,Parameters,EventInfo) exec(@sql)
update #tmp_lock_information set LockType='2', SPID1=@spid,SPID2=@bl,IndividualQuery=@IndividualQuery,[Message]=@Message,AppName=@APPName,HostName=@HostName where [Message] is null
end
end
-- 循环指针下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who
if exists(select 0 from #tmp_lock_information)
Begin
Insert into dblock_information(Message,LockType,SPID1,SPID2,EventType,Parameters,EventInfo,IndividualQuery,AppName,HostName)
Select [Message],LockType,SPID1,SPID2,EventType,Parameters,Substring(EventInfo,1,500),IndividualQuery,AppName,HostName from #tmp_lock_information
End
drop table #tmp_lock_information
return 0
end