用户名：xjsunjie
文章数：1042
评论数：2189
访问量：2738408
注册日期：
阅读量：1297
阅读量：3317
阅读量：433449
阅读量：1121220
51CTO推荐博文
死锁，简而言之，两个或者多个trans，同时请求对方正在请求的某个对象，导致双方互相等待。简单的例子如下：
trans1 trans2
&&&&&&&&&&&&&&&&&&&&&&&&
1.IDBConnection.BeginTransaction 1.IDBConnection.BeginTransaction
2.update table A 2.update table B
3.update table B 3.update table A
那么，很容易看到，如果trans1和trans2，分别到达了step3，那么trans1会请求对于B的X锁，trans2会请求对于A的X锁，而二者的锁在step2上已经被对方分别持有了。由于得不到锁，后面的Commit无法执行，这样双方开始死锁。
好，我们看一个简单的例子，来解释一下，应该如何解决死锁问题。
& Batch #1
CREATE DATABASE deadlocktest
USE deadlocktest
SET NOCOUNT ON
DBCC TRACEON (1222, -1)
& 在SQL2005中，增加了一个新的dbcc参数，就是1222，原来在2000下，我们知道，可以执行dbcc
&traceon(,-1)看到所有的死锁信息。SqlServer 2005中，对于1204进行了增强，这就是1222。
具体用法：dbcc traceon(, 3605,-1)这样就可以在ErrorLog中生成死锁纪录，会纪录死锁类型和导致死锁的存储过程。
关闭的命令是dbcc traceoff。
IF OBJECT_ID (&t1&) IS NOT NULL DROP TABLE t1
IF OBJECT_ID (&p1&) IS NOT NULL DROP PROC p1
IF OBJECT_ID (&p2&) IS NOT NULL DROP PROC p2
CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 char(5000))
DECLARE @x int
SET @x = 1
WHILE (@x &= 1000) BEGIN
INSERT INTO t1 VALUES (@x*2, @x*2, @x*2, @x*2)
SET @x = @x + 1
CREATE CLUSTERED INDEX cidx ON t1 (c1)
CREATE NONCLUSTERED INDEX idx1 ON t1 (c2)
CREATE PROC p1 @p1 int AS SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
CREATE PROC p2 @p1 int AS
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1
上述sql创建一个deadlock的示范数据库，插入了1000条数据，并在表t1上建立了c1列的聚集索引，和c2列的非聚集索引。另外创建了两个sp，分别是从t1中select数据和update数据。
好，打开一个新的查询窗口，我们开始执行下面的query：
& Batch #2
USE deadlocktest
SET NOCOUNT ON
WHILE (1=1) EXEC p2 4
开始执行后，然后我们打开第三个查询窗口，执行下面的query：
& Batch #3
USE deadlocktest
SET NOCOUNT ON
CREATE TABLE #t1 (c2 int, c3 int)
WHILE (1=1) BEGIN
INSERT INTO #t1 EXEC p1 4
TRUNCATE TABLE #t1
开始执行，哈哈，很快，我们看到了这样的错误信息：
Msg 1205, Level 13, State 51, Procedure p1, Line 4
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
spid54发现了死锁。
那么，我们该如何解决它？
在SqlServer 2005中，我们可以这么做：
1.在trans3的窗口中，选择EXEC p1 4，然后right click，看到了菜单了吗？选择Analyse Query in Database Engine Tuning Advisor。
2.注意右面的窗口中，wordload有三个选择：负载文件、表、查询语句，因为我们选择了查询语句的方式，所以就不需要修改这个radio option了。
3.点左上角的Start Analysis按钮
4.抽根烟，回来后看结果吧！出现了一个分析结果窗口，其中，在Index Recommendations中，我们发现了一条信息：大意是，在表t1上增加一个非聚集索引索引：t2+t1。
5.在当前窗口的上方菜单上，选择Action菜单，选择Apply Recommendations，系统会自动创建这个索引。
重新运行batch #3，呵呵，死锁没有了。
这种方式，我们可以解决大部分的Sql Server死锁问题。那么，发生这个死锁的根本原因是什么呢？为什么增加一个non clustered index，问题就解决了呢？
这次，我们分析一下，为什么会死锁呢？再回顾一下两个sp的写法：
CREATE PROC p1 @p1 int AS
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
CREATE PROC p2 @p1 int AS
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1
很奇怪吧！p1没有insert，没有delete，没有update，只是一个select，p2才是update。这个和我们前面说过的，trans1里面updata A，update B；trans2里面upate B，update A，根本不贴边啊！
那么，什么导致了死锁？
需要从事件日志中，看sql的死锁信息：
Spid X is running this query (line 2 of proc [p1], inputbuffer && EXEC p1 4 &&):
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
Spid Y is running this query (line 2 of proc [p2], inputbuffer &EXEC p2 4&):
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
The SELECT is waiting for a Shared KEY lock on index t1.cidx. The UPDATE holds a conflicting X lock.
The UPDATE is waiting for an eXclusive KEY lock on index t1.idx1. The SELECT holds a conflicting S lock.
首先，我们看看p1的执行计划。怎么看呢？可以执行set statistics profile on，这句就可以了。下面是p1的执行计划
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
|&Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [t1].[c1]))
|&Index Seek(OBJECT:([t1].[idx1]), SEEK:([t1].[c2] &= [@p1] AND [t1].[c2] &= [@p1]+(1)) ORDERED FORWARD)
|&Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[t1].[c1] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)
我们看到了一个nested loops，第一行，利用索引t1.c2来进行seek，seek出来的那个rowid，在第二行中，用来通过聚集索引来查找整行的数据。这是什么？就是bookmark lookup啊！为什么？因为我们需要的c2、c3不能完全的被索引t1.c1带出来，所以需要书签查找。
好，我们接着看p2的执行计划。
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
|&Clustered Index Update(OBJECT:([t1].[cidx]), OBJECT:([t1].[idx1]), SET:([t1].[c2] = [Expr1004]))
|&Compute Scalar(DEFINE:([Expr1013]=[Expr1013]))
|&Compute Scalar(DEFINE:([Expr1004]=[t1].[c2]+(1), [Expr1013]=CASE WHEN CASE WHEN &
|&Top(ROWCOUNT est 0)
|&Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[@p1]) ORDERED FORWARD)
通过聚集索引的seek找到了一行，然后开始更新。这里注意的是，update的时候，它会申请一个针对clustered index的X锁的。
实际上到这里，我们就明白了为什么update会对select产生死锁。update的时候，会申请一个针对clustered index的X锁，这样就阻塞住了（注意，不是死锁！）select里面最后的那个clustered index seek。死锁的另一半在哪里呢？注意我们的select语句，c2存在于索引idx1中，c1是一个聚集索引cidx。问题就在这里！我们在p2中更新了c2这个值，所以sqlserver会自动更新包含c2列的非聚集索引：idx1。而idx1在哪里？就在我们刚才的select语句中。而对这个索引列的更改，意味着索引集合的某个行或者某些行，需要重新排列，而重新排列，需要一个X锁。
SO&&&，问题就这样被发现了。
总结一下，就是说，某个query使用非聚集索引来select数据，那么它会在非聚集索引上持有一个S锁。当有一些select的列不在该索引上，它需要根据rowid找到对应的聚集索引的那行，然后找到其他数据。而此时，第二个的查询中，update正在聚集索引上忙乎：定位、加锁、修改等。但因为正在修改的某个列，是另外一个非聚集索引的某个列，所以此时，它需要同时更改那个非聚集索引的信息，这就需要在那个非聚集索引上，加第二个X锁。select开始等待update的X锁，update开始等待select的S锁，死锁，就这样发生鸟。
那么，为什么我们增加了一个非聚集索引，死锁就消失鸟？我们看一下，按照上文中自动增加的索引之后的执行计划：
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
|&Index Seek(OBJECT:([deadlocktest].[dbo].[t1].[_dta_index_t1_7___K2_K1_3]), SEEK:([deadlocktest].[dbo].[t1].[c2] &= [@p1] AND [deadlocktest].[dbo].[t1].[c2] &= [@p1]+(1)) ORDERED FORWARD)
哦，对于clustered index的需求没有了，因为增加的覆盖索引已经足够把所有的信息都select出来。就这么简单。
实际上，在sqlserver 2005中，如果用profiler来抓eventid：1222，那么会出现一个死锁的图，很直观的说。
下面的方法，有助于将死锁减至最少（详细情况，请看SQLServer联机帮助，搜索：将死锁减至最少即可。
按同一顺序访问对象。
避免事务中的用户交互。
保持事务简短并处于一个批处理中。
使用较低的隔离级别。
使用基于行版本控制的隔离级别。
将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON，使得已提交读事务使用行版本控制。
使用快照隔离。
使用绑定连接。
实例：登录到数据库实例，新建查询后执行
dbcc traceon(, 3605,-1)
1204 返回参与死锁的锁的资源和类型，以及受影响的当前命令。
1222 以不符合任何 XSD 架构的 XML 格式，返回参与死锁的锁的资源和类型，以及受影响的当前命令。参考/zh-cn/library/ms188396.aspx
3605 启动参数将此信息写入 SQL Server 错误日志。 可参考/kb/832524/zh-cn
了这篇文章
类别：┆阅读(0)┆评论(0)
本文收录至博客专题：《》Love SQL Server, Love life.
Do you still use trace flag 1204 and 1222 to monitor Deadlock? or using profile to capture deadlock? Now we are in SQL Server 2012!& One of the biggest improvement of SQL 2012 is Extended Events.Extended Events can replace SQL Profiler, and it is more powerful with less performance impact than SQL Profiler. Extended Events has been introduced in SQL Server world from SQL2008, and in SQL2012, it has been integrated into SQL Server Management Studio(SSMS), see the pic below:Now you can use SSMS to manage your Extends Events session. By default, there are 2 session created, "AlwaysOn_health" and "system_health", and "system_health" is started when SQL Service startup. You can script the session and check the defination:CREATE EVENT SESSION [system_health] ON SERVER ADD EVENT sqlclr.clr_allocation_failure(&&& ACTION(package0.callstack,sqlserver.session_id)),ADD EVENT sqlclr.clr_virtual_alloc_failure(&&& ACTION(package0.callstack,sqlserver.session_id)),ADD EVENT sqlos.memory_broker_ring_buffer_recorded,ADD EVENT sqlos.memory_node_oom_ring_buffer_recorded(&&& ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)),ADD EVENT sqlos.scheduler_monitor_deadlock_ring_buffer_recorded,ADD EVENT sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded,ADD EVENT sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded,ADD EVENT sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded,ADD EVENT sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded,ADD EVENT sqlos.scheduler_monitor_system_health_ring_buffer_recorded,ADD EVENT sqlos.wait_info(&&& ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text)&&& WHERE ([duration]&(15000) AND ([wait_type]&(31) AND ([wait_type]&(47) AND [wait_type]&(54) OR [wait_type]&(38) OR [wait_type]&(63) AND [wait_type]&(70) OR [wait_type]&(96) AND [wait_type]&(100) OR [wait_type]=(107) OR [wait_type]=(113) OR [wait_type]&(174) AND [wait_type]&(179) OR [wait_type]=(186) OR [wait_type]=(207) OR [wait_type]=(269) OR [wait_type]=(283) OR [wait_type]=(284)) OR [duration]&(30000) AND [wait_type]&(22)))),ADD EVENT sqlos.wait_info_external(&&& ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text)&&& WHERE ([duration]&(5000) AND ([wait_type]&(365) AND [wait_type]&(372) OR [wait_type]&(372) AND [wait_type]&(377) OR [wait_type]&(377) AND [wait_type]&(383) OR [wait_type]&(420) AND [wait_type]&(424) OR [wait_type]&(426) AND [wait_type]&(432) OR [wait_type]&(432) AND [wait_type]&(435) OR [duration]&(45000) AND ([wait_type]&(382) AND [wait_type]&(386) OR [wait_type]&(423) AND [wait_type]&(427) OR [wait_type]&(434) AND [wait_type]&(437) OR [wait_type]&(442) AND [wait_type]&(451) OR [wait_type]&(451) AND [wait_type]&(473) OR [wait_type]&(484) AND [wait_type]&(499) OR [wait_type]=(365) OR [wait_type]=(372) OR [wait_type]=(377) OR [wait_type]=(387) OR [wait_type]=(432) OR [wait_type]=(502))))),ADD EVENT sqlserver.connectivity_ring_buffer_recorded(SET collect_call_stack=(1)),ADD EVENT sqlserver.error_reported(&&& ACTION(package0.callstack,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)&&& WHERE ([severity]&=(20) OR ([error_number]=(17803) OR [error_number]=(701) OR [error_number]=(802) OR [error_number]=(8645) OR [error_number]=(8651) OR [error_number]=(8657) OR [error_number]=(8902)))),ADD EVENT sqlserver.security_error_ring_buffer_recorded(SET collect_call_stack=(1)),ADD EVENT sqlserver.sp_server_diagnostics_component_result(SET collect_data=(1)&&& WHERE ([sqlserver].[is_system]=(1) AND [component]&&(4))),ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET filename=N'system_health.xel',max_file_size=(5),max_rollover_files=(4)),ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)GOSo "system_health" will monitor the deadlock event by default. let's try the script below to generate deadlock scenario====================================================--create tablecreate table a1(a int)create table b1(b int)insert into a1 values(1)insert into b1 values(1)--run in first sessionselect @@SPIDbegin tranupdate& a1 set a=2update& b1 set b=2--run in second sessionselect @@SPIDbegin tranupdate& b1 set b=2update& a1 set a=2====================================================Congrat, you got deadlock and saw the error belowGo back to SSMS,&double click the "package0.event_file" under "system_health", you can review all the event just like below:?Double Click "Value" to check the deadlock detail, here you can find the process and resource info for the deadlock==============================================&deadlock&&&victim-list&& &victimProcess id="process2ed016558" /&&&/victim-list&&&process-list&& &process id="process2ed016558" taskpriority="0" logused="248" waitresource="RID: 6:1:169:0" waittime="3029" ownerId="54473" transactionname="user_transaction" lasttranstarted="T18:59:15.827" XDES="0x2f8252d28" lockMode="U" schedulerid="3" kpid="4852" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="T18:59:23.397" lastbatchcompleted="T18:59:15.830" lastattention="T00:00:00.830" clientapp="Microsoft SQL Server Management Studio - Query" hostname="V-XUJ1230" hostpid="5688" loginname="FAREAST\v-xuj" isolationlevel="read committed (2)" xactid="54473" currentdb="6" lockTimeout="" clientoption1="" clientoption2="390200"&&& &executionStack&&&& &frame procname="adhoc" line="1" stmtstart="16" sqlhandle="0x082c50d69d2e5f1dea1e2eda"&UPDATE [b1] set [b] = @1&&& &/frame&&&& &frame procname="adhoc" line="1" sqlhandle="0xda843e5bdb59f3c2ace4f8aadd0000"&update& b1 set b=2&&& &/frame&&& &/executionStack&&& &inputbuf&update& b1 set b=2&& &/inputbuf&& &/process&& &process id="process2ed0170c8" taskpriority="0" logused="248" waitresource="RID: 6:1:166:0" waittime="6233" ownerId="54474" transactionname="user_transaction" lasttranstarted="T18:59:18.503" XDES="0x2f82523a8" lockMode="U" schedulerid="3" kpid="1456" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="T18:59:20.210" lastbatchcompleted="T18:59:18.503" lastattention="T00:00:00.503" clientapp="Microsoft SQL Server Management Studio - Query" hostname="V-XUJ1230" hostpid="5688" loginname="FAREAST\v-xuj" isolationlevel="read committed (2)" xactid="54474" currentdb="6" lockTimeout="" clientoption1="" clientoption2="390200"&&& &executionStack&&&& &frame procname="adhoc" line="1" stmtstart="16" sqlhandle="0xcdb030dd161d461be83dcbbf17f0000"&UPDATE [a1] set [a] = @1&&& &/frame&&&& &frame procname="adhoc" line="1" sqlhandle="0xbc0edd6eb92eb9da0000"&update& a1 set a=2&&& &/frame&&& &/executionStack&&& &inputbuf&update& a1 set a=2&& &/inputbuf&& &/process&&&/process-list&&&resource-list&& &ridlock fileid="1" pageid="169" dbid="6" objectname="CDBTEST.dbo.b1" id="lock2f4b46480" mode="X" associatedObjectId="07584"&&& &owner-list&&&& &owner id="process2ed0170c8" mode="X" /&&& &/owner-list&&& &waiter-list&&&& &waiter id="process2ed016558" mode="U" requestType="wait" /&&& &/waiter-list&& &/ridlock&& &ridlock fileid="1" pageid="166" dbid="6" objectname="CDBTEST.dbo.a1" id="lock2f4b49180" mode="X" associatedObjectId="42048"&&& &owner-list&&&& &owner id="process2ed016558" mode="X" /&&& &/owner-list&&& &waiter-list&&&& &waiter id="process2ed0170c8" mode="U" requestType="wait" /&&& &/waiter-list&& &/ridlock&&&/resource-list&&/deadlock&==============================================or you can click the "Deadlock" TAB to see the diagram.Sql Server 高频,高并发访问中的键查找死锁解析-SQLSERVER走起-微转化
(window.slotbydup=window.slotbydup || []).push({
id: '1110850',
container: s,
size: '234,60',
display: 'inlay-fix'
介绍SQLSERVER的一些相关资讯,让众多SQLSERVER爱好者一起交流SQLSERVER,分享SQLSERVER的经验
朴槿惠无罪释放被提上日程！无儿无女巨额国家赔偿赠送教育
[广告]●●●●●●●●●●●
热门公众号Accounts
精彩内容热门推荐
传递有价值的信息!
thepapernews
澎湃新闻的用户进行沟通与互动的平台
参与、沟通、记录时代.
dianbing81
细说天下军情,你不知道的,我告诉你.
sijishanzhuang
传播?泰山?饮食文化
wwwnxbbcom
宁乡帮()官方微信.分享宁乡精彩生活,衣食住行一网打尽!
观察者网,智库与专业新闻媒体的完美结合.
每天早上一句正能量,让一天都充满希望和活力,晚上有真人聊天互动哦~
LL-liaocheng
在这里,读懂聊城!
wei-rongxian
微容县时代,为容县商家提供微平台服务~
新观点、新视角、新资讯.
wxk0203117
70后人的乡愁味道,心灵融汇的触碰,文化观点的漫谈,地方美食的记忆,身边人事的记录,地方历史的钩沉.
Sql Server 高频,高并发访问中的键查找死锁解析
&发表于& 00:01:41
死锁对于DBA或是数据库开发人员而言并不陌生,它的引发多种多样,一般而言,数据库应用的开发者在设计时都会有一定的考量进而尽量避免死锁的产生.但有时因为一些特殊应用场景如高频查询,高并发查询下由于数据库设计的潜在问题,一些不易捕捉的死锁可能出现从而影响业务.这里为大家介绍由于设计问题引起的键查找死锁及相关的解决办法.这里我们在测试的同时开启trace profiler跟踪死锁视图(locks:deadlock graph).(当然也可以开启跟踪标记,或者应用扩展事件(xevents)等捕捉死锁)创建测试对象codecreate table testklup
clskey int not null,
nlskey int not null,
int not null,
char(3000)
)create unique clustered index inx_cls on testklup(clskey)create unique nonclustered index inx_nlcs
on testklup(nlskey) include(cont1)insert into testklup select 1,1,100,'aaa'insert into testklup select 2,2,200,'bbb'insert into testklup select 3,3,300,'ccc'开启会话1 模拟高频update操作----模拟高频update操作
declare @i intset @i=100while 1=1
update testklup set
where clskey=1
end开启会话2 模拟高频select操作----模拟高频select操作declare @cont2 char(3000)while 1=1begin
select @cont2=cont2 from testklup where nlskey=1end此时开启会话2执行一小段时间时我们就可以看到类似错误信息:图1-1 图1-1 而在我们开启的跟踪中捕捉到了如下的死锁图.图1-2 图1-2 死锁分析:可以看出由于读进程(108)请求写进程(79)持有的X锁被阻塞的同时,写进程(79)又申请读进程(108)锁持有的S锁.读执行计划图1-3,写执行计划图1-4(由于在默认隔离级别下(读提交)读申请S锁只是瞬间过程,读完立即释放,不会等待事务完成),所以在并发,执行频率不高的情形下不易出现.但我们模拟的高频情况使得S锁获得频率非常高,此时就出现了仅仅两个会话,一个读,一个写就造成了死锁现象.
图1-3 图1-4 死锁原因:读操作中的键查找造成的额外锁(聚集索引)需求解决方案:在了解了死锁产生的原因后,解决起来就比较简单了.我们可以从以下几个方面入手.a 消除额外的键查找锁需的锁b 读操作时取消获取锁a.1我们可以创建覆盖索引使select语句中的查询列包含在指定索引中 CREATE NONCLUSTERED INDEX [inx_nlskey_incont2] ON [dbo].[testklup]([nlskey] ASC) INCLUDE ( [cont2]) a.2 根据查询需求,分步执行,通过聚集索引获取查询列,避免键查找.declare @cont2 char(3000)declare @clskey intwhile 1=1begin
select @clskey=clskey from testklup where nlskey=1
select @cont2=cont2 from testklup where
b 通过改变隔离级别,使用乐观并发模式,读操作时源行无需锁declare @cont2 char(3000)while 1=1begin
select @cont2=cont2 from testklup with(nolock) where nlskey=1end 结束语.我们在解决问题时,最好弄清问题的本质原因,通过问题点寻找出适合自己的环境的解决方案再实施.Involuntary DBA
声明：以上内容转载自微信公众号，并不代表本站观点及立场，且版权归属微信公众号。
微信公众号# 1.问题引出
老鸟为了重点栽培菜鸟，决定交给菜鸟一个艰巨而光荣的任务。这天，菜鸟刚到公司还未坐下，老鸟便劈头盖脸的问道：“你知道，我们如何Trace SQL Server执行语句吗？怎么手动分析这些Trace文件？如何将Trace File与Windows的性能监视器结合，看到每个语句执行时的性能开销？以及如何自动分析SQL Server Trace文件？”。
菜鸟还没有反应过来，就被
1.问题引出
老鸟为了重点栽培菜鸟，决定交给菜鸟一个艰巨而光荣的任务。这天，菜鸟刚到公司还未坐下，老鸟便劈头盖脸的问道：“你知道，我们如何Trace SQL Server执行语句吗？怎么手动分析这些Trace文件？如何将Trace File与Windows的性能监视器结合，看到每个语句执行时的性能开销？以及如何自动分析SQL Server Trace文件？”。
菜鸟还没有反应过来，就被杀死了99%的老细胞，下意识的回答：“啊？”。
“去研究下吧”，这个周给我答复就好了，老鸟看出来了菜鸟的困惑，心理暗自骄傲。
2.手动Trace SQL Server
菜鸟怀着一颗万事不着急，先问G哥的平稳心态，开始疯狂的问G哥，如何“手动Trace SQL Server”。G哥开始调动亿万个神经细胞，搜索着散落在地球上每一个角落的问题与答案，哦，原来使用SQL Server Profiler工具：
** Start =& All Programs =& Microsoft SQL Server R2 =& Performance Tools =& SQL Server Profiler **
或者是：** Start =& Run =& Profiler **
打开SQL Server Profiler后，** File =& New Trace =& Server type: Database Engine =& Server name: XXXX =& Login: XXXX =& Password: XXXX =& Connect **
连接完毕以后，设置Trace Properties：
** Events Selection =& Show all Events =& 选择要Trace的事件和字段 =& Run**。一个简单的Demo长相如下：
Trace启动后，一会儿就有被抓到的语句跑出来：
欧耶，手动部署SQL Server Trace很简单嘛，搞定。菜鸟迫不及待的问G哥，“如何手动部署Windows性能监视器”。
3.部署Windows 性能监视器
G哥心想，这两个是关联问题吧，早知道你要问这个问题了，早已在你问第一个问题时，已经帮你做了最佳推荐：
** Start =& Run =& Perfmon =& User Defined =& right click on the right side blank space =& New =& Data Collector Set =& type Name: PERFMON_BASE =& Create manually(Advanced) =& Next **
** Create data logs =& Performance counter =& Event trace data =& Next =& Select counters from computer =& 展开性能指标分类，比如：SQL Server:Buffer Manager =& Page read/sec, page writes/sec等 =& Add =& OK **
** Next =& Root directory =& Finish **
性能监视器创建完毕后，最后一个步骤需要启动这个性能监视器，来抓取SQL Server性能指标。选择刚才**创建的数据收集器 =& 右键点击 =& Start。**
4.Trace文件关联性能监视器log文件
当菜鸟完成手动搜集Windows性能监视器以后，信心大增。再回过头来问G哥，哪知道G哥早已推荐他：“SQL Server Trace file与性能监视器log文件关联”。G哥是何等聪明绝顶啊。
当SQL Trace文件与性能监视器log文件关联以后，画面为分为四个区域：
查询语句区域：查看和选择查询语句
性能指标做图区域：可以看到选中的查询语句对应的各项性能指标做图（性能指标做图区域中红色的竖线）
性能指标选择区域：性能指标选择区域选中或者取消相关的性能指标
查询语句显示区域：查看被选中的相关查询语句详情
5.手动分析Trace文件
菜鸟能够将SQL Server Trace文件与性能监视器log文件关联在一起分析，是巨大的进步啊。可是，他还是不满足于现状，觉得一个个去点击查询语句，太过于原始，效率很难提升，而且不方便过滤出自己关心的查询语句。比如过滤出CPU占用最高的TOP 5查询；I/O最高的TOP 5查询；执行最为频繁的查询语句TOP 5等。
于是菜鸟想到了将SQL Server Trace File文件通过系统函数将其读出来，存到一个表里面。这样，就可以利用数据库强大的筛选，过滤，聚合功能得到自己关心的查询语句了。
@trace_file_path sysname
@trace_file_path = N'C:\Temp\perfmon\XXXX.trc'
FROM sys.fn_trace_gettable(@trace_file_path, NULL) AS T
到目前为止，菜鸟已经找到了人生努力的方向，成为高富帅，迎娶白富美是指日可待了。可是菜鸟在分析性能的过程中逐渐意识到，由于查询语句或者存储过程的参数值是“乱七八糟”的，很难做有效的聚合统计。
6.自动化分析Trace文件
革命尚未成功，同志还需努力，到底如何实现自动化分析Trace文件呢？菜鸟又想到了G哥，G哥很豪爽并不加思索的说“用RML Utilities for SQL Server啊”。看来现在G哥已是菜鸟的救火队长了，度娘表示很受伤，羡慕滴妒恨。
6.1.RML Utilities功能介绍
RML（Replay Markup Language）Utilities是MS SQL Server产品支持服务团队内部开发使用的一个trace文件分析工具。支持SQL Server ，12和SQL Server 2014。
主要的功能包括以下几个个方面：
分析最消耗SQL Server系统资源的应用和查询
去除参数干扰，统计汇总查询性能消耗
生成可视化报表，性能消耗大户一目了然
6.2.RML Utilities安装
首先从下面的地址下载对应的版本，分为X86的32位版和64位版：
RML安装文件(RMLSetup_AMD64.msi)是基于Windows MSI的文件。因此安装过程你懂的，非常简单，在此不一一截屏说明安装步骤了。安装完毕后，RML的默认安装目录会在**"C:\Program Files\Microsoft Corporation\RMLUtils\"**
6.3.使用方法
打开RML Command：**Start =& All programs =& RML Utilities for SQL Server =& RML Cmd Prompt**
执行如下命令：
ReadTrace -I"C:\Temp\perfmon\XXX.trc" -o"C:\Temp\OUTPUT" -S"." -d"PerfAnalysis" -E -f
Readtrace的参数是区分大小写的，并且输入文件和输出目录不能够在同一个目录下。详情参见Readtrace -?的参数描述，这条语句的参数说明：
-I: 输入的Trace文件目录和文件名
-o: 日志文件输出目录
-S: 数据存放的数据库服务器
-d: 数据库名字
-E: Windows认证模式链接数据库
-f: 不用生成每一个会话和请求的详细RML输出文件
命令执行完毕后会自动化生成以下的分类报表：
Performance Overview
Performance Overview分类报表包括其他分类报表的入口，资源使用率的统计，性能总览的详细信息。
Application Name
按照应用程序名称做分类统计，这个统计报表可以知道每个应用程序对SQL Server系统资源的消耗情况。利用这个报表，我们很轻松就可以找到资源使用的大户，针对性的采取必要的措施。
Unique Batches
这个分类报表非常有价值，在这个报表中，我们非常轻松的就可以发现占用CPU，Run Duration，IO Read，IO Write TOP Batches语句块。这个为我们优化具体的SQL语句块提供了非常方便的统计汇总。
Interesting Events
针对SQL Server数据库事件的统计分类报表。
Database ID
按照数据库标识ID的分类报表，从这个报表，我们可以很容易发现哪个数据库的压力比较大，可以选择作为我们重点优化的数据库。
Unique Statements
这个与Unique Batches分类统计报表类似，也是非常有价值的报表，只不过这个是针对特定语句的分类统计报表，而不是针对语句块，所以，这两个报表的分析方式非常类似。
这个报表分别从CPU，Duration，Reads和Writes四个角度统计出查询语句执行次数及所占百分比。当我们查看具体的执行语句的时候（点击处的执行语句），会打开下图中的Unique Statement Details页面。这个页面展示了相应查询语句非常详细的信息，包括：执行语句模版，按照CPU，Duration，Reads和Writes的统计汇总图展示，按照时间顺序的统计汇总表格，格式化后的查询语句。
既然拿到TOP Unique Query语句，接下来的要做的事情就是花80%的时间和精力去优化这20%的TOP Query并加以改善到产品环境，这样我们SQL Server性能问题就可以解决了。
Data Lineage
这个报表是Readtrace数据导入的日志信息统计，包含你使用的参数信息和RML的错误警告信息等。比如，这里就提示，我们的SQL Server Trace文件少了SP:StmtStarting事件的跟踪。
Login Name
按照登录用户聚合的分类统计报表，从这报表我们可以很容易发现哪些用户是数据库系统资源的大户。
总结来看，RML Utilities for SQL Server是一款与SQL Profiler结合得非常好的自动化Trace文件分析工具。利用这个工具强大的统计汇总功能，使得我们可以非常容易从多角度，多视野，多维度来发现问题，分析问题，解决问题，真正做到工欲善其事必先利其器的效果。
7.写在最后
当菜鸟把这份心得呈现在老鸟面前的时候，老鸟简直惊呆了：“不错啊，来，给你十三个赞，每月一个赞”。
“每年不是十二个月吗？”，菜鸟疑惑的问道。
“多的一个是十三薪”，老鸟一边走着一边回答。留着菜鸟一个人在风中凌乱。
如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件至：yqgroup@ 进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容。
用云栖社区APP，舒服~
【云栖快讯】首届阿里巴巴中间件技术峰会，揭秘阿里10年分布式技术沉淀！阿里高可用体系核心缔造者、全链路压测创始人，DRDS与TDDL负责人等大咖出场，干货分享，不可错过！&&
支持MySQL、SQL Server、PostgreSQL、MongoDB、Redis等关系型数据库和NoSQL...
RDS是一种稳定可靠、可弹性伸缩的在线数据库服务。支持MySQL、SQL Server、PostgreSQL、高...
提供一种性能卓越、稳定、安全、便捷的计算服务，帮助您快速构建处理能力出色的应用，解放计算给服务带来的压力，使您的...
为您提供简单高效、处理能力可弹性伸缩的计算服务，帮助您快速构建更稳定、安全的应用，提升运维效率，降低 IT 成本...
2017杭州云栖大会火热抢票
Loading...