想个法子找出性能差的SQL

又近放假,发现自己近来有一种放假前做总结的习惯。刚好这两天一个系统总是会出现阵发性的性能问题。经过分析,发现这系统是之前赶出来的系统,什么是赶出来的系统,我想多数人都明白的,很多时候都无可奈何,不多说。现在出问题了,用户骂声一遍。

问题的原因在分析的过程中得到了证实,当时做这个系统时,在没有考虑性能的情况下做出了功能。但是谁又能保证呢?

所以要做些工作:

1. 找出执行时间长的SQL

2. 优化

 

本文只讲找性能差的SQL,不讲优化,因为优化要对特定的内容。有机会再做一下优化的经验总结。

好,看看要怎样找问题SQL,整个应用中所有的SQL都检查一下是不可能的,就算你真的那么做了,也不可能就没事了,有的看起来没有问题的句子,在生产环境中实际是有问题的。所以,我们就在生产运行过程中,在真实的使用中去想办法找出来。

从两个问题来入手:

问题一,你知道现在正在执行些什么SQL语句?

问题二,正在执行的SQL语句各运行了多长时间了?

 

在MS SQL中有一个系统视图sys.sysprocesses,它有以下的内容:

所有字段列表:spid kpid blocked waittype waittime lastwaittype waitresource dbid uid cpu physical_io memusage

login_time last_batch ecid open_tran status sid hostname program_name hostprocess cmd

nt_domain nt_username net_address net_library loginame context_info sql_handle

stmt_start stmt_end request_id

以下有图,但是我只想关心其中的一部分内容就够了,我要这个视图中的spid,数据库名、用户名、电脑名等就可以了,具体就看你自己的需要了。

image

 

image

 

image

 

好多的内容在系统视图sys.sysprocesses中,多数在这里用不到,但都是很有用的。不过,要找正在执行的语句,这里就没有,我们要在另两个系统视图中找,一个是sys.dm_exec_connections,如下:

 

image

 

image

使用以下方式就可以得到正在执行的SQL

   1:  SELECT c.session_id,t.text  
   2:  FROM sys.dm_exec_connections c   
   3:  CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t
 

image

第一个问题解决,正在执行什么SQL已可以知道,下边,我们就要从这些SQL中找出执行时间有问题的,比如执行时间大于1分钟的语句,那就要用上边说到的三个视图一起找,可以用以下的方式,你看到有很多的条件可以用,例如你要看或不看某个程序的、某个用户的、某台电脑的SQL,都是可以的。按需灵活设定。有个要注意的是,status要取活动的。

image

这样第二个问题也已解决,问题SQL已有办法得到,但是不可能时时去执行这个语句,因为我们会有几个人看各自己的部分,并且不可能什么语句都会刚好在有问题时让你发现,我想了一个办法,就是让数据库定时自动找出这些语句,通过DBMail发邮件出来。

