MSSQL DBA 精华总结[处理对象名称中特殊字符]

Cherish_j_wang

MSSQL DBA精华总结

之DeadLock Graph Trace文件分析

场景

在MSSQL DBA的工作中，经常会有DeadLock的原因分析的案例。本文的目的在于使用SQL脚本获取DeadLock Graph Trace文件的信息，从而达到对DeadLock的原因分析的目的。

前提 Deadlock_testing.sql (8.4 KB, 下载次数: 60)

2012-3-12 17:39 上传

点击文件名下载附件

在本次分享开始之前，我们首先必须要用MSSQL ProfilerDeadLock Graph的Trace文件，你可要从本文的附件中下载(Deadlock_testing.sql，然后将后缀名修改为trc)，也可要使用下面的方法来自己生成。

1.      开启MSSQL Profiler：开始->运行->键入profiler

2.      Trace DeadLock Graph Event:在Profiler窗体中，开启一个Trace->显示所有事件->依次找到Locks->DeadLocak Graph->运行（参见下面的截图，按照字母标号依次点击即可）

注：

我们仅Trace这一个事件就好了，请取消其他无用的多余事件。

3.      在MSSQL中创建测试所要使用到的表

1. use test
   
2. GO
   
3. 
   
4. if OBJECT_ID('dbo.test_deadlock1','u') is not null
   
5.         drop table dbo.test_deadlock1
   
6. go
   
7. 
   
8. create table dbo.test_deadlock1(
   
9. id int identity(1,1) not null primary key
   
10. ,name varchar(20) null
    
11. );
    
12. 
    
13. if OBJECT_ID('dbo.test_deadlock2','u') is not null
    
14.         drop table dbo.test_deadlock2
    
15. go
    
16. 
    
17. create table dbo.test_deadlock2(
    
18. id int identity(1,1) not null primary key
    
19. ,name varchar(20) null
    
20. );
    
21. GO
    
22. 
    
23. insert into dbo.test_deadlock1
    
24. select 'AA'
    
25. UNION ALL
    
26. select 'BB';
    
27. 
    
28. 
    
29. insert into dbo.test_deadlock2
    
30. select 'AA'
    
31. UNION ALL
    
32. select 'BB';
    
33. GO
    

复制代码

4.      开启一个MSSQL连接（比如SPID为53），在这个连接中执行下面的语句

1. use test
   
2. GO
   
3. 
   
4. begin tran
   
5. update dbo.test_deadlock1
   
6. set name = 'CC'
   
7. where id = 1
   
8. ;
   
9. waitfor delay '00:00:05'
   
10. update dbo.test_deadlock2
    
11. set name = 'CC'
    
12. where id = 1
    
13. ;
    
14. rollback
    

复制代码

5.      再打开另外一个MSSQL连接（比如SPID为54），在此连接中执行下面的语句(4，5之间执行的事件差不要超过5秒)

1. use test
   
2. GO
   
3. 
   
4. begin tran
   
5. update dbo.test_deadlock2
   
6. set name = 'CC'
   
7. where id = 1
   
8. ;
   
9. 
   
10. update dbo.test_deadlock1
    
11. set name = 'CC'
    
12. where id = 1
    
13. ;
    
14. commit

复制代码

6.      查看Profiler中的DeadLock Graph Trace到的信息，如下图所示：

7.      保存Trace信息，参加下图:比如将Trace信息保存到I:\Temp\Deadlock_testing.trc

脚本
DeadLockGraph_analysis.sql (2.98 KB, 下载次数: 49)

2012-3-12 17:27 上传

点击文件名下载附件

在Deadlock_testing.trc文件生成后，接下来最重要的事情就是对这个文件进行分析了，那么如何对一个Trace文件进行分析呢？这个就是我们今天需要分享的重点了，前面的篇幅都是铺垫。我们讲，我们的目的使用SQL脚本对这个文件进行分析，话不多说了，直接上脚本：

1. use master
   
2. go
   
3. 
   
4. -- declare variables.
   
5. declare
   
6.         @file nvarchar(256)
   
7. ;
   
8. select
   
9.         @file = N'I:\Temp\Deadlock_testing.trc'
   
10. ;
    
11. 
    
12. WITH DATA
    
13. AS
    
