SQL Server监控系列之调优排错

数据库 SQL Server
今天我们将介绍的是SQL Server监控系列之调优排错,SQL Server内部运行的可见性是的查询调整、优化和综合排查成为可能!这一节主要和大家说说SQL Server跟踪(SQL Server Profile)的一些监控方式和途径。

使用场景

记得某次给一家公司调优的时候,负责人发给我一堆业务的T-SQL脚本,我面对海量脚本还是从容,虽然不了解内部复杂的业务,但是我们得专注问题的关键 “慢”,我们根据查询的“慢”把他们筛选出来,一一调式优化,不就迅速解决问题吗?三天后,负责人含泪握着我的手,哥们辛苦了,查询响应得到了质的改善。

跟踪提供者

SQL Server 为我们两者提供跟踪的方式:一种是一个物理文件(可保存在本机或者UNC网络路径),一种是行集。对于后者大家应该比较熟悉

image

这个工具在 SSMS 的 工具 –> SQL Profile

AB490696-8C76-4819-A08D-1D14E3E828BA

详细的我暂时不介绍,先说说两者的区别和类同点 DIFFAndSame(行集,文件提供者)。

两者都是用类似Buffer来保存当前的事件数据,很明显是为了减少IO的压力,这样可以不阻塞和尽量不遗漏 事件数据,当Buffer 到达一定量时候可能才会Flush到磁盘或者发送到网络的终端(客户端)显示监控行集。

物理文件保存监控结果的方式的重要保证是不能遗漏任何事件,一旦IO降速的时候,可能会影响到整个T-SQL的执行情况。

  1. SELECT * FROM sys.dm_os_wait_stats 
  2. WHERE wait_type IN ('SQLTRACE_LOCK','IO_COMPLETION'); 

我使用这个语句来监控TRACE 和IO 完成对我当前机器的影响,我的某个客户的IO情况:                      

  1. wait_type   
  2.  waiting_tasks_count  
  3.  wait_time_ms  
  4.  max_wait_time_ms  
  5.  signal_wait_time_ms   
  6.    
  7.    
  8. IO_COMPLETION  
  9.  66030898  
  10.  24377499  
  11.  3634     
  12.  418960   
  13.    
  14.    
  15. SQLTRACE_LOCK  
  16.  12007  
  17.  175943  
  18.  1001  
  19.  1281 

因为我进行了大量的过滤,因此这个值还是能够接受的,影响不是特别大。

行结果集的方式,其实也是我们最熟悉的,就是使用SQL Server Profile监控GUI 直接展现给我们看到的。但是,我是非常不建议使用的,首先如果Buffer满了,它有一定的延迟,可能会抛弃事件已清空缓存区继续接受事件,而事件没有发送到Client,也没有写到物理文件,自然就丢失了。比如,SQL Server Profile 在DB服务器进行监控,因为高负载的机器再用来展示,很有可能就会丢失事件,另外物理文件方式,其实是接受一个足够大的Buffer,进行的大块写操作,性能是优于行集的。

image(行集)

保密性原则

SQL Server的安全特性会自动过滤 包含隐私的数据,比如密码。我在我的SSMS中执行了如下的语句:

  1. EXEC sp_password 'pp','pp1','sa'

这是修改sa帐号密码的系统sp,我打开了SQL Server Profile –> 选择了T-SQL 监控模版

image

然后执行上面的存储过程,监控结果:

image

监控结果:--*sp_password----------------------------

SQL Server Profile

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

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

image(我随便选择了一个耗时 = 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的跟踪结果):

  1. CREATE PROC [dbo].[sp_trace_sql_durtion]  
  2.     @DatabaseName nvarchar(128),  
  3.     @Seconds bigint,  
  4.     @FilePath nvarchar(260)  
  5. AS 
  6. BEGIN 
  7. DECLARE @rc int,@TraceID int,@MaxFileSize bigint;  
  8. SET @MaxFileSize = 5;  
  9.    
  10. EXEC sp_trace_create @TraceID OUTPUT,2,@FilePath,@MaxFileSize,NULL;  
  11.    
  12. IF @rc != 0   
  13.     RETURN;  
  14.    
  15. DECLARE @On bit;  
  16. SET @On = 1;  
  17.    
  18. EXEC sp_trace_setevent @TraceID,10,35,@On;  
  19. EXEC sp_trace_setevent @TraceID,10,1,@On;  
  20. EXEC sp_trace_setevent @TraceID,10,13,@On;  
  21. EXEC sp_trace_setevent @TraceID,41,35,@On;  
  22. EXEC sp_trace_setevent @TraceID,41,1,@On;  
  23. EXEC sp_trace_setevent @TraceID,41,13,@On;  
  24.    
  25. SET @Seconds = @Seconds * 1000000;  
  26.    
  27. EXEC sp_trace_setfilter @TraceID,13,0,4,@Seconds;  
  28.    
  29. IF @DatabaseName IS NOT NULL 
  30.     EXEC sp_trace_setfilter @TraceID,35,0,0,@DatabaseName  
  31.    
  32. EXEC sp_trace_setstatus @TraceID,1  
  33. SELECT TraceID = @TraceID;  
  34.    
  35. END 

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

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

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

