{"id":46,"date":"2022-04-29T14:15:49","date_gmt":"2022-04-29T06:15:49","guid":{"rendered":"https:\/\/www.yanyucz.cn\/?p=46"},"modified":"2022-04-29T14:18:10","modified_gmt":"2022-04-29T06:18:10","slug":"sqlserver%e6%80%a7%e8%83%bd%e4%bc%98%e5%8c%96","status":"publish","type":"post","link":"https:\/\/www.yanyucz.cn\/?p=46","title":{"rendered":"SQLServer\u6027\u80fd\u4f18\u5316"},"content":{"rendered":"\n<ul><li>\u5f00\u542f\u6267\u884c\u4fe1\u606f\u67e5\u8be2<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code><strong>-- \u8bbe\u7f6e\u67e5\u770b\u8bed\u53e5\u5f71\u54cd\u884c\u6570<\/strong>\nSET NOCOUNT OFF\n-- \u8bbe\u7f6e\u67e5\u770b\u6267\u884c\u65f6\u95f4\u548cCPU\u5360\u7528\u65f6\u95f4\nSET STATISTICS TIME ON\n-- \u8bbe\u7f6e\u67e5\u8be2\u5bf9IO\u7684\u64cd\u4f5c\u60c5\u51b5\nSET STATISTICS IO ON\n\u67e5\u8be2\u6b63\u5728\u6267\u884c\u7684\u8bed\u53e5\n\u6ce8\u610f\uff1aSqlServer\u6570\u636e\u5e93\u65f6\u95f4\u4ee5\u5fae\u79d2\u4e3a\u5355\u4f4d\uff0c\u53731\u79d2=1000\u6beb\u79d2(ms)=1000*1000\u5fae\u79d2\nSET NOCOUNT OFF;\nSET STATISTICS TIME ON;\nSET STATISTICS IO  ON;\n\n<strong>-- \u67e5\u8be2\u6b63\u5728\u6267\u884c\u7684\u8bed\u53e5<\/strong>\nSELECT TOP 10   \n    st.text AS &#91;\u6267\u884c\u8bed\u53e5]                 \n    ,qs.execution_count &#91;\u6267\u884c\u6b21\u6570] \n    ,qs.creation_time AS &#91;\u6267\u884c\u65f6\u95f4] \n    ,(qs.total_logical_reads + qs.total_logical_writes) AS &#91;\u903b\u8f91\u8bfb\u5199]\n    ,qs.total_logical_reads AS &#91;\u903b\u8f91\u8bfb\u53d6]\n    ,qs.total_logical_writes AS &#91;\u903b\u8f91\u5199\u5165]\n    ,qs.total_physical_reads &#91;\u7269\u7406\u8bfb\u53d6]\n    ,qs.total_elapsed_time AS &#91;\u6267\u884c\u8017\u65f6]\n    ,qs.total_worker_time AS &#91;CPU\u8017\u65f6]\nFROM sys.dm_exec_query_stats AS qs\nCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st\nORDER BY qs.creation_time DESC\n-- \u67e5\u8be2\u5f53\u524d\u7f13\u5b58\u4e2d\u6279\u5904\u7406\u6216\u5b58\u50a8\u8fc7\u7a0b\u5360\u7528CPU\u8d44\u6e90\u7684\u60c5\u51b5\nSELECT TOP 50   \n    qs.sql_handle\n    ,COUNT(*) AS &#91;\u8bed\u53e5\u4e2a\u6570]\n    ,SUM(qs.execution_count) AS &#91;\u6267\u884c\u6b21\u6570]\n    ,SUM(qs.total_worker_time)\/1000.0 AS &#91;CPU\u8017\u65f6]\n    ,SUM(qs.total_elapsed_time)\/1000.0 AS &#91;\u6267\u884c\u8017\u65f6]\n    ,SUM(qs.total_logical_reads) AS &#91;\u903b\u8f91\u8bfb\u53d6]\n    ,SUM(qs.total_logical_writes) AS &#91;\u903b\u8f91\u5199\u5165]\n    ,SUM(qs.total_physical_reads) AS &#91;\u7269\u7406\u8bfb\u53d6]\nFROM sys.dm_exec_query_stats AS qs\nGROUP BY qs.sql_handle\nORDER BY 4 DESC\n-<strong>-\u67e5\u8be2\u6267\u884c\u8017\u65f6\u7684\u8bed\u53e5<\/strong>\nSELECT \n    SS.sum_execution_count\n    ,SS.sum_total_elapsed_time\n    ,SS.sum_total_worker_time\n    ,SS.sum_total_logical_reads\n    ,SS.sum_total_logical_writes  \n    ,T.text\nFROM (\n    SELECT \n        S.plan_handle\n        ,SUM(S.execution_count) sum_execution_count\n        ,SUM(S.total_elapsed_time) sum_total_elapsed_time\n        ,SUM(S.total_worker_time) sum_total_worker_time\n        ,SUM(S.total_logical_reads) sum_total_logical_reads\n        ,SUM(S.total_logical_writes) sum_total_logical_writes\n    FROM SYS.dm_exec_query_stats S\n    GROUP BY S.plan_handle\n) AS SS\nCROSS APPLY SYS.dm_exec_sql_text(SS.plan_handle) T\nORDER BY sum_total_logical_reads DESC\n-<strong>-\u67e5\u8be2CPU\u6d88\u8017\u6700\u9ad8\u7684SQL\u8bed\u53e5<\/strong>\nSELECT TOP 10 \n    TEXT AS &#91;SQL]\n    ,last_execution_time AS &#91;\u6700\u540e\u6267\u884c\u65f6\u95f4]\n    ,(total_logical_reads + total_physical_reads + total_logical_writes) \/ execution_count AS &#91;IO\u5e73\u5747\u8bfb\u5199\u6b21\u6570]\n    ,(total_worker_time \/ execution_count) \/ 1000000.0 AS &#91;CPU\u5e73\u5747\u6267\u884c\u79d2\u6570]\n    ,(total_elapsed_time \/ execution_count) \/ 1000000.0 AS &#91;\u5e73\u5747\u6267\u884c\u79d2\u6570]\n    ,execution_count AS &#91;\u6267\u884c\u6b21\u6570]\n    ,qs.total_physical_reads AS &#91;\u7269\u7406\u8bfb\u53d6\u6b21\u6570]\n    ,qs.total_logical_writes AS &#91;\u903b\u8f91\u5199\u5165\u6b21\u6570]\n    ,qp.query_plan AS &#91;\u67e5\u8be2\u8ba1\u5212]\nFROM sys.dm_exec_query_stats qs\nCROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st\nCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp\nORDER BY total_elapsed_time \/ execution_count DESC\n<strong>--\u67e5\u627e\u6267\u884c\u6b21\u6570\u6700\u591a\u7684SQL\u8bed\u53e5<\/strong>\nDECLARE @begin_time DATETIME = '2018-01-01 00:00:00';\nDECLARE @execute_count_limit INT = 500;\n\nWITH tbl AS (\n    SELECT  \n        --\u6267\u884c\u6b21\u6570 \n        QS.execution_count \n        ,SUBSTRING(\n            ST.text\n            ,(QS.statement_start_offset \/ 2) + 1\n            ,((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE QS.statement_end_offset END - QS.statement_start_offset)\/2) + 1 \n        ) AS statement_text\n        ,ST.text AS &#91;text]\n        ,QS.last_elapsed_time\n        ,QS.min_elapsed_time\n        ,QS.max_elapsed_time\n        ,QS.total_worker_time\n        ,QS.last_worker_time\n        ,QS.max_worker_time\n        ,QS.min_worker_time \n    FROM sys.dm_exec_query_stats QS \n    CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST \n    WHERE 1=1\n    AND QS.last_execution_time &gt; @begin_time\n    AND QS.execution_count &gt; @execute_count_limit\n    --AND ST.text LIKE '%%' \n    --ORDER BY QS.execution_count DESC\n)\n\nSELECT \n    MAX(execution_count) max_execution_count\n    ,&#91;text]\nFROM tbl\nWHERE 1=1 \nAND &#91;text] NOT LIKE '%sp_MSupd_%' \nAND &#91;text] NOT LIKE '%sp_MSins_%' \nAND &#91;text] NOT LIKE '%sp_MSdel_%' \nGROUP BY &#91;text]\nORDER BY 1 DESC\n<strong>--\u67e5\u627e\u903b\u8f91\u8bfb\u53d6\u6700\u9ad8\u7684\u67e5\u8be2(\u5b58\u50a8\u8fc7\u7a0b)<\/strong>\nSELECT TOP 25 \n    p.name AS &#91;\u5b58\u50a8\u8fc7\u7a0b]\n    ,deps.total_logical_reads AS &#91;\u903b\u8f91\u8bfb\u603b\u6b21\u6570] \n    ,deps.total_logical_reads \/ deps.execution_count AS &#91;\u903b\u8f91\u8bfb\u5e73\u5747\u6b21\u6570]\n    ,deps.execution_count &#91;\u603b\u6267\u884c\u6b21\u6570]\n    ,ISNULL(deps.execution_count \/ DATEDIFF(Second, deps.cached_time, GETDATE()), 0) AS &#91;\u6bcf\u79d2\u8c03\u7528\u6b21\u6570] \n    ,deps.total_elapsed_time\/1000\/1000.0 AS &#91;\u603b\u6d88\u8017\u65f6\u957f]\n    ,deps.total_elapsed_time\/1000\/1000.0\/deps.execution_count AS &#91;\u5e73\u5747\u6d88\u8017\u65f6\u957f]\n    ,deps.cached_time AS &#91;\u7f13\u5b58\u65f6\u95f4]\nFROM sys.procedures AS p\nINNER JOIN sys.dm_exec_procedure_stats AS deps ON p.&#91;Object_id] = deps.&#91;Object_id]\nWHERE 1=1\nAND deps.Database_id = DB_ID()\nORDER BY deps.total_elapsed_time DESC;\n-- <strong>\u6392\u67e5\u5386\u53f2\u6162\u67e5\u8be2<\/strong>\nSELECT TOP 50\n    DB_NAME(qt.dbid) AS &#91;\u6570\u636e\u5e93]\n    ,OBJECT_NAME(qt.objectid, qt.dbid) AS &#91;\u5bf9\u8c61\u540d]\n    ,qs.creation_time AS &#91;\u521b\u5efa\u65f6\u95f4]\n    ,qs.last_execution_time AS &#91;\u6700\u8fd1\u6267\u884c\u65f6\u95f4]\n    ,qs.last_elapsed_time AS &#91;\u6700\u8fd1\u6267\u884c\u8017\u65f6]\n    ,qs.last_worker_time AS &#91;\u6700\u8fd1CPU\u8017\u65f6]\n    ,qs.last_rows AS &#91;\u6700\u8fd1\u5f71\u54cd\u884c\u6570]\n    ,qs.execution_count AS &#91;\u6267\u884c\u6b21\u6570]\n    ,qs.total_elapsed_time AS &#91;\u7d2f\u8ba1\u6267\u884c\u8017\u65f6]\n    ,(qs.total_elapsed_time \/ qs.execution_count) AS &#91;\u5e73\u5747\u6267\u884c\u8017\u65f6]\n    ,qs.max_elapsed_time AS &#91;\u6700\u5927\u6267\u884c\u8017\u65f6]\n    ,qs.total_worker_time AS &#91;\u7d2f\u8ba1CPU\u8017\u65f6]\n    ,(qs.total_worker_time \/ qs.execution_count) AS &#91;\u5e73\u5747CPU\u8017\u65f6]\n    ,qs.max_worker_time AS &#91;\u6700\u5927CPU\u8017\u65f6]\n    ,(qs.total_logical_reads + qs.total_logical_writes) AS &#91;\u7d2f\u8ba1\u903b\u8f91\u8bfb\u5199]\n    ,(qs.total_logical_reads + qs.total_logical_writes)\/qs.execution_count AS &#91;\u5e73\u5747\u903b\u8f91\u8bfb\u5199]\n    ,qs.min_rows AS &#91;\u6700\u5c0f\u5f71\u54cd\u884c\u6570]\n    ,qs.max_rows AS &#91;\u6700\u5927\u5f71\u54cd\u884c\u6570]\n    ,qs.total_rows AS &#91;\u7d2f\u8ba1\u5f71\u54cd\u884c\u6570]\n    ,SUBSTRING(\n        qt.text,\n        (qs.statement_start_offset\/2) + 1,     \n        ((\n        CASE WHEN qs.statement_end_offset = -1\n        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2\n        ELSE qs.statement_end_offset\n        END - qs.statement_start_offset\n        )\/2) + 1\n    ) AS &#91;\u72ec\u7acb\u67e5\u8be2]\n    ,qt.text AS &#91;\u7236\u7ea7\u67e5\u8be2]\n    ,qp.query_plan AS &#91;\u67e5\u8be2\u8ba1\u5212]\nFROM sys.dm_exec_query_stats qs\nCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt\nCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp\nWHERE 1=1\nAND qt.dbid IS NOT NULL\nAND DB_NAME(qt.dbid)!='msdb'\nORDER BY 4 DESC\n\n-<strong>- \u7cbe\u7b80\u7248 <\/strong>\nSELECT TOP 100\n    DB_NAME(qt.dbid) AS &#91;\u6570\u636e\u5e93]\n    ,OBJECT_NAME(qt.objectid, qt.dbid) AS &#91;\u5bf9\u8c61\u540d]\n    ,qs.execution_count AS &#91;\u6267\u884c\u6b21\u6570]\n    ,qs.total_worker_time\/1000\/1000 AS &#91;CPU\u603b\u6d88\u8017\u79d2\u6570]\n    ,qs.total_worker_time\/qs.execution_count\/1000\/1000.0 AS &#91;CPU\u5e73\u5747\u6d88\u8017\u79d2\u6570]\n    ,max_worker_time\/1000\/1000.0 AS &#91;CPU\u6700\u5927\u6d88\u8017\u79d2\u6570]\n    ,SUBSTRING(\n        qt.text\n        ,qs.statement_start_offset\/2+1\n        ,(CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END -qs.statement_start_offset)\/2 + 1\n    ) AS &#91;\u5254\u9664\u6ce8\u91ca]\n    ,qt.text &#91;\u5b8c\u6574\u8bed\u53e5]\n    ,last_execution_time AS &#91;\u6700\u540e\u6267\u884c\u65f6\u95f4]\nFROM sys.dm_exec_query_stats qs \nWITH(NOLOCK) CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt\nWHERE 1=1\nAND qs.execution_count &gt; 0 \n-- AND qs.total_worker_time\/qs.execution_count\/1000 &gt; 1\nAND OBJECT_NAME(qt.objectid, qt.dbid) IS NOT NULL\nAND DB_NAME(qt.dbid) != 'msdb'\nORDER BY qs.execution_count DESC\n--\u6392\u67e5\u5386\u53f2\u6162\u67e5\u8be2\nSELECT TOP 50\n    (qs.total_logical_reads + qs.total_logical_writes) AS &#91;\u903b\u8f91\u8bfb\u5199]\n    ,(qs.total_logical_reads + qs.total_logical_writes)\/qs.execution_count AS &#91;\u5e73\u5747\u8bfb\u5199]\n    ,qs.execution_count AS &#91;\u6267\u884c\u6b21\u6570]\n    ,SUBSTRING(\n        qt.text,\n        (qs.statement_start_offset\/2) + 1,     \n        ((\n        CASE WHEN qs.statement_end_offset = -1\n        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2\n        ELSE qs.statement_end_offset\n        END - qs.statement_start_offset\n        )\/2) + 1\n    ) AS &#91;\u72ec\u7acb\u67e5\u8be2]\n    ,qt.text AS &#91;\u7236\u7ea7\u67e5\u8be2]\n    ,DB_NAME(qt.dbid) AS &#91;\u6570\u636e\u5e93]\n    ,qp.query_plan AS &#91;\u67e5\u8be2\u8ba1\u5212]\nFROM sys.dm_exec_query_stats qs\nCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt\nCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp\nWHERE 1=1\nAND qt.dbid IS NOT NULL\nAND DB_NAME(qt.dbid)!='msdb'\nORDER BY 2 DESC\n\n-<strong>- \u67e5\u8be2\u5f53\u524d\u6b63\u5728\u6267\u884c\u7684\u6162\u67e5\u8be2<\/strong>\nSELECT TOP 10\n    ST.transaction_id AS TransactionID\n    ,ST.session_id\n    ,DB_NAME(DT.database_id) AS DatabaseName\n    ,SES.host_name\n    ,SES.login_name\n    ,SES.status\n    ,AT.transaction_begin_time AS TransactionStartTime\n    ,S.text \n    ,C.connect_time \n    ,DATEDIFF(second, AT.transaction_begin_time, GETDATE()) \"exec_time(s)\" \n    ,DATEDIFF(minute, AT.transaction_begin_time, GETDATE()) AS Tran_run_time\n    ,CASE AT.transaction_type\n    WHEN 1 THEN 'Read\/Write Transaction'\n    WHEN 2 THEN 'Read-Only Transaction'\n    WHEN 3 THEN 'System Transaction'\n    WHEN 4 THEN 'Distributed Transaction'\n    END AS TransactionType \n    ,CASE AT.transaction_state\n    WHEN 0 THEN 'Transaction Not Initialized'\n    WHEN 1 THEN 'Transaction Initialized &amp; Not Started'\n    WHEN 2 THEN 'Active Transaction'\n    WHEN 3 THEN 'Transaction Ended'\n    WHEN 4 THEN 'Distributed Transaction Initiated Commit Process'\n    WHEN 5 THEN 'Transaction in Prepared State &amp; Waiting Resolution'\n    WHEN 6 THEN 'Transaction Committed'\n    WHEN 7 THEN 'Transaction Rolling Back'\n    WHEN 8 THEN 'Transaction Rolled Back'\n    END AS TransactionState\nFROM sys.dm_tran_session_transactions AS ST\nINNER JOIN sys.dm_tran_active_transactions AS AT ON ST.transaction_id = AT.transaction_id\nINNER JOIN sys.dm_tran_database_transactions AS DT ON ST.transaction_id = DT.transaction_id\nLEFT JOIN sys.dm_exec_connections AS C ON st.session_id = C.session_id\nLEFT JOIN sys.dm_exec_sessions AS SES ON C.session_id = SES.session_id\nCROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_Handle) S\nWHERE 1=1\nAND DATEDIFF(second, AT.transaction_begin_time, GETDATE()) &gt; 2\n\n<strong>\u67e5\u8be2\u6700\u8fd1\u4fee\u6539\u7684\u5b58\u50a8\u8fc7\u7a0b<\/strong>\nSELECT \n  Name\n  ,Create_date\n  ,Modify_Date \nFROM sys.objects \nWHERE 1=1\nAND TYPE in ('U','P', 'V','F', 'TR', 'FN') \nORDER BY Modify_Date DESC;\n\n<strong>--\u67e5\u8be2\u6bcf\u79d2\u6b7b\u9501\u6570\u91cf<\/strong>\nSELECT *\nFROM sys.dm_os_performance_counters\nWHERE 1=1\nAND counter_name LIKE 'Number of Deadlocksc%';\n\n-<strong>-\u67e5\u8be2\u7b49\u5f85\u7c7b\u578b<\/strong>\nSELECT TOP 10 * FROM SYS.dm_os_wait_stats ORDER BY wait_time_ms DESC\n--\u901a\u8fc7\u7b49\u5f85\u7c7b\u578b\u5206\u6790\u8017\u65f6\u64cd\u4f5c \n\n<strong>\u67e5\u8be2\u6570\u636e\u5e93\u8fde\u63a5\u6570<\/strong>\nSELECT \n* \nFROM sysprocesses \nWHERE 1=1\nAND dbid IN(SELECT dbid FROM sysdatabases WHERE 1=1 AND name='WHGameUserDB') \n\n<strong>\u68c0\u7d22\u7d22\u5f15\u788e\u7247<\/strong>\nSELECT  \n    DB_NAME(ps.database_id) AS &#91;DbName] \n    ,OBJECT_NAME(ps.OBJECT_ID) AS &#91;DbObject]\n    ,ps.index_id AS &#91;IndexID]\n    ,b.name \n    ,ps.avg_fragmentation_in_percent\nFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ps\nINNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id\nWHERE 1=1\nAND ps.database_id = DB_ID('ReportServerTempDB')\nORDER BY ps.avg_fragmentation_in_percent DESC\n\n<strong>\u67e5\u770b\u5185\u5b58\u7ed3\u6784<\/strong>\nSELECT\n    (physical_memory_in_use_kb\/1024) AS MemoryUsed\n    ,(locked_page_allocations_kb\/1024) AS LockedPagesUsed\n    ,(total_virtual_address_space_kb\/1024) AS VASTotal\n    ,process_physical_memory_low\n    ,process_virtual_memory_low \nFROM sys.dm_os_process_memory;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u5f00\u542f\u6267\u884c\u4fe1\u606f\u67e5\u8be2<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[9],"tags":[],"_links":{"self":[{"href":"https:\/\/www.yanyucz.cn\/index.php?rest_route=\/wp\/v2\/posts\/46"}],"collection":[{"href":"https:\/\/www.yanyucz.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yanyucz.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yanyucz.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yanyucz.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=46"}],"version-history":[{"count":2,"href":"https:\/\/www.yanyucz.cn\/index.php?rest_route=\/wp\/v2\/posts\/46\/revisions"}],"predecessor-version":[{"id":48,"href":"https:\/\/www.yanyucz.cn\/index.php?rest_route=\/wp\/v2\/posts\/46\/revisions\/48"}],"wp:attachment":[{"href":"https:\/\/www.yanyucz.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=46"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yanyucz.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=46"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yanyucz.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=46"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}