14. (
    
15.         /*
    
16.         SELECT
    
17.                         RowNumber = row_number() OVER (ORDER BY StartTime)
    
18.                         ,DeadlockGraph = CAST(TextData AS xml)
    
19.                         ,StartTime
    
20.                         ,spid
    
21.         FROM test.dbo.Deadlock_testing WITH (NOLOCK)
    
22.         WHERE EventClass = 148*/
    
23.         
    
24.         SELECT
    
25.                 --[TraceID] = @trace_id ,
    
26.                 RowNumber = row_number() OVER (ORDER BY StartTime), -- assign a row number to each deadlock
    
27.                 [DeadlockGraph]=case when TextData like '<deadlock-list%' then convert(xml, TextData) else null end,
    
28.                 *
    
29.         from ::fn_trace_gettable(@file, default)
    
30.         where TextData like '<deadlock-list%'
    
31. )
    
32. ,
    
33. deadlock
    
34. AS
    
35. (
    
36.         SELECT
    
37.                         RowNumber
    
38.                         ,OwnerID = T.C.value('@id', 'varchar(50)')
    
39.                         ,SPid = T.C.value('(./@spid)[1]','int')
    
40.                         ,status = T.C.value('(./@status)[1]','varchar(10)')
    
41.                         --,PagelockObject = T.C.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)')
    
42.                         --,DeadlockObject = T.C.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)')
    
43.                         --,KeylockObject = T.C.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)')
    
44.                         --,DeadlockObject = T.C.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)')
    
45.                         ,Victim = case when T.C.value('@id', 'varchar(50)') = T.C.value('./../../@victim','varchar(50)') then 1 else 0 end
    
46.                         ,LockMode = T.C.value('@lockMode', 'varchar(20)')
    
47.                         ,DeadlockGraph
    
48.                         ,Inputbuf = T.C.value('(./inputbuf/text())[1]','varchar(max)')
    
49.                         ,Code = T.C.value('(./executionStack/frame/text())[1]','VARCHAR(max)')
    
50.                         ,SPName = T.C.value('(./executionStack/frame/@procname)[1]','sysname')
    
51.                         ,Hostname = T.C.value('(./@hostname)[1]','sysname')
    
52.                         ,Clientapp = T.C.value('(./@clientapp)[1]','varchar(max)')
    
53.                         ,LoginName = T.C.value('@loginname', 'varchar(20)')
    
54.                         ,Action = T.C.value('(./@transactionname)[1]','varchar(max)')
    
55.                         ,StartTime
    
56.                         ,TransactionTime = T.C.value('@lasttranstarted', 'datetime')
    
57.                         --,*
    
58.         FROM DATA AS A
    
59.                 CROSS APPLY DeadlockGraph.nodes('deadlock-list/deadlock/process-list/process') AS T(C)
    
60. )
    
61. ,
    
62. keylock
    
63. AS
    
64. (
    
65.         SELECT
    
66.                 OwnerID = T.C.value('./owner[1]/@id', 'varchar(50)')
    
67.                 ,KeylockObject = T.C.value('./../@objectname', 'varchar(200)')
    
68.                 ,Indexname = T.C.value('./../@indexname', 'varchar(200)')
    
69.                 ,IndexLockMode = T.C.value('./../@mode', 'varchar(20)')
    
70.                 --,owner = T.C.query('.')
    
71.         FROM DATA AS A
    
72.                 CROSS APPLY DeadlockGraph.nodes('deadlock-list/deadlock/resource-list/keylock/owner-list') AS T(C)
    
73. )
    
74. SELECT
    
75.         --A.OwnerID
    
76.         A.SPid
    
77.         ,is_Vitim = A.Victim
    
78.         --,A.DeadlockGraph
    
79.         ,A.SPName
    
80.         ,A.Code
    
81.         ,A.LockMode
    
82.         ,A.StartTime
    
83.         ,B.Indexname
    
84.         ,B.KeylockObject
    
85.         ,B.IndexLockMode
    
86.         ,A.Inputbuf
    
87.         ,A.Hostname
    
88.         ,A.LoginName
    
89.         ,A.Clientapp
    
90.         ,A.Action
    
91.         ,status
    
92.         ,A.TransactionTime
    
93. FROM deadlock AS A
    
94.         LEFT JOIN keylock AS B
    
95.         ON A.OwnerID = B.OwnerID
    
96. ORDER BY A.RowNumber,A.Victim

复制代码

结果如下：

我们可以从结果中很明确的看到：

1.       spid=54号进程是DeadLock的牺牲品

2.       死锁时执行的语句是

spid=54

1. UPDATE [dbo].[test_deadlock1] set [name] = @1  WHERE [id]=@2     

复制代码

spid=53
UPDATE [dbo].[test_deadlock2] set [name] = @1  WHERE [id]=@2     

3.      DeadLock时Lock的类型是X锁