通过试验,已初步完成了功能,完整的代码贴上,但是这只是说明我的方法的样例,在实际的应用中要考虑的还有很多问题,在这里就不说。

   1:  DECLARE @html NVARCHAR(MAX);  
   2:   
   3:  with tb  
   4:  as  
   5:  (  
   6:  SELECT c.session_id,t.text  
   7:  FROM sys.dm_exec_connections c  
   8:  CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t  
   9:  )  
  10:  select  distinct x.spid,DB_NAME(x.dbid) as dbname,x.last_batch,x.hostname,x.program_name,x.nt_domain,x.nt_username,tb.text
  11:  into #T  
  12:  from sys.sysprocesses x with (nolock)    
  13:  inner join tb on x.spid=tb.session_id  
  14:  where x.last_batch<dateadd(mi,-15,getdate())  
  15:  and x.program_name<>'Report Server'  
  16:  and nt_domain<>'NT AUTHORITY'
  17:  and x.status<>'sleeping' 
  18:  and x.hostname<>'HZCSRPTSRV' 
  19:    
  20:  if exists(select top 1 * from #T)  
  21:   begin  
  22:   SET @html = '<style type=''text/css''>.header {text-align:center;font-weight:bold;white-space:nowrap;color:#7f7e82;} .cell_text {vertical-align:top;text-align:left;color:#333333;} .cell_num {vertical-align:top;text-align:right;color:#333333;}</style>' ; 
  23:   
  24:   SET @html = @html + CAST(( SELECT 3 [@cellpadding],0 [@cellspacing],'font-family:verdana;font-size:10px;' [@style],1 [@border],  
  25:                                   ( SELECT [@class] = 'header', 'spid' [text()] FOR XML PATH('th'), TYPE) tr,  
  26:                                   ( SELECT [@class] = 'header', 'dbname' [text()] FOR XML PATH('th'), TYPE) tr,  
  27:                                   ( SELECT [@class] = 'header', 'last_batch' [text()] FOR XML PATH('th'), TYPE) tr,  
  28:                                   ( SELECT [@class] = 'header', 'hostname' [text()] FOR XML PATH('th'), TYPE) tr,  
  29:                                   ( SELECT [@class] = 'header', 'program_name' [text()] FOR XML PATH('th'), TYPE) tr,  
  30:                                   ( SELECT [@class] = 'header', 'nt_domain' [text()] FOR XML PATH('th'), TYPE) tr,  
  31:                                   ( SELECT [@class] = 'header', 'nt_username' [text()] FOR XML PATH('th'), TYPE) tr,  
  32:                                   ( SELECT [@class] = 'header', 'text' [text()] FOR XML PATH('th'), TYPE) tr,  
  33:                                   ( SELECT      
  34:                                     ( SELECT [@class] = 'cell_text', spid [text()] FOR XML PATH('td'), TYPE ),  
  35:                                   ( SELECT [@class] = 'cell_text', dbname [text()] FOR XML PATH('td'), TYPE ),  
  36:                                   ( SELECT [@class] = 'cell_text', last_batch [text()] FOR XML PATH('td'), TYPE ),  
  37:                                   ( SELECT [@class] = 'cell_text', hostname [text()] FOR XML PATH('td'), TYPE ),  
  38:                                   ( SELECT [@class] = 'cell_text', program_name [text()] FOR XML PATH('td'), TYPE ),  
  39:                                            ( SELECT [@class] = 'cell_text', nt_domain [text()] FOR XML PATH('td'), TYPE ),  
  40:                                            ( SELECT [@class] = 'cell_text', nt_username [text()] FOR XML PATH('td'), TYPE ),  
  41:                                            ( SELECT [@class] = 'cell_text', text [text()] FOR XML PATH('td'), TYPE )  
  42:                                     FROM (   
  43:                                    select spid,dbname,last_batch,hostname,program_name,nt_domain,nt_username,text  
  44:                                         from #T                                               
  45:                                           ) data   
  46:                                     FOR XML PATH('tr'), TYPE  
  47:                                   )   
  48:                              FOR XML PATH('table'), TYPE  
  49:                             ) AS VARCHAR(MAX));  
  50:   drop table #T;  
  51:  --Send Email    
  52:  EXEC msdb.dbo.sp_send_dbmail     
  53:   @profile_name = 'DBMAIL'    
  54:  ,@recipients = 'DarrenXie@QQ.com'     
  55:  ,@copy_recipients = 'QQQQQ@QQ.com'   
  56:  ,@subject = 'Camstar HZCSRPTSRV long runtime process'    
  57:  ,@body = @html    
  58:  ,@importance ='High'    
  59:  ,@body_format= 'HTML'  
  60:  end  
  61:   else  
  62:  begin  
  63:  drop table #T;  
  64:  end  

 

你用以上代码建立SP,再建立JOB定时执行就可以了,一有问题就会发出像以下的内容邮件,你就可以知道相关的内容了,接着你就去做分析优化吧!

image

 

image

 

最后说明一下,我们可以看到这个方式是基于执行时间的,B/S的程序都差不多是连接数据库执行后就断开的,如果有应用是持久连接的,那就不准确了。

这里只是总结个方法,各自参考参考。

 

原创,转载请注明出处:http://www.cnblogs.com/YIYUMENG

posted @ 2012-04-27 16:19  谢堂文(Darren Xie)  阅读(3079)  评论(6编辑  收藏  举报