开发者社区> 技术小甜> 正文

SQL Server Profiler – 存储过程调试

简介:
+关注继续查看

SQL Server中有许多不同的工具可以帮助调试复杂的存储过程。它们包括Visual Studio中可用的Transact-SQL调试器,可以用来嵌入打印语句或从存储过程返回调试状态,也可以用来唤起存储过程中的定制错误以记录状态信息。SQL跟踪还提供了一个为用户可配置事件的工具。

 

一个用户可配置事件无非就是对名为sp_trace_generateevent的系统存储过程的一个调用。这个存储过程有3个参数:

1. @eventid是介于82~91之间的整数值。每个值对应于10个用户可配置事件类中的一个,这10个类的号从0~9。值为82时会唤起“UserConfigurable:0”事件,83则会唤起“UserConfigurable:1”事件等。

2. @userinfo是一个用来填充事件的TextData列的nvarchar(128)值。

3. @userdata是一个用来填充事件的BinaryData列的varbinary(8000)值。

 

在一些特别难应付的情况下,不得不处理那些在测试环境中难以复制的偶尔失败的存储过程。这样很难进行长时间的跟踪,因为要真正地调试该状态,可能需要语句级的收集,而如果运行时间过长,这类收集就会生成大量的数据。一个更好的选择只在存储过程级和批处理级进行跟踪,并使用用户可配置事件来收集变量值和确定出现问题时可以帮助调试故障的其他数据。采用这种方法,就不必再担心跟踪会收集过多的数据,可以让系统自己去运行了。

 

要将其建立起来,首先必须知道sp_trace_generateevent存储过程需要ALTER TRACE权限才能运行。由于用户的存储过程不大可能拥有这个权限,因此最好是创建一个具有相应权限且调用sp_trace_generateevent的封装存储过程。做到这一点,必须用到SQL Server的模块签名特性。第一步是在主数据库中创建一个证书:


 

1
2
3
4
5
6
7
8
9
10
USE master
GO
  
CREATECERTIFICATE ALTER_TRACE_CERT
ENCRYPTION BYPASSWORD = '-USE_a!sTr0Ng_PWD-or-3~'
WITH
 SUBJECT = 'Certificate for ALTER TRACE',
 START_DATE = '20000101',
 EXPIRY_DATE = '99990101'
GO

 

接下来,创建一个基于该证书的登录。这个登录被同时授予ALTER TRACEAUTHENTICATE SERVER权限,后者赋予其将服务器级权限传至数据库级模块的权利(例如封装存储过程):

 

1
2
3
4
5
6
7
8
9
CREATE LOGINALTER_TRACE_LOGIN
FROM CERTIFICATEALTER_TRACE_CERT
GO
  
GRANT ALTER TRACETO ALTER_TRACE_LOGIN
GO
  
GRANTAUTHENTICATE SERVER TO ALTER_TRACE_LOGIN
GO

 

在这项工作完成后,备份该证书,包括密钥。该备份可以用来恢复任何在其中使用封装存储过程的用户数据库相同的证书。

 

1
2
3
4
5
6
7
8
9
BACKUP CERTIFICATE ALTER_TRACE_CERT
TO FILE = ‘C:\ALTER_TRACE.cer’
WITH PRIVATE KEY
(
FILE = ‘C:\ALTER_TRACE.pvk’,
ENCRYPTION BY PASSWORD = ‘-USE_a!sTr0Ng_PWD-or-3~’,
DECRYPTION BY PASSWORD = ‘-USE_a!sTr0Ng_PWD-or-3~’
)
GO

 

为了更好地理解本章的示例代码,下面将介绍怎样在tempdb中建立一个封装过程,但实际上在任何用户数据库中都可以做到这一点。下列代码在备份版本的tempdb中创建了一个证书,而后对sp_trace_generateevent存储过程做了简单封装:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
USE tempdb
GO
  