4.      DeadLock是发生在主键上

…

说明

1.      7中保存Trace文件，你也可以将Trace信息保存到表中。然后将脚本中的第15行~第22行注释拿掉，然后注释掉第24行~第30行，也可以得到同样的信息

2.      DeadLock Graph的Trace文件中的textData其实是XML格式，我们的方法就是分析这个XML格式，只是大家需要了解每个节点，每个属性的含义。

3.      DeadLock Graph的XML文件也请参见下面的附件(也请将后缀名.sql修改为xml)，以供大家参考

DeadlockGraph1.sql (3.75 KB, 下载次数: 47)

2012-3-12 17:32 上传

点击文件名下载附件

4.      解决DeadLock不是文本介绍的重点(当然通过这个方法查明原因后，解决问题是很简单的事情了)

5.      本例中解决DeadLock的方法是：按照相同的顺序访问表

6.      spid=54发生DeadLock时，会报告下面的错误

消息1205，级别13，状态51，第8 行

事务(进程ID 54)与另一个进程被死锁在锁资源上，并且已被选作死锁牺牲品。请重新运行该事务。

Cherish_j_wang

万恶啊~~不能上传.xml和.trc文件。。。。。。只能修改后缀名上传了~~得麻烦大家下载后自行修改后缀名了~~

Cherish_j_wang

hwtong 发表于 2012-3-8 20:40
好的 那样就更好了。希望能多多分享点东西给大家，让大家共同进步。
分享的多了，我是有奖励的哦

分享好多老了哟~~~~肚子都快要被掏空了，木有看到奖励呢？~~~

Cherish_j_wang

harrytwtc 发表于 2012-3-11 11:42
好物，收下了

谢谢您的光临

vsir01

非常感谢LZ无私奉献,正在寻找相关的脚本.

hwtong

Cherish_j_wang 发表于 2012-3-12 17:52
分享好多老了哟~~~~肚子都快要被掏空了，木有看到奖励呢？~~~

马上奖励你
两个鸡蛋
666P

Cherish_j_wang

vsir01 发表于 2012-3-13 09:25
非常感谢LZ无私奉献,正在寻找相关的脚本.

看来是“好雨知时节，当春乃发生”啊~~

Cherish_j_wang

hwtong 发表于 2012-3-13 09:49
马上奖励你
两个鸡蛋
666P

接鸡蛋~接分~~谢主隆恩~

newworldmcse

学习了、、、

Cherish_j_wang

MSSQL DBA 精华总结

之MSSQL查看和清空存储过程缓存

背景

MSSQL DBA工作中经常会遇到的一个需求就是存储过程优化，那么在优化之前我们需要搞清楚下面的问题：

1.      存储过程中，哪个语句块(或者哪几个语句块)最消耗性能？

2.      这些语句块的执行详细的执行计划如何？

3.      如果某存储过程执行计划已经编译到缓存中，如何手动的删除？

其实在MSSQL中，上面提到的三个问题，我们都可以使用语句来实现。参见下面的脚本。

脚本

还是直接上测试脚本

ExecutionPlan_freeSP_Test.sql (2.56 KB, 下载次数: 58)

2012-3-13 21:18 上传

点击文件名下载附件

1. --In SQL Server 2008
   
2. USE test
   
3. GO
   
4. -----===建立测试表=====
   
5. IF object_id('dbo.Test_SP_Cache','U') IS NOT NULL
   
6.       DROP TABLE dbo.Test_SP_Cache
   
7. GO
   
8. CREATE TABLE dbo.Test_SP_Cache
   
9. (
   
10.       id int identity(1,1) PRIMARY KEY
    
11.       ,name varchar(36)
    
12. );
    
13. 
    
14. -----===插入测试环境=====
    
15. INSERT INTO dbo.Test_SP_Cache
    
16. SELECT 'AA'
    
17. UNION ALL
    
18. SELECT 'BB'
    
19. UNION ALL
    
20. SELECT 'DD'
    
21. UNION ALL
    
22. SELECT 'DD'
    
23. UNION ALL
    
24. SELECT 'EE'
    
25. UNION ALL
    
26. SELECT 'EE'
    
27. ;
    
28. 
    
29. GO
    
30. -----===建立测试存储过程=====
    
31. IF object_id('dbo.UP_Test_SP_Cache','P') IS NOT NULL
    
32.       DROP PROC dbo.UP_Test_SP_Cache
    
33. GO
    
34. CREATE PROC dbo.UP_Test_SP_Cache(
    
35. @id int
    
36. ,@name varchar(20)
    
37. )
    
