{"id":1917,"date":"2012-01-10T10:15:36","date_gmt":"2012-01-10T10:15:36","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=1917"},"modified":"2013-11-12T21:28:58","modified_gmt":"2013-11-12T21:28:58","slug":"%e6%ad%bb%e5%be%aa%e7%8e%af%e5%bc%95%e8%b5%b7%e7%9a%84%e8%bf%9e%e6%8e%a5%e6%b1%a0%e8%80%97%e5%b0%bd","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2012\/01\/10\/%e6%ad%bb%e5%be%aa%e7%8e%af%e5%bc%95%e8%b5%b7%e7%9a%84%e8%bf%9e%e6%8e%a5%e6%b1%a0%e8%80%97%e5%b0%bd\/","title":{"rendered":"\u6b7b\u5faa\u73af\u5f15\u8d77\u7684\u8fde\u63a5\u6c60\u8017\u5c3d"},"content":{"rendered":"
\n\t\u7528\u6237\u53cd\u5e94\u7cfb\u7edf\u54cd\u5e94\u6162\uff0c\u62a5\u8fde\u63a5\u6c60\u8d85\u8fc7\u6700\u5927\u8fde\u63a5\u6570\u9519\u8bef\uff0c\u4e0a\u53bb\u770b\u670d\u52a1\u5668\uff0cCPU\uff0c\u5185\u5b58\u90fd\u5f88\u6b63\u5e38\u3002\u8fc7\u4e00\u6bb5\u65f6\u95f4\u7cfb\u7edf\u53c8\u81ea\u52a8\u6062\u590d\u3002\u901a\u8fc7\u4ee5\u4e0b\u65b9\u6cd5\u6392\u67e5\u8fde\u63a5\u6570\uff1a<\/div>\n
\n\tsp_configure ‘user connections’<\/div>\n
\n\t—-\u67e5\u770b\u7cfb\u7edf\u8fde\u63a5\u6570\uff1a<\/div>\n
\n\tSELECT d.name,b.login_name,COUNT(1) ccount,m.text  FROM<\/div>\n
\n\tsys.dm_exec_connections a<\/div>\n
\n\tJOIN sys.dm_exec_sessions b ON a.session_id = b.session_id –AND b.login_name IN('OA','brm')<\/div>\n
\n\tJOIN sys.databases d ON b.database_id = d.database_id<\/div>\n
\n\tcross apply  sys.dm_exec_sql_text(a.most_recent_sql_handle)m<\/div>\n
\n\tGROUP BY d.name,b.login_name,m.text<\/div>\n
\n\tORDER BY d.name,b.login_name,ccount DESC<\/p>\n

\t—-\u6216\u8005\u770b\u660e\u7ec6<\/p>\n

\n
\n\t\t\tSELECT b.session_id,d.name,b.login_name,m.text,<\/div>\n
\n\t\t\ta.client_net_address,a.connect_time,a.last_read,a.last_write<\/div>\n
\n\t\t\tFROM sys.dm_exec_connections a<\/div>\n
\n\t\t\tJOIN sys.dm_exec_sessions b ON a.session_id = b.session_id –AND b.login_name IN('OA','brm')<\/div>\n
\n\t\t\tJOIN sys.databases d ON b.database_id = d.database_id<\/div>\n
\n\t\t\tcross apply  sys.dm_exec_sql_text(a.most_recent_sql_handle)m<\/div>\n
\n\t\t\twhere client_net_address != '<local machine>'<\/div>\n
\n\t\t\torder by a.last_read desc<\/div>\n<\/p><\/div>\n<\/div>\n
\n\t <\/div>\n
\n\t\u53d1\u73b0\u6392\u5728\u7b2c\u4e00\u4f4d\u7684\u8fde\u63a5\u6570\u666f\u6709\u6570\u5343\uff0c\u610f\u5473\u7740\u4e00\u4e2a\u4f1a\u8bdd\u5185\u53d1\u751f\u4e86\u4e0a\u5343\u6b21\u7684\u8fde\u63a5\uff0c\u8fd9\u662f\u5f88\u5f02\u5e38\u7684\uff0c\u5206\u6790sql\u8bed\u53e5\u6765\u6e90\u5b9a\u4f4d\u5f02\u5e38sql\u8bed\u53e5\uff1a<\/div>\n
\n\tSELECT * FROM sys.dm_exec_sql_text(0x03000600B1C9EA687B3F2801CA9F00000100000000000000)<\/div>\n
\n\t–\u770bsql\u8bed\u53e5<\/div>\n
\n\tSELECT TOP 50<\/div>\n
\n\tqs.total_worker_time\/qs.execution_count\/1000. as [\u5e73\u5747\u6d88\u8017CPU \u65f6\u95f4(ms)],<\/div>\n
\n\ttotal_worker_time\/1000 AS [\u603b\u6d88\u8017CPU \u65f6\u95f4(ms)],execution_count [\u8fd0\u884c\u6b21\u6570],<\/div>\n
\n\tSUBSTRING(qt.text,qs.statement_start_offset\/2+1,<\/div>\n
\n\t(case when qs.statement_end_offset = -1<\/div>\n
\n\tthen DATALENGTH(qt.text)<\/div>\n
\n\telse qs.statement_end_offset end -qs.statement_start_offset)\/2 + 1)<\/div>\n
\n\tas [\u67e5\u8be2\u8bed\u53e5], qt.text [\u6240\u5728\u5b58\u50a8\u8fc7\u7a0b],<\/div>\n
\n\tqt.dbid, dbname=db_name(qt.dbid),<\/div>\n
\n\tqt.objectid,object_name(qt.objectid,qt.dbid) ObjectName<\/div>\n
\n\tFROM sys.dm_exec_query_stats qs<\/div>\n
\n\tcross apply sys.dm_exec_sql_text(qs.sql_handle) as qt<\/div>\n
\n\tWHERE qs.last_execution_time >=’2012-01-10 10:00′ AND dbid = 6<\/div>\n
\n\tAND qs.sql_handle = ’0x03000600B1C9EA687B3F2801CA9F00000100000000000000′<\/div>\n
\n\t\u53d1\u73b0\u6700\u5927\u8fde\u63a5\u6570\u4e3a596\uff0c599\uff1f<\/div>\n","protected":false},"excerpt":{"rendered":"

\u7528\u6237\u53cd\u5e94\u7cfb\u7edf\u54cd\u5e94\u6162\uff0c\u62a5\u8fde\u63a5\u6c60\u8d85\u8fc7\u6700\u5927\u8fde\u63a5\u6570\u9519\u8bef\uff0c\u4e0a\u53bb\u770b\u670d\u52a1\u5668\uff0cCPU\uff0c\u5185\u5b58\u90fd\u5f88\u6b63\u5e38\u3002\u8fc7\u4e00\u6bb5\u65f6\u95f4\u7cfb\u7edf\u53c8\u81ea\u52a8\u6062\u590d\u3002 […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13],"tags":[],"class_list":["post-1917","post","type-post","status-publish","format-standard","hentry","category-13"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/1917","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=1917"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/1917\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=1917"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=1917"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=1917"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}