{"id":1860,"date":"2011-08-28T07:24:44","date_gmt":"2011-08-28T07:24:44","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=1860"},"modified":"2012-05-29T09:29:52","modified_gmt":"2012-05-29T09:29:52","slug":"sql-server2005%e6%80%a7%e8%83%bd%e8%af%8a%e6%96%ad","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2011\/08\/28\/sql-server2005%e6%80%a7%e8%83%bd%e8%af%8a%e6%96%ad\/","title":{"rendered":"Sql Server2005\u6027\u80fd\u8bca\u65ad"},"content":{"rendered":"
\r\n\u5f15\u7528\uff1ahttp:\/\/technet.microsoft.com\/zh-cn\/library\/cc966540(en-us).aspx\r\nSql Server\u901f\u5ea6\u53d8\u6162\u95ee\u9898\u4e3b\u8981\u7531\u4e09\u4e2a\u65b9\u9762\u5f15\u8d77\uff0c\u53ef\u4ece\u8fd9\u4e09\u4e2a\u65b9\u9762\u5165\u624b\u5206\u6790\u95ee\u9898\r\n  1\uff0c\u8d44\u6e90\u74f6\u9888\uff1aCPU\u3001I\/O\u3001\u5185\u5b58\u7b49\u3002\r\n  2\uff0c\u4e34\u65f6\u8868\u74f6\u9888\uff1atempdb\u5404\u4e2a\u6570\u636e\u5e93\u5171\u7528\uff0c\u8d44\u6e90\u7d27\u5f20\u65f6\u4f1a\u5f15\u8d77\u6027\u80fd\u964d\u4f4e\u3002\r\n  3\uff0c\u7528\u6237\u6bd4\u8f83\u8017\u65f6\u7684sql\u67e5\u8be2\u3002\r\n\r\n\u4e00\uff0cCPU\u74f6\u9888\u5206\u6790\u3002\u74f6\u9888\u75c7\u72b6\uff1a\u6253\u5f00perfmon, \u76d1\u89c6 processor:% Processor Time\u82e5\u957f\u671f>80%\uff0c\u6216\u8005\u4efb\u52a1\u7ba1\u7406\u5668\uff0c\u8bf4\u660e\u53ef\u80fd\u662fCPU\u539f\u56e0\u3002\r\n1\uff0c\u5206\u6790\u662f\u5426\u662f\u67e5\u8be2\u8bed\u53e5\u5f15\u8d77\r\n \u8fd9\u91cc\u6d89\u53ca\u4e00\u4e2a\u52a8\u6001\u89c6\u56fe\uff1asys.dm_exec_query_stats\uff0c\u8fd4\u56de\u6bcf\u6761\u67e5\u8be2\u8bed\u53e5\u8fd0\u884c\u7684\u65f6\u95f4\r\n\u5173\u952e\u680f\u4f4d\u6709\uff1a\r\n 1\uff09sql_handle:\u8868\u793a\u5305\u542b\u67e5\u8be2\u7684\u6279\u67e5\u8be2\u6216\u5b58\u50a8\u8fc7\u7a0b\u7684\u6807\u8bb0,\u82e5sql_handle\u76f8\u540c\uff0c\u4ee3\u8868\u67e5\u8be2\u5728\u540c\u4e2a\u6279\u5904\u7406\u6216\u540c\u4e00\u4e2a\u5b58\u50a8\u8fc7\u7a0b\u5185\u3002\u53ef\u5c06\u6b64\u503c\u4f20\u5165    sys.dm_exec_sql_text\u4e2d\u83b7\u53d6\u67e5\u8be2\u8bed\u53e5\u3002\u540c\u4e2a\u5b58\u50a8\u8fc7\u7a0b\u6216\u6279\u5904\u7406\u8bed\u53e5\u7684sql_handle\u76f8\u540c\uff0c\u5982\u4f55\u5206\u8fa8\u662f\u54ea\u4e2a\u67e5\u8be2\u8bed\u53e5\u5f97\u5230\u4e86\u6267\u884c\uff0c\r\n\u53ef\u7528statement_start_offset\u3001statement_end_offset\u6765\u67e5\u51fa\u6b64\u67e5\u8be2\u8bed\u53e5\u7684\u771f\u5bb9\u3002\r\n2\uff09statement_start_offset\uff1a\u67e5\u8be2\u5728\u5176\u6279\u67e5\u8be2\u6216\u6301\u4e45\u5316\u5bf9\u8c61\u6587\u672c\u4e2d\u7684\u5f00\u59cb\u4f4d\u7f6e\r\n3\uff09statement_end_offset\uff1a\u67e5\u8be2\u5728\u5176\u6279\u67e5\u8be2\u6216\u6301\u4e45\u5316\u5bf9\u8c61\u6587\u672c\u4e2d\u7684\u7ed3\u675f\u4f4d\u7f6e\r\n4\uff09execution_count\uff1a \u8ba1\u5212\u81ea\u4e0a\u6b21\u7f16\u8bd1\u4ee5\u6765\u6240\u6267\u884c\u7684\u6b21\u6570\u3002\r\n5\uff09total_worker_time\uff1a\u6b64\u8ba1\u5212\u81ea\u7f16\u8bd1\u4ee5\u6765\u6267\u884c\u6240\u7528\u7684 CPU \u65f6\u95f4\u603b\u91cf\uff08\u4ee5\u5fae\u79d2\u4e3a\u5355\u4f4d\u62a5\u544a\uff0c\u4f46\u4ec5\u7cbe\u786e\u5230\u6beb\u79d2\uff09\u3002\r\n6\uff09last_execution_time\uff1a\u4e0a\u6b21\u5f00\u59cb\u6267\u884c\u8ba1\u5212\u7684\u65f6\u95f4\u3002\r\n\u6545\u5206\u6790\u8017CPU\u8bed\u53e5\u65b9\u6cd5\u53ef\u5f97\u51fa\uff1a\r\n\r\n--\u82e5\u8fd4\u56de\u7684dbname,objectname\u4e3a\u7a7a\uff0c\u4ee3\u8868\u662f\u52a8\u6001sql\u8bed\u53e5\uff0c\u4e0d\u662f\u5b58\u50a8\u8fc7\u7a0b\r\nSELECT \r\n    dbname,ObjectName,Sql,\r\n    SUM([\u603b\u6d88\u8017CPU \u65f6\u95f4(ms)]) [\u603b\u6d88\u8017CPU \u65f6\u95f4(ms)],\r\n    SUM([\u8fd0\u884c\u6b21\u6570])[\u8fd0\u884c\u6b21\u6570],[\u67e5\u8be2\u8bed\u53e5],[\u6240\u5728\u5b58\u50a8\u8fc7\u7a0b],\r\n    SUM([\u5e73\u5747\u6d88\u8017CPU \u65f6\u95f4(ms)])[\u5e73\u5747\u6d88\u8017CPU \u65f6\u95f4(ms)]    \r\n    FROM (\r\n    SELECT TOP (50)\r\n        qs.total_worker_time\/qs.execution_count\/1000. as [\u5e73\u5747\u6d88\u8017CPU \u65f6\u95f4(ms)],\r\n        total_worker_time\/1000 AS [\u603b\u6d88\u8017CPU \u65f6\u95f4(ms)],execution_count [\u8fd0\u884c\u6b21\u6570],\r\n       \r\n        dbo.SQLSig(SUBSTRING(qt.text,qs.statement_start_offset\/2+1, --\u5229\u7528sqlsig<\/a>\u51fd\u6570\u8fdb\u884c\u53c2\u6570\u5316\uff0c\u4ee5\u5c4f\u853d\u4f20\u6765\u7684\u53c2\u6570\uff0c\u4f7f\u4e4b\u6807\u51c6\u5316\r\n            (case when qs.statement_end_offset = -1\r\n            then DATALENGTH(qt.text)\r\n            else qs.statement_end_offset end -qs.statement_start_offset)\/2 + 1),4000)\r\n        as [\u67e5\u8be2\u8bed\u53e5], qt.text [\u6240\u5728\u5b58\u50a8\u8fc7\u7a0b],\r\n        qt.dbid, dbname=db_name(qt.dbid),\r\n        qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName\r\n    FROM sys.dm_exec_query_stats qs\r\n    cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt\r\n    ORDER BY [\u5e73\u5747\u6d88\u8017CPU \u65f6\u95f4(ms)] DESC\r\n    )M\r\n    GROUP BY [\u67e5\u8be2\u8bed\u53e5],[\u6240\u5728\u5b58\u50a8\u8fc7\u7a0b],dbid, dbname,objectid,ObjectName\r\n    ORDER BY [\u5e73\u5747\u6d88\u8017CPU \u65f6\u95f4(ms)] DESC\r\n\r\n\u6ce8\uff1a\u6d4b\u8bd5sys.dm_exec_query_stats \u65f6\u53ef\u5148\u6e05\u9664\u7f13\u5b58DBCC FREEPROCCACHE \u518d\u6267\u884c\u67e5\u8be2\u8bed\u53e5\u5206\u6790\u3002\r\n\r\n2\uff0c\u5206\u6790\u662f\u5426\u662f\u91cd\u65b0\u7f16\u8bd1\u5f15\u8d77\uff0c\u91cd\u65b0\u7f16\u8bd1\u6bd4\u8f83\u8d39\u65f6\u3002\r\n1\uff09perfmon:\r\n\r\n    SQL Server: SQL Statistics: Batch Requests\/sec :\u6bcf\u79d2\u949f\u63a5\u6536\u7684\u8bf7\u6c42\u6570\r\n\r\n    SQL Server: SQL Statistics: SQL Compilations\/sec\uff1a\u6bcf\u79d2\u949f\u7684\u7f16\u8bd1\u6570\r\n\r\n    SQL Server: SQL Statistics: SQL Recompilations\/sec\uff1a\u6bcf\u79d2\u949f\u7684\u91cd\u65b0\u7f16\u8bd1\u6570\r\n\r\n2\uff09profile:SP:Recompile \/ SQL:StmtRecompile.\r\n3\uff09sys.dm_exec_query_stats \u6709\u4e00\u4e2a\u680f\u4f4dplan_generation_num\uff0c\u8ba1\u5212\u7f16\u8bd1\u6b21\u6570\uff0c\u53ef\u7528\u6b64\u6765\u5206\u6790\u6700\u5e38\u7f16\u8bd1\u7684\u8ba1\u5212\u3002\r\nselect top 25\r\n    plan_generation_num,\r\n    SUBSTRING(qt.text,qs.statement_start_offset\/2+1,\r\n        (case when qs.statement_end_offset = -1\r\n        then DATALENGTH(qt.text)\r\n        else qs.statement_end_offset end -qs.statement_start_offset)\/2 + 1)\r\n        as stmt_executing,\r\n    qt.text,\r\n    execution_count,\r\n    sql_handle,\r\n    dbid,\r\n    db_name(dbid) DBName,\r\n    objectid,\r\n    object_name(objectid,dbid) ObjectName\r\nfrom sys.dm_exec_query_stats as qs\r\n    Cross apply sys.dm_exec_sql_text(sql_handle) qt\r\nwhere plan_generation_num >1\r\n--AND qs.last_execution_time >='2011-08-28 10:00' \u9650\u5b9a\u65f6\u95f4\r\norder by plan_generation_num desc\r\n\r\n\r\n\r\n\u4e8c\uff0c\u5185\u5b58\u74f6\u9888\u5206\u6790\u3002\u74f6\u9888\u75c7\u72b6\uff1a\u6253\u5f00perfmon, \u76d1\u89c6 Paging File: %Usage\u957f\u671f>80%\u3002Paging File:Usage: \u5206\u9875\u7a7a\u95f4\u4f7f\u7528\u767e\u5206\u7387\r\n\r\n\u4e09\uff0cI\/O\u74f6\u9888\u5206\u6790\uff1a\u75c7\u72b6\uff1a\u8ba1\u6570\u5668PhysicalDisk:%Disk Time >80% , Avg.Disk Queue Length:>2\r\n1\uff0c%Disk Time :\u6240\u9009\u78c1\u76d8\u9a71\u52a8\u5668\u5fd9\u4e8e\u4e3a\u8bfb\u6216\u5199\u5165\u8bf7\u6c42\u63d0\u4f9b\u670d\u52a1\u6240\u7528\u7684\u65f6\u95f4\u7684\u767e\u5206\u6bd4\u3002\r\nAvg. Disk Queue Length \u6307\u8bfb\u53d6\u548c\u5199\u5165\u8bf7\u6c42(\u4e3a\u6240\u9009\u78c1\u76d8\u5728\u5b9e\u4f8b\u95f4\u9694\u4e2d\u5217\u961f\u7684)\u7684\u5e73\u5747\u6570\u3002\r\n\u4e0d\u8fc7\uff0c\u82e5\u51fa\u73b0\u4e0a\u8ff0\u60c5\u51b5\uff0c\u4e5f\u53ef\u4ee5\u662f\u5185\u5b58\u4e0d\u8db3\u5f15\u8d77\u3002\r\n\r\n2\uff0csys.dm_exec_query_stats \u6709\u680f\u4f4dtotal_logical_writes\uff1a\u6b64\u8ba1\u5212\u81ea\u7f16\u8bd1\u540e\u5728\u6267\u884c\u671f\u95f4\u6240\u6267\u884c\u7684\u903b\u8f91\u5199\u5165\u603b\u6b21\u6570\u3002total_logical_reads\uff1a\u6b64\u8ba1\u5212\u81ea\u7f16\u8bd1\u540e\u5728\u6267\u884c\u671f\u95f4\u6240\u6267\u884c\u7684\u903b\u8f91\u5199\u5165\u603b\u6b21\u6570\u3002\r\n\u6545\u67e5\u8be2\u6700\u8017I\/O\u7684\u8bed\u53e5\u4e3a\uff1a\r\n\r\nselect top 50\r\n(total_logical_reads\/execution_count) as [\u5e73\u5747\u903b\u8f91\u8bfb\u53d6\u6b21\u6570],\r\n(total_logical_writes\/execution_count) as [\u5e73\u5747\u903b\u8f91\u5199\u5165\u6b21\u6570],\r\n(total_physical_reads\/execution_count) as [\u5e73\u5747\u5bf9\u8c61\u8bfb\u53d6\u6b21\u6570],\r\n Execution_count \u8fd0\u884c\u6b21\u6570,\r\nsubstring(qt.text,r.statement_start_offset\/2+1,\r\n(case when r.statement_end_offset = -1\r\nthen datalength(qt.text)\r\nelse r.statement_end_offset end - r.statement_start_offset)\/2+1) [\u8fd0\u884c\u8bed\u6cd5]\r\nfrom sys.dm_exec_query_stats  as r\r\n    cross apply sys.dm_exec_sql_text(r.sql_handle) as qt\r\n--WHERE r.last_execution_time >='2011-08-28 10:00' \u9650\u5b9a\u65f6\u95f4\r\norder by\r\n (total_logical_reads + total_logical_writes) Desc\r\n\r\n\u56db\uff0ctempdb\u74f6\u9888\u3002\u7a7a\u95f4\u8d44\u6e90\u8017\u5c3d\u5f15\u8d77\u3002\r\n\u67e5\u8be2\uff1a\r\nSelect\r\n    SUM (user_object_reserved_page_count)*8 as user_objects_kb,\r\n    SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,\r\n    SUM (version_store_reserved_page_count)*8  as version_store_kb,\r\n    SUM (unallocated_extent_page_count)*8 as freespace_kb\r\nFrom sys.dm_db_file_space_usage\r\nWhere database_id = 2\r\n\u5176\u4e2d\uff1afreespace_kb  \u8981\u8db3\u591f\u5927\u624d\u53ef\u3002\r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
\u5f15\u7528\uff1ahttp:\/\/technet.microsoft.com\/zh-cn\/library\/cc966540( […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-1860","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/1860","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=1860"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/1860\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=1860"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=1860"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=1860"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}