qileilove

blog已经转移至github,大家请访问 http://qaseven.github.io/

SQL Server监控系列之调优排错

 使用SQL Server Profile GUI工具还是很多优势,首先是减少了我们监控的复杂性,可以款速的建立监控,在跟踪属性中,可以可以选择MSSQL为我们提供的模版,包括常用的T-SQL、T-SQL Duration、T-SQL Locks模版分别监控当前DB运行的所有查询,所有查询的耗时、所有的锁定状态。

  在跟踪属性 –> 选择事件选择 我们可以选择自己需要的事件,所有的事件在MSDN 都有定义->单击列筛选器 可以自定义过滤,排序噪点干扰因素

(我随便选择了一个耗时 = 500 微妙的过滤条件)

  其他的模版大家可以自己看看MSDN 手册,自己尝试一下:SQL Server 2008 R2 本机 MSDN

  服务器端跟踪和物理方式收集

  SQL Server Profile 只是对一些存储过程的封装,我更倾向于,自己定义常用的脚本,将监控结果保存在本机,用来大量的分析和存档。

  当然涉及4个存储过程,虽然设置过滤的脚本非常麻烦,但是SQL Server Profile 可以利用 文件->导出 可以导出监控脚本意味着,我们不需要编写复杂的T-SQL 脚本,不过还是建议大家熟悉这几个存储过程:

  sp_trace_create 定义跟踪 ,创建的跟踪会在sys.traces查询的到。

  s_trace_setevent 设置监控事件

  sp_trace_setfilter 设置过滤

  sp_trace_setstatus 设置跟踪的状态 常用的是 sp_trace_setstatus @traceid,0 停止功能 、sp_trace_setstatus @traceid,2 移除跟踪,这将导致sys.traces最终查询不到该跟踪

  其实整个跟踪还是比较简单的。我这里有一个常用的脚本:

  用来 监控超过指定秒数 和 数据库 的 批处理和存储过程 语句(超过5MB的文件,会执行ROLLOVER,根据文件名在后面添加类似_1,_2.trc的跟踪结果):

以下是代码片段:
CREATE PROC [dbo].[sp_trace_sql_durtion] @DatabaseName nvarchar(128), @Seconds bigint, @FilePath nvarchar(260) AS BEGIN DECLARE @rc int,@TraceID int,@MaxFileSize bigint; SET @MaxFileSize = 5; EXEC sp_trace_create @TraceID OUTPUT,2,@FilePath,@MaxFileSize,NULL; IF @rc != 0 RETURN; DECLARE @On bit; SET @On = 1; EXEC sp_trace_setevent @TraceID,10,35,@On; EXEC sp_trace_setevent @TraceID,10,1,@On; EXEC sp_trace_setevent @TraceID,10,13,@On; EXEC sp_trace_setevent @TraceID,41,35,@On; EXEC sp_trace_setevent @TraceID,41,1,@On; EXEC sp_trace_setevent @TraceID,41,13,@On; SET @Seconds = @Seconds * 1000000; EXEC sp_trace_setfilter @TraceID,13,0,4,@Seconds; IF @DatabaseName IS NOT NULL EXEC sp_trace_setfilter @TraceID,35,0,0,@DatabaseName EXEC sp_trace_setstatus @TraceID,1 SELECT TraceID = @TraceID; END

  参数非常的明了,数据库名称、执行事件超过多少秒、保存的路径。

  当我们运行这个脚本一段事件以后,可以快速的发现大量耗时的T-SQL,我们可以通过

  SELECT * FROM fn_trace_gettable(N'监控文件路径',1);

  来查看行方式的结果。

  同样的富有创造力的读者可以自己创建监控锁定,监控死锁等方式保存文件,但是我的建议是尽可能的减少噪音,也就是说我们要达到什么目地就在《Microsfot SQL Server 2005 技术内幕: T-SQL 程序设计》 中有一个正则,用来将类似的语句全部组合成,只有参数形式替换具体值的SQL CLR,但是我认为那个正则还有bug,等我空了给大家写一个,自己也能使用的更完善。


 使用SQL Server Profile GUI工具还是很多优势,首先是减少了我们监控的复杂性,可以款速的建立监控,在跟踪属性中,可以可以选择MSSQL为我们提供的模版,包括常用的T-SQL、T-SQL Duration、T-SQL Locks模版分别监控当前DB运行的所有查询,所有查询的耗时、所有的锁定状态。

  在跟踪属性 –> 选择事件选择 我们可以选择自己需要的事件,所有的事件在MSDN 都有定义->单击列筛选器 可以自定义过滤,排序噪点干扰因素

