為什么SQL Server實例處在壓力下

為什么SQL Server實例處在壓力下

翻譯自：https://www.simple-talk.com/sql/database-administration/why-is-that-sql-server-instance-under-stress/

當你在一個SQL Server實例上遇到性能問題，有些度量指標足夠告訴你本質，你隨后可以快速專注于實際原因上。有很多不同的性能指標可以用來理解你的SQL Server實例怎么樣了。這些指標中的每個會給你一個積極暗示。也許有內部或外部內存壓力、過度的或不規則的CPU負載，或者IO瓶頸。如果你獲得一個尋找問題的積極暗示，你隨后可以深入詳情。例如，如果你確定在SQL Server里有過度的CPU壓力，接下來需要確定哪個查詢導致了大多數CPU。

那么這些常用暗示是什么呢？對于獲得有關總處理器時間，服務器上CPU的利用率，或者從磁盤讀取數據的時間花費的平均讀取時間的直接的信息是有用的。當你完成了這些，然后，有其他找出系統上發生什么的方法是不容易的，它們甚至提供了更有用的信息。讓我們更詳細的瀏覽這些方法中的一些。

是否你的實例經受著內存壓力？

sys.dm_os_ring_buffers

操作系統內的環形緩沖是特定系統信息類型的一個收集點。大部分是鮮為人知的系統信息，它們中的很多與環形緩沖自己的管理有關，但是緩沖信息中的一些極其有趣。例如，操作系統意識到它運行在低內存下時，有一條信息記錄到環形緩沖里，如果有一個內存警告，你可以通過使用DMV對象sys.dm_os_ring_buffers來找出來。

實際上有兩類內存警告。對于正在運行的機器的物理內存，你可以得到一個內存警告。從SQL Server的角度來講，這被稱為外部內存，因為它不是SQL Server服務管理的內存。你也獲得虛擬內存警告，這個內存正被SQL Server服務管理。我們常稱為內部內存。當任何一個運行低時，你可以看到一個警告記錄到了環形緩沖里。當你有足夠的內存或者有一個大的內存增長，你也可以看到警告。

你只需要像這樣查詢這個DMV：

SELECT * FROM sys.dm_os_ring_buffers AS dorb;

那將返回可用信息，你很快發現大多數返回的增長信息是在“record”列。這是一個存儲XML的文本列，你可以使用以下更加深入的查詢從這里獲取感興趣的信息：