CREATE CERTIFICATE ALTER_TRACE_CERT
FROM FILE = ‘C:\ALTER_TRACE.cer’
WITH PRIVATE KEY
(
FILE = ‘C:\ALTER_TRACE.pvk’,
ENCRYPTION BY PASSWORD = ‘-USE_a!sTr0Ng_PWD-or-3~’,
DECRYPTION BY PASSWORD = ‘-USE_a!sTr0Ng_PWD-or-3~’
)
GO
  
CREATE PROCEDURE ThrowEvent
@eventid INT,
@userinfo nvarchar(128),
@userdata varbinary(8000)
AS
BEGIN
EXEC sp_trace_generateevent
@eventid = @eventid,
@userinfo = @userinfo,
@userdata = @userdata
END
GO

 

为了完成这一过程,这个存储过程签署了该证书,并给这个过程有效的与ALTER_TRACE_LOGIN登录等同的所有权限,随后这个权限被授予运行该存储过程的任何用户:

 

1
2
3
4
5
6
7
ADD SIGNATURE TO ThrowEvent
BY CERTIFICATE ALTER_TRACE_CERT
WITH PASSWORD '-USE_a!sTr0Ng_PWD-or-3~'
GO
  
GRANT EXEC ON ThrowEvent TO [public]
GO

 

如果用户在自己选择的数据库上创建好ThrowEvent存储过程,就可以开始从别的存储过程内部使用了。另外,由于证书的存在,就可以不用考虑调用程序有哪些权限。当试图找出间歇故障时,这个工具十分重要。

 

例如,假设在测试中发现存储过程中需要更新一些列,而这些列所在的表有时似乎不做任何更新。这个故障看上去是由该存储过程被调用的同时另一个表的状态导致的,但是用户无法用之前的排列方式重新生成。对其进行调试,可以在更新后的存储过程中插入下列代码:

 

1
2
IF @@ROWCOUNT = 0
EXEC ThrowEvent 82, N’No data inserted into MyTable’, 0x0000

 

还应当建立一个跟踪来捕捉“RPC:Starting”、“SQL:BatchStarting”及“UserConfigurable:0”事件。运行一段时间之后,如果用户自定义事件被激发并告知用户没有行插入,就可能已经收集好足够多的后台数据以确定在插入时另一个表的状态。

 

这个例子显示出往存储过程调试工具箱里添加这个工具的好处。用户对存储过程中进展的可见性控制程度越高,就越容易在小故障变大之前跟踪并解决。


















本文转自UltraSQL51CTO博客,原文链接: http://blog.51cto.com/ultrasql/1589434,如需转载请自行联系原作者


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
C# 使用FileUpload控件上传图片,将文件转换成二进制进行存储与读取
状况描述:   需要上传文件,但是不想要保存到实体路径下,便可以用该功能来实现。   效果图:      点击【Upload】按钮,上传文件到数据库;   点击【Preview】,预览文件;   具体实现:   前台: 1 2 3 4 : 5 ...
1764 0
k8s与监控--prometheus的远端存储
前言 prometheus在容器云的领域实力毋庸置疑,越来越多的云原生组件直接提供prometheus的metrics接口,无需额外的exporter。所以采用prometheus作为整个集群的监控方案是合适的。
3032 0
sqlserver 通用分页存储过程
来源:http://www.jb51.net/article/19936.htm CREATE PROCEDURE commonPagination @columns varchar(500), --要显示的列名,用逗号隔开 @tableName varchar(100), --要查询...
705 0
SQLSERVER存储过程语法详解
SQL SERVER存储过程语法: Create PROC [ EDURE ] procedure_name [ ; number ]     [ { @parameter data_type }         [ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ]   [ WITH     { RECOMPILE | ENCRY
1509 0
SQL Server 在多个数据库中创建同一个存储过程(Create Same Stored Procedure in All Databases)
原文:SQL Server 在多个数据库中创建同一个存储过程(Create Same Stored Procedure in All Databases) 一.本文所涉及的内容(Contents) 本文所涉及的内容(Contents) 背景(Contexts) 遇到的问题(Problems)...
1263 0
+关注
10145
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载