(我随便选择了一个耗时 = 500 微妙的过滤条件)

  其他的模版大家可以自己看看MSDN 手册,自己尝试一下:SQL Server 2008 R2 本机 MSDN

  服务器端跟踪和物理方式收集

  SQL Server Profile 只是对一些存储过程的封装,我更倾向于,自己定义常用的脚本,将监控结果保存在本机,用来大量的分析和存档。

  当然涉及4个存储过程,虽然设置过滤的脚本非常麻烦,但是SQL Server Profile 可以利用 文件->导出 可以导出监控脚本意味着,我们不需要编写复杂的T-SQL 脚本,不过还是建议大家熟悉这几个存储过程:

  sp_trace_create 定义跟踪 ,创建的跟踪会在sys.traces查询的到。

  s_trace_setevent 设置监控事件

  sp_trace_setfilter 设置过滤

  sp_trace_setstatus 设置跟踪的状态 常用的是 sp_trace_setstatus @traceid,0 停止功能 、sp_trace_setstatus @traceid,2 移除跟踪,这将导致sys.traces最终查询不到该跟踪

  其实整个跟踪还是比较简单的。我这里有一个常用的脚本:

  用来 监控超过指定秒数 和 数据库 的 批处理和存储过程 语句(超过5MB的文件,会执行ROLLOVER,根据文件名在后面添加类似_1,_2.trc的跟踪结果):

以下是代码片段:
CREATE PROC [dbo].[sp_trace_sql_durtion] @DatabaseName nvarchar(128), @Seconds bigint, @FilePath nvarchar(260) AS BEGIN DECLARE @rc int,@TraceID int,@MaxFileSize bigint; SET @MaxFileSize = 5; EXEC sp_trace_create @TraceID OUTPUT,2,@FilePath,@MaxFileSize,NULL; IF @rc != 0 RETURN; DECLARE @On bit; SET @On = 1; EXEC sp_trace_setevent @TraceID,10,35,@On; EXEC sp_trace_setevent @TraceID,10,1,@On; EXEC sp_trace_setevent @TraceID,10,13,@On; EXEC sp_trace_setevent @TraceID,41,35,@On; EXEC sp_trace_setevent @TraceID,41,1,@On; EXEC sp_trace_setevent @TraceID,41,13,@On; SET @Seconds = @Seconds * 1000000; EXEC sp_trace_setfilter @TraceID,13,0,4,@Seconds; IF @DatabaseName IS NOT NULL EXEC sp_trace_setfilter @TraceID,35,0,0,@DatabaseName EXEC sp_trace_setstatus @TraceID,1 SELECT TraceID = @TraceID; END

  参数非常的明了,数据库名称、执行事件超过多少秒、保存的路径。

  当我们运行这个脚本一段事件以后,可以快速的发现大量耗时的T-SQL,我们可以通过

  SELECT * FROM fn_trace_gettable(N'监控文件路径',1);

  来查看行方式的结果。

  同样的富有创造力的读者可以自己创建监控锁定,监控死锁等方式保存文件,但是我的建议是尽可能的减少噪音,也就是说我们要达到什么目地就在《Microsfot SQL Server 2005 技术内幕: T-SQL 程序设计》 中有一个正则,用来将类似的语句全部组合成,只有参数形式替换具体值的SQL CLR,但是我认为那个正则还有bug,等我空了给大家写一个,自己也能使用的更完善。

 监控异常

  在上个系列中,讲述了具体的SQL Event抓去的异常,可以及时通知,但是具体的异常信息,并不是特别详细。因此我们可以选择事件中的Error来添加有关T-SQL批处理和SP的所有异常,用于分析,这个跟踪非常有利于我们监控一些异常情况!!!我创建了一个跟踪的脚本,和上面的跟踪事件的脚本一样,超过5MB RollOver。我们要定期的执行这个跟踪,虽然不建议长期开启,但是定期监控处理异常是有利我们系统更加长时间运作的。