来查看行方式的结果。

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

监控异常

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

  1. CREATE PROC [dbo].[sp_trace_sql_exception]  
  2.     @FilePath nvarchar(260)  
  3. AS 
  4. DECLARE @rc int,@TraceID int,@Maxfilesize bigint 
  5. SET @maxfilesize = 5   
  6.    
  7.    
  8. EXEC @rc = sp_trace_create @TraceID output, 2, @FilePath, @Maxfilesize, NULL   
  9. IF (@rc != 0)   
  10.     RETURN;  
  11.    
  12. DECLARE @on bit 
  13. SET @on = 1  
  14. EXEC sp_trace_setevent @TraceID, 33, 1, @on 
  15. EXEC sp_trace_setevent @TraceID, 33, 14, @on 
  16. EXEC sp_trace_setevent @TraceID, 33, 51, @on 
  17. EXEC sp_trace_setevent @TraceID, 33, 12, @on 
  18. EXEC sp_trace_setevent @TraceID, 11, 2, @on 
  19. EXEC sp_trace_setevent @TraceID, 11, 14, @on 
  20. EXEC sp_trace_setevent @TraceID, 11, 51, @on 
  21. EXEC sp_trace_setevent @TraceID, 11, 12, @on 
  22. EXEC sp_trace_setevent @TraceID, 13, 1, @on 
  23. EXEC sp_trace_setevent @TraceID, 13, 14, @on 
  24. EXEC sp_trace_setevent @TraceID, 13, 51, @on 
  25. EXEC sp_trace_setevent @TraceID, 13, 12, @on 
  26.    
  27. DECLARE @intfilter int,@bigintfilter bigint;  
  28.    
  29. EXEC sp_trace_setstatus @TraceID, 1  
  30.    
  31. SELECT TraceID=@TraceID  
  32. GOTO finish  
  33.    
  34. ERROR:   
  35. SELECT ErrorCode=@rc  
  36.    
  37. FINISH:  

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

默认跟踪和黑盒跟踪

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

可以通过

  1. EXEC sp_configure 'default trace enabled',0;  
  2. RECONFIGURE WITH OVERRIDE; 

来关闭默认跟踪。

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

image

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

  1. CREATE PROCEDURE sp_trace_blackbox  
  2.     @FilePath nvarchar(260)  
  3. AS 
  4. BEGIN 
  5.     DECLARE @TraceID int,@MaxFileSize bigint 
  6.     SET @MaxFileSize = 25;  
  7.     EXEC sp_trace_create @TraceID OUTPUT,8,@FilePath,@MaxFileSize  
  8.     EXEC sp_trace_setstatus @TraceID,1;  
  9.  
  10. END 

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

结尾

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

原文链接:http://www.cnblogs.com/bhtfg538/archive/2011/01/21/1939706.html

【编辑推荐】

  1. SQL Server 2000删除实战演习
  2. SQL Server存储过程的命名标准如何进行?
  3. 卸载SQL Server 2005组件的正确顺序
  4. 对SQL Server字符串数据类型的具体描述
  5. SQL Server数据类型的种类与应用
责任编辑:彭凡 来源: 博客园
相关推荐

2011-09-02 14:05:25

SQL Server性能调优

2009-01-08 19:14:37

服务器应用程序SQL Server

2023-11-28 08:43:48

2009-01-08 19:11:39

服务器应用程序SQL Server

2009-01-08 19:06:13

服务器应用程序SQL Server

2010-07-19 09:39:53

SQL Server

2010-07-19 13:35:51

SQL Server性

2017-07-21 08:55:13

TomcatJVM容器

2014-09-22 13:31:46

Linux

2023-03-30 07:52:46

SQLTiFlash调优

2023-11-10 09:29:30

MySQLExplain

2010-07-21 09:28:34

SQL Server

2020-11-09 07:34:49

JVM性能监控

2011-09-13 15:39:00

SQL Server性能调优

2021-11-07 23:49:19

SQL数据库工具

2009-11-17 13:45:12

Oracle SQL调

2019-11-01 08:49:07

JVM监控性能

2015-06-17 10:34:15

SQL Server性能调优

2017-10-17 14:02:30

jvm调优工具

2017-09-22 15:15:23

jvm调优命令
点赞
收藏

51CTO技术栈公众号