[SQL]透過redgate SQL Monitor 來找出 ASYNC_NETWORK_IO 問題

SQL的Top Wait是ASYNC_NETWORK_IO,可以看一下AP跟DB之間的頻寬是否足夠哦!
本篇是透過redgate SQL Monitor來找出問題所在,希望對大家有所幫助。

最近因為在查一個SQL的效能問題,透過 sys.dm_os_wait_stats 來取得Top的Wait(from Wait statistics, or please tell me where it hurts) ,如下,

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
--1.取得目前最高的Wait
WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
    )
SELECT
    [W1].[wait_type] AS [WaitType],
    CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],
    CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],
    CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],
    [W1].[WaitCount] AS [WaitCount],
    CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
GO

 

執行的結果如下,

image

 

想要知道當在執行程式時,SQL Server的狀況到底如何,所以就download SQL Monitor來用看看,您可以輸入使用者的相關信息,或是直接download來安裝。

image

下載SQLDBABundle.zip裡有2個檔案,一個是SQLDBABundle.exe,一個是SQLMonitor.exe。

如果您要試用其他的DB管理Tool可以安裝SQLDBABundle。這裡我要的是 SQL Monitor ,所以執行 SQLMonitor.exe。

因為我要使用自已建立的DB及IIS,所以安裝 SQL Monitor 之前,請先安裝IIS(使用.NET 2.0,如果沒有的話,x64 OS請執行C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i,x86 OS請執行C:\Windows\Microsoft.NET\Framework64\v2.0.50727\aspnet_regiis.exe -i )及SQL Server。

安裝完成後,設定IIS的.NET Framework請選取.NET 2.0 整合式,如下圖,

image

 

再來就是設定要監看的SQL Server,可設定分別設定監看OS狀態及SQL狀態的驗證資訊,如下,

image

image

 

詳細使用方式,可參考http://www.red-gate.com/products/dba/sql-monitor/resources/

針對這次,對我比較有用的是「Analysis」,因為可以加入各種指標來監看SQL Server的狀況,因為最高的Wait是「ASYNC_NETWORK_IO」,所以我將 Network utilization 加入,發現如下的狀況,

image

 

發現程式在執行時,網路傳輸的比重相當的高。於是請MIS想一下是否能增加AP跟DB之間的頻寬,結果發現DB Server所使用的Hub是10M的,換成1G,再執行程式測式,結果網路塞車的問題就解掉了,執行時間也整個都縮短了,如下圖,

image

 

最高的Wait也換成了CXPACKET,如下圖。

image

 

另外, SQL Monitor 還有其他好用的功能,比如說執行比較久的SQL及系統的一些Alert等等,大家有時間可以下載來玩看看哦!

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活

請大家繼續支持 ^_^