以下是代码片段:
CREATE PROC [dbo].[sp_trace_sql_exception] @FilePath nvarchar(260) AS DECLARE @rc int,@TraceID int,@Maxfilesize bigint SET @maxfilesize = 5 EXEC @rc = sp_trace_create @TraceID output, 2, @FilePath, @Maxfilesize, NULL IF (@rc != 0) RETURN; DECLARE @on bit SET @on = 1 EXEC sp_trace_setevent @TraceID, 33, 1, @on EXEC sp_trace_setevent @TraceID, 33, 14, @on EXEC sp_trace_setevent @TraceID, 33, 51, @on EXEC sp_trace_setevent @TraceID, 33, 12, @on EXEC sp_trace_setevent @TraceID, 11, 2, @on EXEC sp_trace_setevent @TraceID, 11, 14, @on EXEC sp_trace_setevent @TraceID, 11, 51, @on EXEC sp_trace_setevent @TraceID, 11, 12, @on EXEC sp_trace_setevent @TraceID, 13, 1, @on EXEC sp_trace_setevent @TraceID, 13, 14, @on EXEC sp_trace_setevent @TraceID, 13, 51, @on EXEC sp_trace_setevent @TraceID, 13, 12, @on DECLARE @intfilter int,@bigintfilter bigint; EXEC sp_trace_setstatus @TraceID, 1 SELECT TraceID=@TraceID GOTO finish ERROR: SELECT ErrorCode=@rc FINISH:

  定期执行吧,同志们,找异常。。。

  默认跟踪和黑盒跟踪

  在sys.traces中的TraceID = 1的跟踪是SQL Server 默认跟踪,这个跟踪比较轻量级,一般监控服务器的启用停止,对象的创建和删除,日志和数据文件自动增长以及其他数据库的变化。(监控那些没事删错了表的人,是最好的,当然前提不要都使用一个帐号!)

  可以通过

以下是代码片段:
EXEC sp_configure 'default trace enabled',0; RECONFIGURE WITH OVERRIDE;

  来关闭默认跟踪。

  黑盒跟踪,就是可以帮助我们诊断数据库没事自个奔了的异常,在MSDN 搜索sp_create_trace的时候应该也发现了

  的选项,那么我们也能创建一个类似的存储过程来快速的创建黑盒跟踪,帮助我们诊断一些异常!

以下是代码片段:
CREATE PROCEDURE sp_trace_blackbox @FilePath nvarchar(260) AS BEGIN DECLARE @TraceID int,@MaxFileSize bigint SET @MaxFileSize = 25; EXEC sp_trace_create @TraceID OUTPUT,8,@FilePath,@MaxFileSize EXEC sp_trace_setstatus @TraceID,1; END

  我这里提供@FilePath = NULL参数,这个默认就保存在SQL Server的数据文件夹中。

  结尾

  这里详细的描述了SQL Server Trace 的各种功能特性,有兴趣的朋友可以深入到MSDN研究监控,我这是也只是一笔带过,也参考了MSDN 和《Microsoft SQL Server 2005调优》那本书,下面的监控可能和大家讲述 DDL触发器监控,C2审核以及SQL Server的事件通知(涉及的Service Broker我会开一个系列和大家详细说说Service Broker),最后的结束可能就是说说2008的数据收集监控

posted on 2011-12-16 15:02 顺其自然EVO 阅读(159) 评论(0)  编辑  收藏


只有注册用户登录后才能发表评论。


网站导航:
 
<2011年12月>
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567

导航

统计

常用链接

留言簿(54)

随笔分类

随笔档案

文章分类

文章档案

搜索

最新评论

阅读排行榜

评论排行榜