{"id":2755,"date":"2013-07-02T04:32:35","date_gmt":"2013-07-02T04:32:35","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2755"},"modified":"2013-11-25T02:44:11","modified_gmt":"2013-11-25T02:44:11","slug":"%e4%bb%8ewait%e8%a7%92%e5%ba%a6%e8%b0%83%e4%bc%98","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2013\/07\/02\/%e4%bb%8ewait%e8%a7%92%e5%ba%a6%e8%b0%83%e4%bc%98\/","title":{"rendered":"\u4ecewait\u89d2\u5ea6\u8c03\u4f18"},"content":{"rendered":"
\n\tWITH [Waits] AS<\/div>\n
\n\t    (SELECT<\/div>\n
\n\t        [wait_type],<\/div>\n
\n\t        [wait_time_ms] \/ 1000.0 AS [WaitS],<\/div>\n
\n\t        ([wait_time_ms] – [signal_wait_time_ms] ) \/ 1000.0 AS [ResourceS],<\/div>\n
\n\t        [signal_wait_time_ms] \/ 1000.0 AS [SignalS],<\/div>\n
\n\t        [waiting_tasks_count] AS [WaitCount],<\/div>\n
\n\t        100.0 * [wait_time_ms] \/ SUM ( [wait_time_ms]) OVER() AS [Percentage],<\/div>\n
\n\t        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC ) AS [RowNum]<\/div>\n
\n\t    FROM sys.dm_os_wait_stats<\/div>\n
\n\t    WHERE [wait_type] NOT IN (<\/div>\n
\n\t        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',<\/div>\n
\n\t        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',<\/div>\n
\n\t        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',<\/div>\n
\n\t        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',<\/div>\n
\n\t        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',<\/div>\n
\n\t        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',<\/div>\n
\n\t        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',<\/div>\n
\n\t        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',<\/div>\n
\n\t        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',<\/div>\n
\n\t        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',<\/div>\n
\n\t        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',<\/div>\n
\n\t        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',<\/div>\n
\n\t        N'DIRTY_PAGE_POLL', &nbsp; N'SP_SERVER_DIAGNOSTICS_SLEEP')<\/div>\n
\n\t    )<\/div>\n
\n\tSELECT<\/div>\n
\n\t    [W1]. [wait_type] AS [WaitType],<\/div>\n
\n\t    CAST ([W1]. [WaitS] AS DECIMAL( 14, 2 )) AS [Wait_S],<\/div>\n
\n\t    CAST ([W1]. [ResourceS] AS DECIMAL( 14, 2 )) AS [Resource_S],<\/div>\n
\n\t    CAST ([W1]. [SignalS] AS DECIMAL( 14, 2 )) AS [Signal_S],<\/div>\n
\n\t    [W1]. [WaitCount] AS [WaitCount],<\/div>\n
\n\t    CAST ([W1]. [Percentage] AS DECIMAL( 4, 2 )) AS [Percentage],<\/div>\n
\n\t    CAST (([W1]. [WaitS] \/ [W1]. [WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],<\/div>\n
\n\t    CAST (([W1]. [ResourceS] \/ [W1]. [WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],<\/div>\n
\n\t    CAST (([W1]. [SignalS] \/ [W1]. [WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]<\/div>\n
\n\tFROM [Waits] AS [W1]<\/div>\n
\n\tINNER JOIN [Waits] AS [W2]<\/div>\n
\n\t    ON [W2].[RowNum] <= [W1].[RowNum]<\/div>\n
\n\tGROUP BY [W1]. [RowNum], [W1].[wait_type] , [W1] .[WaitS],<\/div>\n
\n\t    [W1]. [ResourceS], [W1].[SignalS] , [W1] .[WaitCount], [W1].[Percentage]<\/div>\n
\n\tHAVING SUM ([W2] .[Percentage]) – [W1].[Percentage] < 95 ; — percentage threshold<\/div>\n
\n\tGO<\/div>\n
\n\t <\/div>\n
\n\tyou can very easily come up with a way to persist the results every few hours or every day and do some time-series analysis to figure out trends or automatically spot problems as they start to happen. You can also use Performance Dashboard to see these graphically in 2005 and Data Collector in 2008. On SQL Server 2000 you can use DBCC SQLPERF (N’waitstats’).<\/div>\n
\n\t <\/div>\n
\n\t\u8fd9\u4e2asql\u53ef\u4ee5\u7528\u6765\u4ea7\u770b95%\u4ee5\u4e0a\u7684\u7b49\u5f85\u3002<\/p>\n

\t–\u67e5\u770b\u7b49\u5f85\u7c7b\u578b\u5bf9\u5e94\u7684sql<\/p>\n

\r\nif exists (select * from sys.objects where object_id = object_id(N'[dbo].[get_statements_from_waiter_list]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1) \r\n    drop procedure [dbo].[get_statements_from_waiter_list] \r\ngo \r\n \r\ncreate proc get_statements_from_waiter_list (@wait_type nvarchar(60)=NULL) \r\nas \r\nselect  \r\n        r.wait_type \r\n        ,r.wait_time \r\n        ,SUBSTRING(qt.text,r.statement_start_offset\/2,  \r\n            (case when r.statement_end_offset = -1  \r\n            then len(convert(nvarchar(max), qt.text)) * 2  \r\n            else r.statement_end_offset end -r.statement_start_offset)\/2)  \r\n        as query_text \r\n        ,qt.dbid, dbname=db_name(qt.dbid) \r\n        ,qt.objectid \r\n        ,r.sql_handle \r\n        ,r.plan_handle \r\nFROM sys.dm_exec_requests r \r\ncross apply sys.dm_exec_sql_text(r.sql_handle) as qt \r\nwhere r.session_id > 50 \r\n  and r.wait_type = isnull(upper(@wait_type),r.wait_type) \r\ngo \r\n \r\nexec get_statements_from_waiter_list \r\n<\/pre>\n<\/div>\n
\n\tDBCC SQLPERF (N'sys.dm_os_wait_stats' , CLEAR );\u7528\u6765\u6e05\u7a7a\u7b49\u5f85\u4fe1\u606f<\/div>\n
\n\t\u4f5c\u8005\u5bf9\u7ecf\u5e38\u78b0\u5230\u7684\u7b49\u5f85\u7c7b\u578b\u505a\u51fa\u4e86\u89e3\u91ca\uff1a<\/div>\n
\n\tCXPACKET:\u5728\u5e76\u53d1\u67e5\u8be2\u4e2d\uff0c\u67d0\u4e2a\u7ebf\u7a0b\u7b49\u5f85\u5176\u4ed6\u7ebf\u7a0b\u5b8c\u6210\u65f6\u51fa\u73b0\u3002\u53ef\u4ee5\u4f7f\u7528cost threshold for parallelism\uff0cmax degree of parallelism2\u4e2a\u53c2\u6570\u7684\u914d\u7f6e\uff0c\u6216\u8005\u8bbe\u7f6e\u8d44\u6e90\u8c03\u63a7\u5668\u6765\u51cf\u5c11\u7b49\u5f85\u7684\u53d1\u9001\uff0c\u4f46\u5f80\u5f80\u4e0d\u662f\u89e3\u51b3\u95ee\u9898\u7684\u6839\u672c\u65b9\u6cd5\u3002<\/div>\n
\n\t\u8fd9\u610f\u5473\u7740<\/div>\n
\n\t•\u53d1\u751f\u4e86\u5e76\u884c\u64cd\u4f5c<\/div>\n
\n\t•\u53d1\u751f\u4e86\u5e76\u884c\u6267\u884c,\u6216\u662f\u5e76\u884c\u6267\u884c\u4e2d\u7684\u4e00\u4e2aworker\u88ab\u963b\u585e<\/div>\n
\n\t\u4e0d\u8981\u671b\u6587\u751f\u4e49<\/div>\n
\n\t•\u4e0d\u8981\u5c06\u670d\u52a1\u5668\u7ea7\u522b\u7684MAXDOP\u8bbe\u7f6e\u4e3a1,\u4e5f\u5c31\u662f\u7981\u7528\u5e76\u884c
\n\t\u5f53\u60a8\u914d\u7f6e\u7684 MAXDOP \u503c\u65f6\uff0c\u8bf7\u9075\u5faa\u4ee5\u4e0b\u51c6\u5219\u3002<\/font><\/p>\n
\n\t\tSQL Server 2005 \u53ca\u66f4\u9ad8\u7248\u672c<\/h3>\n
    \n
  • \n\t\t\t\u5bf9\u4e8e\u4f7f\u7528\u8d85\u8fc7\u516b\u4e2a\u5904\u7406\u5668\u7684\u670d\u52a1\u5668\uff0c\u8bf7\u4f7f\u7528\u4e0b\u5217\u914d\u7f6e\uff1a<\/font><\/p>\n
    \n\t\t\t\tMAXDOP = 8<\/div>\n<\/li>\n
  • \n\t\t\t\u5bf9\u4e8e\u4f7f\u7528 8 \u4e2a\u6216\u66f4\u5c11\u7684\u5904\u7406\u5668\u7684\u670d\u52a1\u5668\uff0c\u8bf7\u4f7f\u7528\u4e0b\u5217\u914d\u7f6e\uff1a<\/font><\/p>\n
    \n\t\t\t\tMAXDOP = 0 \u5230N<\/var><\/font>

    \n\t\t\t\t

    \n\t\t\t\t\u6ce8\u610f<\/b><\/font>\u5728\u6b64\u914d\u7f6e\u4e2d\uff0c N<\/var>\u8868\u793a\u5904\u7406\u5668\u7684\u6570\u76ee\u3002<\/font><\/div>\n<\/li>\n
  • \n\t\t\t\u5bf9\u4e8e\u5177\u6709 NUMA \u914d\u7f6e\u7684\u670d\u52a1\u5668\uff0cMAXDOP \u4e0d\u5e94\u8d85\u8fc7\u5206\u914d\u7ed9\u6bcf\u4e2a NUMA \u8282\u70b9\u7684 Cpu \u7684\u6570\u91cf\u3002<\/li>\n
  • \n\t\t\t\u5bf9\u4e8e\u5df2\u542f\u7528\u8d85\u7ebf\u7a0b\u7684\u670d\u52a1\u5668\uff0cMAXDOP \u503c\u4e0d\u5e94\u8d85\u8fc7\u7269\u7406\u5904\u7406\u5668\u7684\u6570\u91cf\u3002<\/li>\n
  • \n\t\t\t\u5bf9\u4e8e\u670d\u52a1\u5668\u5177\u6709 NUMA \u914d\u7f6e\u548c\u542f\u7528\u8d85\u7ebf\u7a0b\u540e\uff0cMAXDOP \u503c\u4e0d\u5e94\u8d85\u8fc7\u6bcf\u4e2a NUMA \u8282\u70b9\u7684\u7269\u7406\u5904\u7406\u5668\u7684\u6570\u76ee\u3002<\/li>\n<\/ul>\n
    \n\t\t <\/div>\n<\/div>\n
    \n\t\u66f4\u591a\u5206\u6790\u75c7\u72b6\u548c\u89e3\u51b3\u65b9\u6848-PAGEIOLATCH_XX<\/div>\n
    \n\t•\u662f\u5426\u5b58\u5728PAGEIOLATCH_SH\u7b49\u5f85,\u8fd9\u610f\u5473\u7740\u5927\u8303\u56f4SCAN<\/div>\n
    \n\t•\u540c\u65f6\u4e5f\u89c2\u5bdf\u4e00\u4e0bACCESS_METHODS_DATASET_PARENTLatch\u548cACCESS_METHODS_SCAN_RANGE_GENERATOR LATCH<\/div>\n
    \n\t•\u68c0\u67e5\u5bfc\u81f4CXPACKET\u7684\u8bf7\u6c42\u6765\u67e5\u770b\u6267\u884c\u8ba1\u5212\u662f\u5426\u5408\u7406<\/div>\n
    \n\t•\u5176\u4e2d\u67d0\u4e2a\u5e76\u884c\u7ebf\u7a0b\u6267\u884c\u65f6\u95f4\u8fc7\u957f(\u4e5f\u5c31\u662f\u5176\u4e2d\u67d0\u4e2a\u7ebf\u7a0b\u4e0d\u662f\u7531\u4e8eCXPACKET\u963b\u585e)<\/div>\n
    \n\t\u53ef\u80fd\u7684\u539f\u56e0<\/div>\n
    \n\t•\u4ec5\u4ec5\u662f\u7531\u4e8e\u53d1\u751f\u4e86\u5e76\u884c<\/div>\n
    \n\t•\u7531\u4e8e\u7f3a\u5c11\u805a\u96c6\u7d22\u5f15\u6216\u662f\u4e0d\u51c6\u786e\u7684\u6267\u884c\u8ba1\u5212\u5bfc\u81f4\u626b\u63cf<\/div>\n
    \n\t•\u8fc7\u671f\u7684\u7edf\u8ba1\u4fe1\u606f<\/div>\n
    \n\t•Distinct\u7ed3\u679c\u96c6\u65e0\u6cd5\u9884\u4f30\u6267\u884c\u8ba1\u5212,\u5bfc\u81f4\u4e0d\u5408\u9002\u6267\u884c\u8ba1\u5212,\u4ece\u800c\u4ea7\u751fCXPACKET\u7b49\u5f85,\u89e3\u51b3\u529e\u6cd5\u662f\u4e34\u65f6\u8868(\u738b\u6210\u8f89)<\/div>\n
    \n\t\u5982\u679c\u7684\u786e\u662f\u95ee\u9898<\/div>\n
    \n\t•\u786e\u4fdd\u7edf\u8ba1\u4fe1\u606f\u662f\u6700\u65b0\u7684,\u5e76\u4e14\u5b58\u5728\u9002\u5f53\u7684\u7d22\u5f15<\/div>\n
    \n\t•\u8bbe\u7f6e\u67e5\u8be2\u7684MAXDOP<\/div>\n
    \n\t•\u8003\u8651MAXDOP=NUMA\u7684\u7269\u7406CPU\u6838\u6570<\/div>\n
    \n\t•\u5728\u8003\u8651\u5230\u8d1f\u8f7d\u7c7b\u578b(\u6df7\u5408)\u7684\u524d\u63d0\u4e0b,\u518d\u8bbe\u7f6e\u5b9e\u4f8b\u7684MAXDOP<\/div>\n
    \n\t•\u8003\u8651\u8bbe\u7f6e\u5c06”cost threshold parallelism”\u7684\u503c\u8bbe\u7f6e\u7684\u66f4\u9ad8<\/div>\n
    \n\t <\/div>\n
    \n\t <\/div>\n
    \n\tPAGEIOLATCH_XX:\u4ece\u78c1\u76d8\u8bfb\u5165\u5230\u5185\u5b58\u65f6\u53d1\u9001\uff0c\u4e0d\u4e00\u5b9a\u662fio\u95ee\u9898\uff0c\u53ef\u80fd\u662f\u6267\u884c\u8ba1\u5212\u95ee\u9898\u3002\u6216\u8005\u5185\u5b58\u538b\u529b\u95ee\u9898\u3002<\/div>\n
    \n\t\u8fd9\u610f\u5473\u7740:<\/div>\n
    \n\t•\u7b49\u5f85\u9875\u7531\u78c1\u76d8\u88ab\u8bfb\u53d6\u5230<\/div>\n
    \n\t•\u6700\u5e38\u89c1\u7684\u662fSH\u548cEX<\/div>\n
    \n\t•SH\u610f\u5473\u7740\u9875\u88ab\u7528\u4e8e\u8bfb\u53d6<\/div>\n
    \n\t•EX\u610f\u5473\u7740\u9875\u4f1a\u88ab\u4fee\u6539<\/div>\n
    \n\t\u907f\u514d\u671b\u6587\u751f\u4e49<\/div>\n
    \n\t•\u4e0d\u8981\u76f4\u63a5\u5224\u65ad\u662fIO\u7cfb\u7edf\u548cIO\u901a\u9053\u7684\u95ee\u9898<\/div>\n
    \n\t\u66f4\u591a\u5206\u6790<\/div>\n
    \n\t•\u51b3\u5b9a\u54ea\u4e2a\u8868\/\u7d22\u5f15\u88ab\u8bfb\u53d6(\u901a\u8fc7DBCC Page)<\/div>\n
    \n\t•\u4f7f\u7528sys.dm_io_virtual_file_stats\u548cAvg Disk Secs\/Read\u6027\u80fd\u8ba1\u6570\u5668\u5224\u65adIO<\/div>\n
    \n\t•\u5bf9\u5e94\u7684CXPACKET\u7b49\u5f85,\u662f\u5426\u5b58\u5728\u5e76\u884c\u626b\u63cf<\/div>\n
    \n\t•\u901a\u8fc7\u6267\u884c\u8ba1\u5212\u67e5\u770b\u5e76\u884c\u626b\u63cf<\/div>\n
    \n\t•\u901a\u8fc7\u6267\u884c\u8ba1\u5212\u67e5\u770b\u662f\u5426\u5b58\u5728\u9690\u5f0f\u8f6c\u6362(\u53ef\u80fd\u5bfc\u81f4\u626b\u63cf)<\/div>\n
    \n\t•\u901a\u8fc7Page Life Expectancy\u67e5\u770b\u662f\u5426\u5b58\u5728\u7f13\u5b58\u533a\u5185\u5b58\u538b\u529b<\/div>\n
    \n\t\u521b\u5efa\u975e\u805a\u96c6\u7d22\u5f15\u6765\u51cf\u5c11\u626b\u63cf<\/div>\n
    \n\t\u66f4\u65b0\u7edf\u8ba1\u4fe1\u606f<\/div>\n
    \n\t\u5c06\u53d7\u5f71\u54cd\u7684\u6570\u636e\u8f6c\u79fb\u5230\u66f4\u5feb\u7684IO\u5b50\u7cfb\u7edf<\/div>\n
    \n\t\u8003\u8651\u589e\u52a0\u5185\u5b58<\/div>\n
    \n\t <\/div>\n
    \n\t <\/div>\n
    \n\tASYNC_NETWORK_IO:\u901a\u5e38\u5728sql server\u7b49\u5f85\u5ba2\u6237\u7aef\u53d6\u8d70\u6570\u636e\u65f6\u53d1\u9001\uff0c\u5ba2\u6237\u7aef\u751f\u4ea7\u5927\u91cf\u6570\u636e\uff0c\u5bfc\u81f4\u53d6\u6570\u636e\u5f88\u6162\uff0c\u5f80\u5f80\u662f\u7a0b\u5e8f\u8bbe\u8ba1\u4e0d\u5408\u7406\u9020\u6210\u3002<\/div>\n
    \n\t\u8fd9\u610f\u5473\u7740<\/div>\n
    \n\t•SQL Server\u7b49\u5f85\u5ba2\u6237\u7aef\u83b7\u53d6\u6570\u636e\u7684ACK\u53cd\u9988<\/div>\n
    \n\t\u907f\u514d\u671b\u6587\u751f\u4e49<\/div>\n
    \n\t•\u4e0d\u8981\u7b80\u5355\u8ba4\u4e3a\u662f\u7f51\u7edc\u5ef6\u8fdf<\/div>\n
    \n\t•\u53ea\u6709\u518d\u8003\u8651\u5176\u4ed6\u6240\u6709\u56e0\u7d20\u4e4b\u540e,\u518d\u8003\u8651\u662f\u4e0d\u662f\u7f51\u7edc\u5ef6\u8fdf<\/div>\n
    \n\t\u66f4\u591a\u5206\u6790<\/div>\n
    \n\t•\u5206\u6790\u5ba2\u6237\u7aef\u7a0b\u5e8f<\/div>\n
    \n\t•\u5206\u6790\u7f51\u7edc\u5ef6\u8fdf<\/div>\n
    \n\t\u89e3\u51b3\u65b9\u6848<\/div>\n
    \n\t•\u5ba2\u6237\u7aef\u7a0b\u5e8fRBAR(Row-By-Agonizing-Row)<\/div>\n
    \n\t•\u5206\u6790\u7f51\u7edc\u786c\u4ef6,TCP\u914d\u7f6e\u7b49<\/div>\n
    \n\t <\/div>\n
    \n\t <\/div>\n
    \n\tWRITELOG:\u65e5\u5fd7\u7ba1\u7406\u7cfb\u7edf\u7b49\u5f85\u65e5\u5fd7\u5237\u65b0\u5230\u78c1\u76d8\u65f6\u53d1\u9001\u3002\u5f80\u5f80\u8bf4\u660eio\u5b50\u7cfb\u7edf\u7684\u95ee\u9898\uff0c1.\u628a\u7b26\u5408\u5206\u6563\u5230\u591a\u4e2a\u6570\u636e\u5e93\u4e0a\u6216\u8005\u7f29\u5c0f\u957f\u4e8b\u52a1\u3002\u53ef\u4ee5\u4f7f\u7528sys.dm_io_virtual_file_stats\u68c0\u67e5\u65e5\u5fd7\u7684io\u95ee\u9898<\/div>\n
    \n\t\u8fd9\u610f\u5473\u7740<\/div>\n
    \n\t•\u7b49\u5f85\u5c06\u65e5\u5fd7\u5757flush\u5230\u65e5\u5fd7<\/div>\n
    \n\t\u907f\u514d\u671b\u6587\u751f\u4e49<\/div>\n
    \n\t•\u4e0d\u8981\u4e00\u5f00\u59cb\u5c31\u4ee5\u4e3a\u662fIO\u95ee\u9898<\/div>\n
    \n\t•\u4e0d\u8981\u76f4\u63a5\u589e\u52a0\u65e5\u5fd7\u6587\u4ef6<\/div>\n
    \n\t\u66f4\u591a\u5206\u6790<\/div>\n
    \n\t•\u67e5\u770bsys.dm_io_virtual_file_stats<\/div>\n
    \n\t•\u67e5\u770bLOGBUFFER\u7b49\u5f85,\u770b\u662f\u5426\u5b58\u5728\u5bf9\u65e5\u5fd7\u7f13\u51b2\u533a\u7684\u4e89\u62a2<\/div>\n
    \n\t•\u67e5\u770b\u65e5\u5fd7\u6240\u5728\u78c1\u76d8\u7684\u78c1\u76d8\u7b49\u5f85\u961f\u5217<\/div>\n
    \n\t•\u67e5\u770b\u4e8b\u52a1\u7684\u5e73\u5747\u5927\u5c0f<\/div>\n
    \n\t•\u67e5\u770b\u662f\u5426\u6709\u5927\u91cf\u7684\u9875\u5206\u88c2(\u9875\u5206\u88c2\u4f1a\u5bfc\u81f4\u5927\u91cf\u65e5\u5fd7)<\/div>\n
    \n\t\u5c06\u65e5\u5fd7\u8f6c\u79fb\u5230\u66f4\u5feb\u7684IO\u7cfb\u7edf(\u4e00\u5b9a\u8981\u548c\u6570\u636e\u5206\u5f00)<\/div>\n
    \n\t\u589e\u52a0\u4e8b\u52a1\u7684\u5927\u5c0f\u6765\u907f\u514d\u5927\u91cf\u65e5\u5fd7\u5199\u5165(\u6bd4\u5982\u8bf4\u6279\u91cf\u5199\u5165)<\/div>\n
    \n\t\u5220\u9664\u6ca1\u7528\u7684\u975e\u805a\u96c6\u7d22\u5f15,\u6765\u907f\u514d\u65e5\u5fd7\u5f00\u9500<\/div>\n
    \n\t\u4fee\u6539\u7d22\u5f15\u952e\u6216\u4f7f\u7528\u586b\u5145\u54ce\u51cf\u5c11\u9875\u5206\u88c2<\/div>\n
    \n\t\u4fee\u6539\u7a0b\u5e8f\u67b6\u6784,\u5c06\u8d1f\u8f7d\u5206\u5e03\u5230\u591a\u4e2a\u6570\u636e\u5e93\u6216\u670d\u52a1\u5668<\/div>\n
    \n\t <\/div>\n
    \n\tMSQL_XP: sql server\u7b49\u5f85\u6269\u5c55\u5b58\u50a8\u8fc7\u7a0b\u5b8c\u6210\u65f6\u53d1\u9001\uff0c\u68c0\u67e5\u6269\u5c55\u5b58\u50a8\u8fc7\u7a0b\u4ee3\u7801<\/div>\n
    \n\tLCK_M_XX:\u7ebf\u7a0b\u7b49\u5f85\u9501\u7684\u5206\u914d\uff0c\u8bf4\u660e\u7ebf\u7a0b\u5835\u585e<\/div>\n
    \n\t\u8fd9\u610f\u5473\u7740\uff1a<\/div>\n
    \n\t•\u7531\u4e8e\u53e6\u4e00\u4e2a\u7ebf\u7a0b\u5bf9\u67d0\u4e2a\u8d44\u6e90\u52a0\u9501\uff0c\u8be5\u7ebf\u7a0b\u4e0d\u80fd\u5bf9\u8d44\u6e90\u52a0\u4e0d\u517c\u5bb9\u7684\u9501<\/div>\n
    \n\t\u907f\u514d\u671b\u6587\u751f\u4e49<\/div>\n
    \n\t•\u4e0d\u8981\u4ee5\u4e3a\u9501\u662fRoot Cause<\/div>\n
    \n\t\u66f4\u591a\u5206\u6790<\/div>\n
    \n\t•\u901a\u8fc7sys.dm_os_waiting_tasks\u6765\u627e\u5230\u6700\u5f00\u59cb\u88ab\u963b\u585e\u7684\u7ebf\u7a0b\uff0c\u800c\u963b\u585e\u8be5\u7ebf\u7a0b\u7684\u539f\u56e0\u53ef\u80fd\u662fIO\u3001\u7f51\u7edc\u3001\u5185\u5b58\u7b49<\/div>\n
    \n\t•\u4f7f\u7528\u963b\u585e\u8fdb\u7a0b\u62a5\u8868\u6765\u6355\u6349\u7b49\u5f85\u4fe1\u606f<\/div>\n
    \n\t\u89e3\u51b3\u65b9\u6848\u57fa\u4e8e\u6700\u5f00\u59cb\u88ab\u963b\u585e\u8fdb\u7a0b\u7684\u7b49\u5f85\u7c7b\u578b<\/div>\n
    \n\t\u4e00\u4e2a\u67e5\u8303\u56f4\u66f4\u65b0\u6216\u626b\u63cf\u9020\u6210\u7684\u9501\u5347\u7ea7<\/div>\n
    \n\t•\u5982\u679c\u53ef\u80fd\uff0c\u4f7f\u7528\u5206\u533a\u9501<\/div>\n
    \n\t•\u5c1d\u8bd5\u521b\u5efa\u7d22\u5f15\uff0c\u4f7f\u5f97\u626b\u63cf\u53d8\u4e3a\u975e\u805a\u96c6\u7d22\u5f15\u67e5\u627e<\/div>\n
    \n\t•\u5c06\u5927\u6279\u91cf\u66f4\u65b0\u4e8b\u52a1\u5206\u89e3\u6210\u591a\u4e2a\u5c0f\u4e8b\u52a1<\/div>\n
    \n\t•\u5c1d\u8bd5\u4f7f\u7528\u4e0d\u540c\u7684\u9694\u79bb\u7b49\u7ea7\u6216\u662f\u5feb\u7167\u9694\u79bb<\/div>\n
    \n\t•\u907f\u514d\u4e0d\u5fc5\u8981\u7684\u9501<\/div>\n
    \n\t\u8bfb\u5199\u4e0d\u5e94\u8be5\u4e92\u76f8\u963b\u585e\uff0c\u53ef\u4ee5\u5c1d\u8bd5\u4fee\u6539\u9694\u79bb\u7b49\u7ea7\u6216\u4f7f\u7528\u4e50\u89c2\u5e76\u53d1<\/div>\n
    \n\t\u5176\u5b83\u963b\u6b62\u4e8b\u52a1\u91ca\u653e\u9501\u7684\u60c5\u51b5\uff0c\u5bfb\u627e\u57fa\u672c\u539f\u56e0<\/div>\n
    \n\t <\/div>\n
    \n\tIO_COMPLETION:\u7b49\u5f85io\u5b8c\u6210\u65f6\u51fa\u73b0\uff0c\u5f80\u5f80\u8bf4\u660eio\u95ee\u9898<\/div>\n
    \n\tSOS_SCHEDULER_YIELD:\u5728\u7b49\u5f85spinlock\u65f6\u53d1\u73b0\u53ef\u80fd\u4f1a\u6d6a\u8d39\u5f88\u591acpu\u56e0\u6b64\uff0c\u7ebf\u7a0b\u786e\u5b9a\u81ea\u52a8\u8ba9\u51facpu<\/div>\n
    \n\t\u8fd9\u610f\u5473\u7740<\/div>\n
    \n\t•\u7ebf\u7a0b\u7528\u5b8c4\u6beb\u79d2\u7684\u65f6\u95f4\u7247\uff0c\u4e3b\u52a8\u653e\u5f03CPU<\/div>\n
    \n\t•\u5b58\u5728\u65cb\u9501<\/div>\n
    \n\t\u907f\u514d\u671b\u6587\u751f\u4e49<\/div>\n
    \n\t•\u4e0d\u4e00\u5b9a\u662fCPU\u95ee\u9898\uff08CPU\u95ee\u9898\u5f80\u5f80\u4f53\u73b0\u5728\u957fRunnable\u961f\u5217\u6216\u5927\u91cfsignal wait\uff09<\/div>\n
    \n\t\u66f4\u591a\u5206\u6790<\/div>\n
    \n\t•\u901a\u8fc7\u6267\u884c\u8ba1\u5212\u67e5\u770b\u662f\u5426\u5b58\u5728\u5927\u91cf\u626b\u63cf<\/div>\n
    \n\t•\u67e5\u770b\u7b49\u5f85\u7c7b\u578b<\/div>\n
    \n\t\u6ce8\u610f\uff1a\u8be5\u65b9\u5f0f\u6ca1\u6709Resource_wait\u7b49\u5f85\u7c7b\u578b\uff0c\u56e0\u6b64\u4e00\u4e9b\u67e5\u8be2\u7b49\u5f85\u7c7b\u578b\u7684\u8bed\u53e5\u53ef\u80fd\u65e0\u6cd5\u6355\u83b7<\/div>\n
    \n\t•\u65e0\u6cd5\u5728sys.dm_os_waiting_tasks\u4e2d\u770b\u5230<\/div>\n
    \n\t <\/div>\n
    \n\tPAGELATCH_XX:\u5728\u8bbf\u95eepage\u65f6\u51fa\u73b0\uff08buf\u95e9\uff09\u7684\u7b49\u5f85\u3002\u53ef\u80fd\u662f\u70ed\u70b9\u9875\uff0cGAM\uff0cSGAM\uff0cPFS\u53ef\u80fd\u4f1a\u5f15\u8d77\u8fd9\u4e2a\u95ee\u9898<\/div>\n
    \n\t\u8fd9\u610f\u5473\u7740<\/div>\n
    \n\t•\u7b49\u5f85\u8bbf\u95ee\u5185\u5b58\u4e2d\u7684\u6570\u636e\u6587\u4ef6\u9875<\/div>\n
    \n\t•\u5e38\u89c1\u7684\u662fSH\u548cEX<\/div>\n
    \n\t•SH\u610f\u5473\u7740\u9875\u5c06\u88ab\u8bfb\u53d6<\/div>\n
    \n\t•EX\u610f\u5473\u7740\u9875\u4f1a\u88ab\u4fee\u6539<\/div>\n
    \n\t\u907f\u514d\u671b\u6587\u751f\u4e49<\/div>\n
    \n\t•\u4e0d\u8981\u989dPAGEIOLATCH_XX\u6df7\u6dc6<\/div>\n
    \n\t•\u4e0d\u610f\u5473\u7740\u9700\u8981\u589e\u52a0IO\u548c\u5185\u5b58<\/div>\n
    \n\t\u66f4\u591a\u5206\u6790<\/div>\n
    \n\t•\u627e\u51fa\u7b49\u5f85\u9875\u7684\u7c7b\u578b<\/div>\n
    \n\t <\/div>\n
    \n\t <\/div>\n
    \n\tSELECT TOP 50 * <\/div>\n
    \n\tFROM sys.dm_os_waiting_tasks<\/div>\n
    \n\t <\/div>\n
    \n\tSELECT wt.session_id, wt.wait_type, wt.wait_duration_ms<\/div>\n
    \n\t, s.name AS schema_name<\/div>\n
    \n\t, o.name AS object_name<\/div>\n
    \n\t, i.name AS index_name<\/div>\n
    \n\tFROM sys.dm_os_buffer_descriptors bd<\/div>\n
    \n\tJOIN (<\/div>\n
    \n\t    SELECT  session_id, wait_type,wait_duration_ms,resource_description,<\/div>\n
    \n\tPARSENAME(replace(resource_description,':','.'),1) database_id,<\/div>\n
    \n\tPARSENAME(replace(resource_description,':','.'),2) file_id,PARSENAME(replace(resource_description,':','.'),3)page_id<\/div>\n
    \n\t    FROM sys.dm_os_waiting_tasks<\/div>\n
    \n\t    WHERE wait_type LIKE 'PAGELATCH%'<\/div>\n
    \n\t)wt<\/div>\n
    \n\tON bd.database_id = wt.database_id<\/div>\n
    \n\tAND bd.file_id = wt.file_id<\/div>\n
    \n\tAND bd.page_id = wt.page_id<\/div>\n
    \n\tJOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id<\/div>\n
    \n\tJOIN sys.partitions p ON au.container_id = p.partition_id<\/div>\n
    \n\tJOIN sys.indexes i ON  p.index_id = i.index_id AND p.object_id = i.object_id<\/div>\n
    \n\tJOIN sys.objects o ON i.object_id = o.object_id<\/div>\n
    \n\tJOIN sys.schemas s ON o.schema_id = s.schema_id<\/div>\n
    \n\t <\/div>\n
    \n\t\u5904\u7406\u65b9\u6cd5\uff1ahttp:\/\/sqlcat.com\/sqlcat\/b\/technicalnotes\/archive\/2009\/09\/22\/resolving-pagelatch-contention-on-highly-concurrent-insert-workloads-part-1.aspx<\/div>\n
    \n\t <\/div>\n
    \n\t\u6700\u7ecf\u5178\u7684TempDB\u4e89\u62a2<\/div>\n
    \n\t•\u6dfb\u52a0TempDB\u6587\u4ef6<\/div>\n
    \n\t•4\u4e2a\u8d77,\u5982\u679c\u8fd8\u6709\u4e89\u62a2,\u518d\u589e\u52a04\u4e2a<\/div>\n
    \n\t•\u542f\u7528\u8ddf\u8e2a\u6807\u8bb01118<\/div>\n
    \n\t•\u51cf\u5c11TempDB\u7684\u4f7f\u7528(\u6bd4\u5982\u8bf4\u51cf\u5c11\u4e34\u65f6\u8868)<\/div>\n
    \n\t•\u51cf\u5c11\u4e34\u65f6\u8868\u7684\u4f7f\u7528,\u4e0d\u8981\u663e\u5f0f\u7684drop\u6389\u4e34\u65f6\u8868(\u975eBOOT\u9875TempDB\u4e89\u62a2)(\u9ad8\u7ee7\u4f1f)<\/div>\n
    \n\t\u8fc7\u591a\u7684\u9875\u5206\u88c2<\/div>\n
    \n\t•\u4fee\u6539\u7d22\u5f15\u952e(\u7ecf\u5178\u7684GUID)<\/div>\n
    \n\t•\u907f\u514d\u66f4\u65b0\u592a\u957f\u7684\u8bb0\u5f55<\/div>\n
    \n\t•\u4f7f\u7528\u586b\u5145\u56e0\u5b50<\/div>\n
    \n\t\u63d2\u5165\u9012\u589e\u8868\u4ea7\u751f\u63d2\u5165\u70ed\u70b9<\/div>\n
    \n\t•\u4f7f\u7528\u968f\u673a\u6216\u7ec4\u5408\u952e\u5e76\u7ed3\u5408\u586b\u5145\u56e0\u5b50\u6765\u51cf\u5c11\u9875\u5206\u88c2<\/div>\n
    \n\t•\u4fee\u6539\u7a0b\u5e8f\u67b6\u6784,\u63d2\u5165\u5206\u5e03\u5230\u591a\u4e2a\u8868\u3001\u6570\u636e\u5e93\u3001\u670d\u52a1\u5668\u4e2d<\/div>\n
    \n\t <\/div>\n
    \n\t <\/div>\n
    \n\t <\/div>\n
    \n\t <\/div>\n
    \n\tBACKUPXX:<\/div>\n
    \n\t\u53ef\u80fd\u662f<\/div>\n
    \n\t•BACKUPBUFFER<\/div>\n
    \n\t•BACKUPIO<\/div>\n
    \n\t•BACKUPTHREAD<\/div>\n
    \n\t\u8fd9\u610f\u5473\u7740<\/div>\n
    \n\t•\u7b49\u5f85\u6570\u636e\u6216\u662f\u6570\u636e\u7684\u7f13\u5b58<\/div>\n
    \n\t•\u8bfb\u53d6\u6570\u636e\u5e93\u6587\u4ef6<\/div>\n
    \n\t•\u7b2c\u4e09\u79cd\u901a\u5e38\u662f\u7531\u4e8e\u6570\u636e\u6216\u78c1\u76d8\u7684\u586b0\u521d\u59cb\u5316<\/div>\n
    \n\t\u66f4\u591a\u5206\u6790<\/div>\n
    \n\t•\u7b2c\u4e00\u79cd,\u5907\u4efd\u662f\u57fa\u4e8e\u6162\u901f\u7684\u7684IO\u7cfb\u7edf\u6216\u7f51\u7edc,\u6216\u662f\u8fdc\u7a0b\u670d\u52a1\u5668\u7684IO\u7cfb\u7edf\u7f13\u6162<\/div>\n
    \n\t•\u6570\u636e\u6587\u4ef6\u6240\u5728\u7684IO\u7cfb\u7edf\u7f13\u6162<\/div>\n
    \n\t•\u4f1a\u4ea7\u751fPREEMPTIVE_OS_WRITEFILEGATHER\u7b49\u5f85<\/div>\n
    \n\t <\/div>\n
    \n\tOLEDB<\/div>\n
    \n\t\u8fd9\u610f\u5473\u7740<\/div>\n
    \n\t•\u4f7f\u7528\u4e86OLE DB\u673a\u5236<\/div>\n
    \n\t\u907f\u514d\u671b\u6587\u751f\u4e49<\/div>\n
    \n\t•\u4e0d\u8981\u76f4\u63a5\u731c\u60f3\u662f\u56e0\u4e3a\u4f7f\u7528\u4e86\u94fe\u63a5\u670d\u52a1\u5668<\/div>\n
    \n\t\u66f4\u591a\u5206\u6790<\/div>\n
    \n\t•\u7b49\u5f85OLE DB\u7684\u67e5\u8be2\u662f\u4ec0\u4e48<\/div>\n
    \n\t•\u5982\u679c\u4f7f\u7528\u4e86\u94fe\u63a5\u670d\u52a1\u5668\uff0c\u90a3\u4e48\u4ec0\u4e48\u5bfc\u81f4\u4e86\u94fe\u63a5\u670d\u52a1\u5668\u7684\u5ef6\u65f6<\/div>\n
    \n\t\u53ef\u80fd\u7684\u89e3\u51b3\u65b9\u6848<\/div>\n
    \n\t•DBCC CHECKDB\u8fd9\u7c7b\u5185\u90e8\u4f7f\u7528\u4e86OLEDB\u7684\u547d\u4ee4<\/div>\n
    \n\t•\u5f88\u591aDMV\u5185\u90e8\u4f7f\u7528\u4e86OLEDB\uff0c\u56e0\u6b64\u53ef\u80fd\u662f\u4e00\u4e9b\u76d1\u6d4b\u5de5\u5177\u5bfc\u81f4\u7684\u95ee\u9898<\/div>\n
    \n\t•\u4f4e\u6027\u80fd\u7684\u94fe\u63a5\u670d\u52a1\u5668<\/div>\n
    \n\t <\/div>\n
    \n\t <\/div>\n
    \n\tLATCH_XX:\u975ebuf\u95e9\u7684\u7b49\u5f85\uff08\u95e9\u5206\u4e3a2\u79cd\uff0cbuf\u95e9\u548c\u975ebuf\u95e9\uff0cSQL Server 2008\u5185\u90e8\u5256\u6790\u4e0e\u6545\u969c\u5206\u6790\u4e00\u4e66\u76846.6\u4e2d\u6709\u8be6\u7ec6\u4ecb\u7ecd\uff09<\/div>\n
    \n\tPREEMPTIVE_XX:\u5207\u6362\u5230\u62a2\u5360\u6a21\u5f0f\u901a\u8fc7windows\u8c03\u5ea6\u505a\u76f8\u5173\u64cd\u4f5c\u65f6\u51fa\u73b0\u7684\u7b49\u5f85<\/div>\n
    \n\tPREEMPTIVE_OS_XX<\/div>\n
    \n\t\u8fd9\u610f\u5473\u7740<\/div>\n
    \n\t•\u7ebf\u7a0b\u76f4\u63a5\u8c03\u7528OS<\/div>\n
    \n\t•\u7ebf\u7a0b\u5207\u6362\u5230\u62a2\u5360\u5f0f\u8c03\u5ea6\u6a21\u5f0f<\/div>\n
    \n\t•\u7ebf\u7a0b\u7684\u72b6\u6001\u662fRUNNING\uff0c\u800c\u4e0d\u662fSUSPENDED<\/div>\n
    \n\t\u66f4\u591a\u5206\u6790<\/div>\n
    \n\t•SQL Server 2012\u4e2d\u6709194\u4e2a\u8be5\u7c7b\u4e8b\u4ef6<\/div>\n
    \n\t•\u8fd9\u7c7b\u4e8b\u4ef6\u6587\u6863\u975e\u5e38\u5c11<\/div>\n
    \n\t•\u4e00\u4e2a\u5c0f\u6280\u5de7\uff0c\u5728MSDN\u641c\u7d22PREEMPTIVE_OS_XX\u4e2d\u7684XX\u90e8\u5206\uff0c\u8fd9\u90e8\u5206\u5185\u5bb9\u5176\u5b9e\u5c31\u662fWINDOWS API<\/div>\n
    \n\t\u53ef\u80fd\u7684\u89e3\u51b3\u65b9\u6848<\/div>\n
    \n\t•\u8981\u57fa\u4e8e\u4e0d\u540c\u79cd\u7c7b\u7684\u7b49\u5f85\u7c7b\u578b\u6765\u5224\u65ad<\/div>\n
    \n\t <\/div>\n
    \n\tPREEMPTIVE_OS_CREATEFILE<\/div>\n
    \n\t\u8fd9\u610f\u5473\u7740<\/div>\n
    \n\t•\u7ebf\u7a0b\u4f1a\u8c03\u7528Windows\u6765\u521b\u5efa\u6587\u4ef6<\/div>\n
    \n\t•\u5982\u679c\u4f7f\u7528\u4e86FileStream\uff0c\u5f53FileStream\u521b\u5efa\u65b0\u7684NTFS\u6587\u4ef6\u65f6\uff0c\u53ef\u80fd\u4f1a\u5bfc\u81f4\u8be5\u95ee\u9898<\/div>\n
    \n\t\u66f4\u591a\u5206\u6790<\/div>\n
    \n\t•\u67e5\u770b\u4e0d\u65ad\u589e\u957f\u7684\u7b49\u5f85\u65f6\u95f4<\/div>\n
    \n\t\u53ef\u80fd\u7684\u89e3\u51b3\u65b9\u6848<\/div>\n
    \n\t•\u627f\u8f7dFileStream\u7684IO\u6027\u80fd\u4e0d\u884c<\/div>\n
    \n\t•\u4f7f\u7528FileStream\u7684IO\u8d1f\u8f7d\u8fc7\u91cd<\/div>\n
    \n\t•\u53c2\u8003WIN32 API:http:\/\/msdn.microsoft.com\/en-us\/library\/windows\/desktop\/aa363858(v=vs.85).aspx<\/div>\n
    \n\t <\/div>\n
    \n\tPREEMPTIVE_OS_WRITEFILEGATHER<\/div>\n
    \n\t\u8fd9\u610f\u5473\u7740<\/div>\n
    \n\t•\u7ebf\u7a0b\u4f1a\u8c03\u7528Windows\u6765\u5199\u5165\u6587\u4ef6<\/div>\n
    \n\t\u907f\u514d\u671b\u6587\u751f\u4e49<\/div>\n
    \n\t•\u4e0d\u8981\u53ea\u8ba4\u4e3a\u662fIO\u95ee\u9898<\/div>\n
    \n\t\u66f4\u591a\u5206\u6790<\/div>\n
    \n\t•\u6b63\u5728\u8fdb\u884c\u7684\u6570\u636e\u5e93\u64cd\u4f5c<\/div>\n
    \n\t•\u6bd4\u5982\u8bf4\u8fd8\u539f\u6570\u636e\u5e93\uff0c\u6570\u636e\u5e93\u6587\u4ef6\u7684\u521b\u5efa\u3001\u589e\u957f\u548c\u81ea\u52a8\u589e\u957f<\/div>\n
    \n\t\u53ef\u80fd\u7684\u89e3\u51b3\u529e\u6cd5<\/div>\n
    \n\t•\u5728\u8fd8\u539f\u6570\u636e\u5e93\u6216\u65e5\u5fd7\u589e\u957f\u65f6\u5bf9\u65e5\u5fd7\u586b\u96f6\u521d\u59cb\u5316<\/div>\n
    \n\t•\u5bf9\u6570\u636e\u6587\u4ef6\u586b\u96f6\u521d\u59cb\u5316<\/div>\n
    \n\t•\u542f\u7528\u5feb\u901f\u6587\u4ef6\u521d\u59cb\u5316<\/div>\n
    \n\t•\u5728\u8fdb\u884c\u6570\u636e\u5e93\u8fd8\u539f\u65f6\uff0c\u4e0d\u8981\u5220\u9664\u73b0\u6709\u7684\u6587\u4ef6<\/div>\n
    \n\t•\u53c2\u8003WIN32 API:http:\/\/msdn.microsoft.com\/en-us\/library\/windows\/desktop\/aa365749(v=vs.85).aspx<\/div>\n
    \n\t <\/div>\n
    \n\tPREEMPTIVE_OS_WRITEFILEGATHER<\/div>\n
    \n\t\u8fd9\u610f\u5473\u7740<\/div>\n
    \n\t•\u4e00\u4e2a\u7ebf\u7a0b\u8c03\u7528Windows\u6765\u7b49\u5f85\u540c\u6b65\u5bf9\u8c61\u7684\u6539\u53d8<\/div>\n
    \n\t•\u901a\u5e38\u548cNETWORK_IO\u4ee5\u53caASYNC_NETWORK_IO\u4e00\u8d77\u51fa\u73b0<\/div>\n
    \n\t\u66f4\u591a\u5206\u6790<\/div>\n
    \n\t•\u6309\u7167ASYNC_NETWORK_IO\u5904\u7406\u65b9\u5f0f\u5904\u7406<\/div>\n
    \n\t•\u67e5\u770b\u662f\u5426\u5b58\u5728\u4e8b\u52a1\u65e5\u5fd7\u590d\u5236<\/div>\n
    \n\t\u53ef\u80fd\u7684\u89e3\u51b3\u65b9\u6848<\/div>\n
    \n\t•ASYNC_NETWORK_IO<\/div>\n
    \n\t•\u5f53APP\u670d\u52a1\u5668\u548c\u6570\u636e\u5e93\u670d\u52a1\u5668\u5728\u540c\u4e00\u53f0\u65f6\uff0c\u4f7f\u7528\u5171\u4eab\u5185\u5b58<\/div>\n
    \n\t•\u5f53\u548cNETWORK_IO\u4e00\u8d77\u65f6\uff0c\u5f88\u53ef\u80fd\u662f\u4e8b\u52a1\u65e5\u5fd7\u590d\u5236<\/div>\n
    \n\t <\/div>\n
    \n\tPREEMPTIVE_OS_DBMIRRORXX<\/div>\n
    \n\t\u793a\u4f8b<\/div>\n
    \n\t•DBMIRROR_EVENT_QUEUE<\/div>\n
    \n\t•DBMIRROR_SEND<\/div>\n
    \n\t•DBMIRRORING_CMD<\/div>\n
    \n\t•DBMIRROR_DBR_MUTEX<\/div>\n
    \n\t\u8fd9\u610f\u5473\u7740<\/div>\n
    \n\t•\u7b49\u5f85\u955c\u50cf\u8d44\u6e90<\/div>\n
    \n\t\u907f\u514d\u671b\u6587\u751f\u4e49<\/div>\n
    \n\t•\u4e0d\u8981\u4ec5\u4ec5\u76f4\u63a5\u79fb\u9664\u955c\u50cf\u6216\u9009\u62e9\u9ad8\u6027\u80fd\u6a21\u5f0f<\/div>\n
    \n\t\u66f4\u591a\u5206\u6790<\/div>\n
    \n\t•\u5206\u6790DBMIRROR_DBR_MUTEX\u7684\u5e73\u5747\u7b49\u5f85\u65f6\u95f4<\/div>\n
    \n\t\u53ef\u80fd\u7684\u539f\u56e0<\/div>\n
    \n\t•\u5982\u679cDBMIRROR_DBR_MUTEX\u7684\u7b49\u5f85\u65f6\u95f4\u8fc7\u591a\uff0c\u5219\u53ef\u80fd\u662f\u7531\u4e8e\u955c\u50cf\u7684\u6570\u636e\u5e93\u8fc7\u591a\uff0c\u6216\u592a\u591a\u9700\u8981\u955c\u50cf\u7684\u5185\u5bb9<\/div>\n
    \n\t•\u53ef\u80fd\u662f\u7531\u4e8e\u5e38\u89c1\u7684\u7cfb\u7edf\u74f6\u9888<\/div>\n
    \n\t <\/div>\n
    \n\tSQLTRACE_XX<\/div>\n
    \n\t\u8fd9\u610f\u5473\u7740<\/div>\n
    \n\t•\u7ebf\u7a0b\u7b49\u5f85\u5199\u5165SQLTRACE\u6587\u4ef6<\/div>\n
    \n\t\u907f\u514d\u671b\u6587\u751f\u4e49<\/div>\n
    \n\t•\u4e0d\u4e00\u5b9a\u975e\u8981\u505c\u6b62SQLTRACE<\/div>\n
    \n\t\u66f4\u591a\u5206\u6790<\/div>\n
    \n\t•\u4f7f\u7528sys.traces\u548csys.fn_trace_geteventinfo\u662f\u5426\u8ddf\u8e2a\u4e86\u4e00\u4e9b\u975e\u5e38\u9891\u7e41\u7684\u4e8b\u4ef6<\/div>\n
    \n\t•\u5206\u6790\u8ddf\u8e2a\u6587\u4ef6\u6240\u5728\u6240\u5728\u7684IO<\/div>\n
    \n\t\u53ef\u80fd\u7684\u539f\u56e0<\/div>\n
    \n\t•\u8ddf\u8e2a\u6355\u83b7\u4e86\u592a\u591a\u7684\u4e8b\u4ef6<\/div>\n
    \n\t•\u884c\u96c6\u6ca1\u6709\u5feb\u901f\u6d88\u8d39\u7ed3\u679c\u96c6<\/div>\n
    \n\t•\u7b2c\u4e09\u65b9\u4ea7\u54c1\u5728\u626b\u63cf\u8ddf\u8e2a<\/div>\n
    \n\t <\/div>\n
    \n\tLATCH_XX<\/div>\n
    \n\t\u8fd9\u610f\u5473\u7740<\/div>\n
    \n\t•\u5b58\u5728\u975e\u9875\u95e9\u9501<\/div>\n
    \n\t\u66f4\u591a\u5206\u6790<\/div>\n
    \n\t•\u4f7f\u7528sys.dm_os_latch_stats\u6765\u5206\u6790\u54ea\u4e00\u4e2a\u95e9\u9501\u7b49\u5f85\u65f6\u95f4\u8fc7\u957f<\/div>\n
    \n\t•\u548c\u5176\u5b83\u540c\u65f6\u53d1\u751f\u7684\u7b49\u5f85\u7c7b\u578b\u7ed3\u5408\u67e5\u770b<\/div>\n
    \n\t•\u6bd4\u5982\u8bf4CXPACKET\u548cLATCH_EX\u4e0eACCESS_METHODs_SCAN_RANGE_GENERATOR\u5f80\u5f80\u610f\u5473\u7740\u5b58\u5728\u5927\u91cf\u626b\u63cf<\/div>\n
    \n\t\u53ef\u80fd\u7684\u89e3\u51b3\u65b9\u6848<\/div>\n
    \n\t•\u8fd9\u7c7b\u9501\u662f\u6ca1\u6709\u6587\u6863\u652f\u6301\u7684\uff0c\u9700\u8981\u81ea\u884cGoogle<\/div>\n
    \n\t•\u63a5\u4e0b\u6765\u63a2\u8ba8\u51e0\u9875\u5e38\u89c1\u7684\u9501<\/div>\n
    \n\t•\u5fae\u8f6f\u767d\u76ae\u4e66\uff1ahttp:\/\/sqlcat.com\/sqlcat\/b\/whitepapers\/archive\/2011\/07\/05\/diagnosing-and-resolving-latch-contention-on-sql-server.aspx<\/div>\n
    \n\t <\/div>\n
    \n\t <\/div>\n
    \n\tTHREADPOOL:\u7b49\u5f85\u53ef\u7528\u7684workthreads<\/div>\n
    \n\tDBMIRROR_DBM_MUTEX:\u53d1\u9001buffer\u65f6\u51fa\u73b0\u7684\u7b49\u5f85\uff0c\u53ef\u80fd\u662f\u955c\u50cf\u56de\u8bdd\u8fc7\u591a<\/div>\n
    \n\tRESOUCE_SEMAPHORE:\u67e5\u8be2\u8bed\u53e5\u7b49\u5f85\u5206\u914d\u5185\u5b58\u65f6\u51fa\u73b0\uff0c\u53ef\u80fd\u662f\u67e5\u8be2\u8bed\u53e5\u8fc7\u5927\u6216\u8005\u9700\u6c42\u7684\u5185\u5b58\u8fc7\u5927\u3002<\/div>\n
    \n\tMSQL_DG: sql server\u7b49\u5f85\u5206\u5e03\u5f0f\u67e5\u8be2\u5b8c\u6210\u65f6\u51fa\u73b0\uff0c\u8bf4\u660e\u5206\u5e03\u5f0f\u67e5\u8be2\u6709\u95ee\u9898<\/div>\n
    \n\tRESOUCE_SEMAPHORE_QUERY_COMPLIE:\u8fc7\u5927\u7684\u5e76\u53d1\u7f16\u8bd1\uff0c\u4e3b\u8981\u662f\u91cd\u7f16\u8bd1\u548c\u65e0\u7f13\u51b2plan\u9020\u6210<\/div>\n
    \n\tMSSEARCH:\u5168\u6587\u67e5\u8be2\u7b49\u5f85<\/div>\n
    \n\t <\/div>\n
    \n\t\u53c2\u8003:Wait statistics, or please tell me where it hurtd <\/div>\n
    \n\twaitstats\uff0clatch\uff0cspinlock\u76f8\u5173\u6587\u7ae0<\/div>\n
    \n\t\u7b49\u5f85\u7c7b\u578bbol<\/div>\n
    \n\t <\/div>\n","protected":false},"excerpt":{"rendered":"
    WITH [Waits] AS     (SELECT     &nb […]<\/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-2755","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\/2755","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=2755"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2755\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2755"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2755"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2755"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}