--sql server dead lock (学习与研究) sql server 2008 r2
--http://mahuidong0222.blog.163.com/blog/static/1044585200911225318604/(引用)
exec sp_configure
DBCC TRACEON (1222,-1)
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 4;
GO
RECONFIGURE WITH OVERRIDE;
GO
--1 create test table
CREATE TABLE [dbo].[LockTest](
[ID] [int] NULL,
[Name] [nvarchar](10) NULL
) ON [PRIMARY]
GO
--2 ecec proc
DBCC TRACEon (1222,-1)
--3 run sql on two windows
set transaction isolation level serializable
declare @count int
declare @tranname varchar(10)
set @count =1
while(@count <10)
begin
set @tranname='a'+convert(varchar,@count)
begin tran @tranname
update dbo.LockTest set Name ='aaaa' where ID =1;
waitfor delay '00:00:02'
commit tran @tranname
set @count =@count +1
end
set transaction isolation level serializable
declare @count int
declare @tranname varchar(10)
set @count =1
while(@count <10)
begin
set @tranname='B'+convert(varchar,@count)
begin tran @tranname
update dbo.LockTest set Name ='BBBB' where ID =2;
waitfor delay '00:00:02'
commit tran @tranname
set @count =@count +1
end
--4 check log and sql server profil
--5 trace off
DBCC TRACEoff (1222,-1)
--============================================================
DBCC TRACESTATUS
exec sp_who_lock
--每秒死锁数量---------------------------------------------------
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Number of Deadlo%';
--查询当前阻塞---------------------------------------------------
WITH CTE_SID ( BSID, SID, sql_handle )
AS ( SELECT blocking_session_id ,
session_id ,
sql_handle
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
UNION ALL
SELECT A.blocking_session_id ,
A.session_id ,
A.sql_handle
FROM sys.dm_exec_requests A
JOIN CTE_SID B ON A.SESSION_ID = B.BSID
)
SELECT C.BSID ,
C.SID ,
S.login_name ,
S.host_name ,
S.status ,
S.cpu_time ,
S.memory_usage ,
S.last_request_start_time ,
S.last_request_end_time ,
S.logical_reads ,
S.row_count ,
q.text
FROM CTE_SID C
JOIN sys.dm_exec_sessions S ON C.sid = s.session_id
CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q
ORDER BY sid
--历史死锁查询(确定开启才可以)
SELECT REPLACE(REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),
'<victim-list>',
'<deadlock><victim-list>'),
'<process-list>',
'</victim-list><process-list>')
FROM (select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s
on s.address = st.event_session_address
where name = 'system_health' ) AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
--查询所有的死锁信息,当然如果出现内存瓶颈的时候能保存多久我不确定,如果死锁太多你无法顺利的找到,你想把结果减少一点,可以在每次查询死锁后使用:
ALTER EVENT SESSION system_health ON SERVER STATE = stop
go
ALTER EVENT SESSION system_health ON SERVER STATE = start
--保存成csv文件
<deadlock-list>
<deadlock><victim-list>
<victimProcess id="process5c3b708"/>
</victim-list><process-list>
<process id="process5c3b708" taskpriority="0" logused="0" waitresource="OBJECT: 18:1663344990:0 " waittime="4929" ownerId="7825618" transactionname="B3" lasttranstarted="2013-09-26T09:29:33.557" XDES="0xd77dd950" lockMode="X" schedulerid="6" kpid="5340" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-09-26T09:29:29.533" lastbatchcompleted="2013-09-26T09:22:09.440" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="6O4NFQJ7YA5LWIQ" hostpid="13612" loginname="sa" isolationlevel="serializable (4)" xactid="7825618" currentdb="18" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="" line="9" stmtstart="58" sqlhandle="0x02000000d09e0e03e6960a6056b81deeff2192c0efe3b1da">
</frame>
<frame procname="" line="9" stmtstart="420" stmtend="528" sqlhandle="0x020000004059cc11f483bbf741897850dcf590a2b292ddf4">
</frame>
</executionStack>
<inputbuf>
set transaction isolation level serializable
declare @count int
declare @tranname varchar(10)
set @count =1
while(@count <10)
begin
set @tranname='B'+convert(varchar @count)
begin tran @tranname
update dbo.LockTest set Name ='BBBB' where ID =2;
waitfor delay '00:00:02'
commit tran @tranname
set @count =@count +1
end </inputbuf>
</process>
<process id="process5c1d708" taskpriority="0" logused="0" waitresource="OBJECT: 18:1663344990:0 " waittime="4929" ownerId="7825587" transactionname="a1" lasttranstarted="2013-09-26T09:29:31.883" XDES="0x802f1250" lockMode="X" schedulerid="3" kpid="5384" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-09-26T09:29:31.883" lastbatchcompleted="2013-09-26T08:54:14.807" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="6O4NFQJ7YA5LWIQ" hostpid="13612" loginname="sa" isolationlevel="serializable (4)" xactid="7825587" currentdb="18" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="" line="9" stmtstart="58" sqlhandle="0x02000000d09e0e03e6960a6056b81deeff2192c0efe3b1da">
</frame>
<frame procname="" line="9" stmtstart="420" stmtend="528" sqlhandle="0x0200000039181934c682aaaf76581b314c058e1adc6716c7">
</frame>
</executionStack>
<inputbuf>
set transaction isolation level serializable
declare @count int
declare @tranname varchar(10)
set @count =1
while(@count <10)
begin
set @tranname='a'+convert(varchar @count)
begin tran @tranname
update dbo.LockTest set Name ='aaaa' where ID =1;
waitfor delay '00:00:02'
commit tran @tranname
set @count =@count +1
end </inputbuf>
</process>
</process-list>
<resource-list>
<objectlock lockPartition="0" objid="1663344990" subresource="FULL" dbid="18" objectname="" id="lock1d17d8680" mode="IX" associatedObjectId="1663344990">
<owner-list>
<owner id="process5c1d708" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process5c3b708" mode="X" requestType="convert"/>
</waiter-list>
</objectlock>
<objectlock lockPartition="0" objid="1663344990" subresource="FULL" dbid="18" objectname="" id="lock1d17d8680" mode="IX" associatedObjectId="1663344990">
<owner-list>
<owner id="process5c3b708" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process5c1d708" mode="X" requestType="convert"/>
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
</deadlock-list>
--http://www.111cn.net/database/mssqlserver/43563.htm(学习与分析死锁)
--语句深入与学习
--1
SELECT REPLACE(REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),
'<victim-list>',
'<deadlock><victim-list>'),
'<process-list>',
'</victim-list><process-list>')
FROM (select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s
on s.address = st.event_session_address
where name = 'system_health' ) AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
--2
select *
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s
on s.address = st.event_session_address
-- where name = 'system_health
--http://blog.csdn.net/kevinsqlserver/article/details/7978036[SQL Server Extended Events使用1: 查询运行时间久的SQL语句]
SELECT*FROM sys.server_event_sessions
--首先我们来看一下目前数据库系统所有请求情况======================================常用语句总结
select s.session_id, s.status,db_name(r.database_id) as database_name,
s.login_name,s.login_time, s.host_name,
c.client_net_address,c.client_tcp_port,s.program_name,
r.cpu_time, r.reads, r.writes,c.num_reads,c.num_writes,
s.client_interface_name,
s.last_request_start_time, s.last_request_end_time,
c.connect_time, c.net_transport, c.net_packet_size,
r.start_time, r.status, r.command,
r.blocking_session_id, r.wait_type,
r.wait_time, r.last_wait_type, r.wait_resource, r.open_transaction_count,
r.percent_complete,r.granted_query_memory
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50
order by s.session_id
2. 哪个用户连接数最
--request info by user
select login_name,COUNT(0) user_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50
group by login_name
order by 2 desc
3. 哪台机器发起到数据库的连接数最多:
--request info by hostname
select s.host_name,c.client_net_address,COUNT(0) host_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50
group by host_name,client_net_address
order by 3 desc
4. 这些连接在访问哪个库
--request info by databases
select db_name(r.database_id) as database_name,COUNT(0) host_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50
group by r.database_id
order by 2 desc
select s.status,COUNT(0) host_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50
group by s.status
order by 2 desc
--1. 查看数据库阻塞情况==================================================================
----------------------------------------Blocked Info----------------------------------
--记录当前阻塞信息
select t1.resource_type as [lock type] ,db_name(resource_database_id) as [database]
,t1.resource_associated_entity_id as [blk object]
,t1.request_mode as [lock req] -- lock requested
,t1.request_session_id as [waiter sid] -- spid of waiter
,t2.wait_duration_ms as [wait time]
,(select text from sys.dm_exec_requests as r with(nolock) --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as waiter_batch
,(select substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2+1)
from sys.dm_exec_requests as r with(nolock)
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as waiter_stmt --- statement executing now
,t2.blocking_session_id as [blocker sid] --- spid of blocker
,(select text from sys.sysprocesses as p with(nolock) --- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as blocker_stmt,getdate() time
from sys.dm_tran_locks as t1 with(nolock) , sys.dm_os_waiting_tasks as t2 with(nolock)
where t1.lock_owner_address = t2.resource_address;
--2. 查看阻塞其他进程的进程(阻塞源头):====================================================
--阻塞其他session的session
select t2.blocking_session_id,COUNT(0) counts
from sys.dm_tran_locks as t1 with(nolock) , sys.dm_os_waiting_tasks as t2 with(nolock)
where t1.lock_owner_address = t2.resource_address
group by blocking_session_id
order by 2;
--3. 被阻塞时间最长的进程:==================================================================
--被阻塞时间最长的session
select top 10 t1.resource_type as [lock type] ,db_name(resource_database_id) as [database]
,t1.resource_associated_entity_id as [blk object]
,t1.request_mode as [lock req] -- lock requested
,t1.request_session_id as [waiter sid] -- spid of waiter
,t2.wait_duration_ms as [wait time]
,(select text from sys.dm_exec_requests as r with(nolock) --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as waiter_batch
,(select substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2+1)
from sys.dm_exec_requests as r with(nolock)
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as waiter_stmt --- statement executing now
,t2.blocking_session_id as [blocker sid] --- spid of blocker
,(select text from sys.sysprocesses as p with(nolock) --- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as blocker_stmt,getdate() time
from sys.dm_tran_locks as t1 with(nolock) , sys.dm_os_waiting_tasks as t2 with(nolock)
where t1.lock_owner_address = t2.resource_address
order by t2.wait_duration_ms desc;
SELECT sessions.nameAS SessionName,sevents.packageasPackageName,
sevents.name AS EventName,
sevents.predicate, sactions.nameAS ActionName, stargets.nameAS TargetName
FROM sys.server_event_sessionssessions
INNER JOIN sys.server_event_session_eventssevents
ON sessions.event_session_id= sevents.event_session_id
INNER JOIN sys.server_event_session_actionssactions
ON sessions.event_session_id= sactions.event_session_id
INNER JOIN sys.server_event_session_targetsstargets
ON sessions.event_session_id= stargets.event_session_id
--低效的sql
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
--其他网址
http://www.cnblogs.com/bhtfg538/archive/2011/01/21/1939706.html
http://msdn.microsoft.com/zh-cn/library/ms186265.aspx 跟踪的含义