WITH    RingBuffer
          AS (SELECT    CAST(dorb.record AS XML) AS xRecord,
                        dorb.TIMESTAMP
              FROM      sys.dm_os_ring_buffers AS dorb
              WHERE     dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
             )
    SELECT  xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification,
            xr.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') AS IndicatorsProcess,
            xr.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint') AS IndicatorsSystem,
            DATEADD(ss,
                    (-1 * ((dosi.cpu_ticks / CONVERT (FLOAT, (dosi.cpu_ticks / dosi.ms_ticks)))
                           - rb.TIMESTAMP) / 1000), GETDATE()) AS RmDateTime,
            xr.value('(MemoryNode/TargetMemory)[1]', 'bigint') AS TargetMemory,
            xr.value('(MemoryNode/ReserveMemory)[1]', 'bigint') AS ReserveMemory,
            xr.value('(MemoryNode/CommittedMemory)[1]', 'bigint') AS CommitedMemory,
            xr.value('(MemoryNode/SharedMemory)[1]', 'bigint') AS SharedMemory,
            xr.value('(MemoryNode/PagesMemory)[1]', 'bigint') AS PagesMemory,
            xr.value('(MemoryRecord/MemoryUtilization)[1]', 'bigint') AS MemoryUtilization,
            xr.value('(MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS TotalPhysicalMemory,
            xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS AvailablePhysicalMemory,
            xr.value('(MemoryRecord/TotalPageFile)[1]', 'bigint') AS TotalPageFile,
            xr.value('(MemoryRecord/AvailablePageFile)[1]', 'bigint') AS AvailablePageFile,
            xr.value('(MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS TotalVirtualAddressSpace,
            xr.value('(MemoryRecord/AvailableVirtualAddressSpace)[1]',
                     'bigint') AS AvailableVirtualAddressSpace,
            xr.value('(MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]',
                     'bigint') AS AvailableExtendedVirtualAddressSpace
    FROM    RingBuffer AS rb
            CROSS APPLY rb.xRecord.nodes('Record') record (xr)
            CROSS JOIN sys.dm_os_sys_info AS dosi
    ORDER BY RmDateTime DESC;

使用這個查詢，我首先創建了一個叫做RingBuffer的公用表表達式（CTE）。在那里我只做了兩件事，首先過濾了一個特定的ring buffer類型“RING_BUFFER_RESOURCE_MONITOR”。這里是產生內存信息的地方。第二，我將“Record”列從文本轉換為XML。在那我使用CTE查詢，并使用XQuery命令從XML數據獲取所有感興趣的信息。

作為一個額外因素，在sys.dm_os_ring_buffers里的timestamp列實際上是一個datetime值，但是它是基于CPU頻率，因此你得使用那個公式來將數據轉換為可讀的date和time。

使用sys.dm_os_buffers作為監控進程的一部分，你只需查找這兩個事件，RESOURCE_MEMPHYSICAL_LOW或RESOURCE_MEMVIRTUAL_LOW。這些是在XML里可用的ResourceMonitor/Notification屬性。他們是機器上低內存條件的一個絕對指標，所以如果你獲得了警告，你要么外部的/OS/物理內存低，要么內部的/SQL Server/虛擬內存低。

是否系統在負載之下？

一個問題隨之而來，“是否系統在負載之下？”

有大量不同的方法嘗試理解這個，但是只有一些讓你肯定的知道是否你處于壓力下。

sys.dm_os_workers

我最喜歡的精準確定系統中有多少工作進程的方法之一是查看sys.dm_os_workers。這個度量指標不會告訴你導致系統負載的是什么，它也不會允許你理解負載的影響是什么。然而，它是系統上負載的完美衡量。

從sys.dm_os_workers返回了大量的信息。這個DMV返回操作系統上工作進程的信息。你可以查看關于進程的信息，像最后等待類型，是否工作進程有異常，它經歷了多少次上下文切換，各種東西。聯機幫助文檔里的DMV甚至顯示了如何確定一個在可運行狀態的進程有多長時間。

只是使用它作為一個負載的度量，你的查詢極其容易：

SELECT  COUNT(*)
FROM    sys.dm_os_workers AS dow
WHERE   state = 'RUNNING';

這真的有那么簡單。隨著數量上下，系統上的負載也上下。今天有大量的昨天沒有的“RUNNING”工作進程，你系統上有負載增加。但是牢記你需要比較一段時間。只捕獲一個數字不能說明問題。你得能夠在兩個值間比較。

sys.dm_os_schedulers

衡量系統負載的另一個方法是查看調度器。這是管理工作進程的進程。再次，這是系統上負載的絕對度量。它可以告訴你系統上有多少工作正在做。

查看調度器產生了大量有關系統正被管理的感興趣的信息。你可以看到多少工作進程正被一個特定的調度器所處理。你可以看到調度器退讓CPU的次數（放棄訪問另一個進程，因為每個進程只獲得對CPU限制的訪問），調度器里大量當前活動的工作進程和一些其他詳情。

但是，去看一個負載的度量，你可以運行一個非常簡單的查詢：

SELECT  COUNT(*)
FROM    sys.dm_os_schedulers AS dos
WHERE   dos.is_idle = 0;

再次，這個數字只有與之前的值比較時才有意義。使用工作進程或者調度器作為工作負載的度量與你的極限數據一樣準確，但如果你維護著這些值一段時間的集合，你可以確定操作系統上的負載。

SQL Server有足夠的內存么？

DBCC MEMORYSTATUS

這簡直是一個令人驚喜的數據集合。你所獲得的是在SQL Server里各種內存管理的輸出。你可以看到在SQL Server里分配和管理的每位內存。當你與來自微軟的客戶支持工程師處理特定問題的故障排除時，這個命令頻繁使用。但是，這是另一個精準確定系統上內存工作得怎么樣的方式。

如果你只運行這個命令：

DBCC MEMORYSTATUS();

你會看到SQL Server里所有的各種內存分配和管理進程。它們全部。事實上，有如此多的信息很快變成沒有意義的嘗試。好消息是，好消息是可以面向一些特定的信息片段。如果我們專門追求Target Committed值和Current Committed值，我們可以確定是否SQL Server有足夠內存。很簡單。如果Target值高于Current值，在SQL Server里你沒有需要的內存。但是獲得這些值有點頭疼。這里有個方法：

DECLARE @MemStat TABLE
    (ValueName SYSNAME,
     Val BIGINT
    );
INSERT  INTO @MemStat
        EXEC ('DBCC MEMORYSTATUS() WITH TABLERESULTS'
            );
WITH    Measures
          AS (SELECT TOP 2
                        CurrentValue,
                        ROW_NUMBER() OVER (ORDER BY OrderColumn) AS RowOrder
              FROM      (SELECT CASE WHEN (ms.ValueName = 'Target Committed')
                                     THEN ms.Val
                                     WHEN (ms.ValueName = 'Current Committed')
                                     THEN ms.Val
                                END AS 'CurrentValue',
                                0 AS 'OrderColumn'
                         FROM   @MemStat AS ms
                        ) AS MemStatus
              WHERE     CurrentValue IS NOT NULL
             )
    SELECT  TargetMem.CurrentValue - CurrentMem.CurrentValue
    FROM    Measures AS TargetMem
            JOIN Measures AS CurrentMem
            ON TargetMem.RowOrder + 1 = CurrentMem.RowOrder;

我創建了一個表變量，然后使用TABLERESULTS從MEMORYSTATUS導入所有的輸出，確保輸出是一個表。通過使用公共表表達式（CTE）來定義從表變量選擇的信息，我可以使用SELECT語句并基于ROW_NUMBER來JOIN著兩個值來參照它兩次。它真的有用。如果獲得了一個負值，查看內存問題。

理解DBCC MEMORYSTATUS是指一個微軟的支持機制。它不是標準工具集的一部分。這就意味著它完全是針對SQL Server從一個版本到下一個，或者甚至一個補丁包到下一個的未公布的修改。有了這一深刻的理解立刻使用它來診斷內存問題。

是否我需要一個更好、更快的磁盤系統？

sys.dm_io_virtual_file_stats

這個動態管理視圖返回了你的數據庫上文件行為的統計。這里最感興趣的信息片段是stalls、waits、gathered和available。如果你簡單運行這個查詢：

SELECT *
FROM   sys.dm_io_virtual_file_stats(DB_ID(DB_NAME()), NULL) AS divfs;

你必需傳給它兩個信息：數據庫ID，通過使用DB_NAME來識別當前附加上的數據庫，然后傳給DB_ID；和文件ID，我可以傳遞NULL作為參數，將返回數據的所有文件。

返回的信息是非常棒的，尤其這四列：sample_ms、io_stall_read_ms、io_stall_write_ms和io_stall。讓我們看看這些代表什么，你講很快理解到這些對于作為DBA的你多么感興趣。sample_ms是非常直接的。從上次SQL Server重啟后的時間。它提供了理解所有其他值的度量信息。下一個是io_stall_read_ms。這表示進程被迫從這個設備上等待讀操作的時間總量。如果你結合is_stall_read_ms和sample_ms，你將會獲得你的應用程序正從一個特定數據庫的一個特定文件上等待讀操作的時間百分比的精確度量。你也得到is_stall_write_ms，它表示進程已等待寫操作的時間總量。你可以收集這些性能指標一段時間來查看它如何增長，或者與讀操作一樣，使用sample_ms用相同的方法。最后，io_stall顯示了對于任何io操作，發生在那個文件上的等待時間的總量。再次，你可以收集一段時間來看它是如何增長的（因為它永遠只會增長）或者你可以通過比較sample_ms來獲得等待磁盤的時間百分比。

這些方法會準確告訴你，關于系統上的io等待問題如何嚴峻。但是它們不能定位特定的查詢。相反，這個方法主要專注于確定是否你的系統有問題。你需要更多磁盤、更快的磁盤等。

CPU執行的怎樣？

sys.dm_os_wait_stats

我將它列為一個收集性能指標的鮮為人知的方法，但是它真的不是。迄今為止每個人聽到理解服務器正等待什么是理解什么導致服務器運行慢的好方法。但是我仍然看到很多人驚訝于他們可以找到這個信息。

sys.dm_os_wait_stats顯示了自從上次它啟動以來（或者從等待統計信息被清理以來）服務器正等待什么的一個聚合視圖。這個信息分解為特定的等待類型，其中一些的確晦澀難懂。我不會嘗試將它們文檔化，甚至微軟都不支持關于這些的完整的文檔化。你需要依賴于網絡搜索來識別什么一些等待類型表示什么。其他的在聯機幫助文檔里有文檔輸出，因此請利用這個很棒的資源。

去查詢sys.dm_os_wait_stats，像這樣運行一個查詢：

SELECT *
FROM   sys.dm_os_wait_stats AS dows;

輸出只有五列：wait_type、waiting_tasks_count、wait_time_ms、max_wait_time_ms和singal_wait_time_ms。這其中唯一一個不可立即通過名字理解的是singal_wait_time_ms。這列表示當線程被調用和它實際開始執行的時間數量。這個時間包含在總時間wait_time_ms。single_wait_time_ms然后是獲得CPU的等待時間的一個實際度量。這是一個CPU支撐多少負載的很好的衡量。因為這，通常當你應該完全查看等待統計時，為了理解CPU執行得怎樣，你應該總是單獨專注于single_wait_time_ms。你會對這變得富有經驗，后者你可以只專注于以下查詢：
SELECT SUM(dows.signal_wait_time_ms)
FROM   sys.dm_os_wait_stats AS dows;

這表示發生在系統上的CPU等待的一個累加總計。這是一個短時間負載的不錯表示器。你需要比較不同方法來看它增長如何。

總結

這些只是常見的壓力方面的示例，你可以通過檢查快速專注于系統特定的方面，來幫助了解運行得怎樣。使用這些方法你可以快速確認或評估導致性能問題的可能性。

每一種方法提供了一個足夠積極的暗示，讓你確認有內存壓力或者CPU處于負載下。一旦你理解了壓力的一般性質，為了理解像哪個查詢導致最多CPU，你需要知道其他更多標準指標。