想个法子找出性能差的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，数据库名、用户名、电脑名等就可以了，具体就看你自己的需要了。
好多的内容在系统视图sys.sysprocesses中，多数在这里用不到，但都是很有用的。不过，要找正在执行的语句，这里就没有，我们要在另两个系统视图中找，一个是sys.dm_exec_connections，如下：
使用以下方式就可以得到正在执行的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
第一个问题解决，正在执行什么SQL已可以知道，下边，我们就要从这些SQL中找出执行时间有问题的，比如执行时间大于1分钟的语句，那就要用上边说到的三个视图一起找，可以用以下的方式，你看到有很多的条件可以用，例如你要看或不看某个程序的、某个用户的、某台电脑的SQL，都是可以的。按需灵活设定。有个要注意的是，status要取活动的。
这样第二个问题也已解决，问题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定时执行就可以了，一有问题就会发出像以下的内容邮件，你就可以知道相关的内容了，接着你就去做分析优化吧！
最后说明一下，我们可以看到这个方式是基于执行时间的，B/S的程序都差不多是连接数据库执行后就断开的，如果有应用是持久连接的，那就不准确了。
这里只是总结个方法，各自参考参考。
原创，转载请注明出处： http://www.cnblogs.com/YIYUMENG
分类: MES , MS SQL , 工作中的麻烦 , 工作過程記錄與總結
作者： Leo_wl
出处： http://www.cnblogs.com/Leo_wl/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
版权信息