38. AS
    
39. BEGIN
    
40.       SELECT name
    
41.       FROM dbo.Test_SP_Cache WITH(NOLOCK)
    
42.       WHERE id = @id
    
43.       
    
44.       update dbo.Test_SP_Cache
    
45.       set name = NEWID()
    
46.       where name = @name
    
47. END;
    
48. 
    
49. GO
    
50. 
    
51. -----===查询应该无结果集=
    
52. --因为存储过程还没有执行，所以还暂时没有执行计划
    
53. SELECT *
    
54. FROM sys.dm_exec_cached_plans AS cache
    
55. CROSS APPLY sys.dm_exec_query_plan(cache.plan_handle)  AS pla
    
56. CROSS APPLY sys.dm_exec_sql_text(cache.plan_handle) AS txt
    
57. WHERE pla.objectid  = object_id('dbo.UP_Test_SP_Cache','P')
    
58. and  txt.objectid  = object_id('dbo.UP_Test_SP_Cache','P')
    
59. GO
    
60. 
    
61. -----===执行存储过程=
    
62. EXEC dbo.UP_Test_SP_Cache @id = 1,@name = 'DD';
    
63. GO
    
64. -----===查询应该有结果集=
    
65. SELECT
    
66.                 txt.text
    
67.                 ,pla.query_plan
    
68.                 ,database_name = DB_NAME()
    
69.                 ,txt.objectid
    
70.                 ,cache.objtype
    
71.                 ,cache.size_in_bytes
    
72. FROM sys.dm_exec_cached_plans AS cache
    
73. CROSS APPLY sys.dm_exec_query_plan(cache.plan_handle)  AS pla
    
74. CROSS APPLY sys.dm_exec_sql_text(cache.plan_handle) AS txt
    
75. WHERE pla.objectid  = object_id('dbo.UP_Test_SP_Cache','P')
    
76. and  txt.objectid  = object_id('dbo.UP_Test_SP_Cache','P')
    
77. GO
    
78. 
    
79. -----===清空某一存储过程的缓存=
    
80. declare
    
81.       @plan_id varbinary(64)
    
82. ;
    
83. 
    
84. SELECT @plan_id = cache.plan_handle
    
85. FROM sys.dm_exec_cached_plans AS cache
    
86. CROSS APPLY sys.dm_exec_query_plan(cache.plan_handle)  AS pla
    
87. WHERE pla.objectid  = object_id('dbo.UP_Test_SP_Cache','P')
    
88. 
    
89. DBCC FREEPROCCACHE (@plan_id);
    
90. GO
    
91. 
    
92. -----===缓存被清空查询应该无结果集=
    
93. SELECT *
    
94. FROM sys.dm_exec_cached_plans AS cache
    
95. CROSS APPLY sys.dm_exec_query_plan(cache.plan_handle)  AS pla
    
96. CROSS APPLY sys.dm_exec_sql_text(cache.plan_handle) AS txt
    
97. WHERE pla.objectid  = object_id('dbo.UP_Test_SP_Cache','P')
    
98. and  txt.objectid  = object_id('dbo.UP_Test_SP_Cache','P')
    
99. GO
    
100. 
     
101. -----===删除测试对象=
     
102. IF object_id('dbo.Test_SP_Cache','U') IS NOT NULL
     
103.       DROP TABLE dbo.Test_SP_Cache
     
104. GO
     
105. IF object_id('dbo.UP_Test_SP_Cache','P') IS NOT NULL
     
106.       DROP PROC dbo.UP_Test_SP_Cache
     
107. GO

复制代码

执行上面的测试语句，点击第三个结果集的query_plan

测试中存储过程的执行计划如下：

说明

1.      从存储过程的执行计划来看，第二个语句块性能消耗最为严重，为整个性能消耗的80%，所有这里是我们需要重点去优化的地方。

1. update dbo.Test_SP_Cache
   
2. set name = NEWID()
   
3. where name = @name

复制代码

2.      清空存储过程执行计划的命令很简单DBCC FREEPROCCACHE (plan_handle)，我们只需要找到这个Plan_handle即可。

1. DBCC FREEPROCCACHE (@plan_id);

复制代码

3.      当然有的人可能说，我直接执行存储过程的每个语句块就好了嘛~~真的是这样吗？这种方法至少有三个坏处：

a)        产品环境中的MSSQL如果存在DML语句的存储过程是无法直接执行语句块的

b)        如果语句块中有对变量(变量常量或者表变量)的时候，每个语句块的执行变得不够现实

c)        每个语句块的执行显得既麻烦又繁琐