前言
MS SQL Server DBMS 的用戶、開發人員和管理員經常會遇到數據庫或整個 DBMS 的性能問題，因此 MS SQL Server 監控非常重要。
這篇文章是對文章的補充 它將涵蓋監控 MS SQL Server 的一些方面，特別是：如何快速確定哪些資源丟失，以及設置跟踪標誌的建議。
為了使以下腳本正常工作，您需要在所需的數據庫中創建一個 inf 架構，如下所示：
創建 inf 架構
use <имя_БД>;
go
create schema inf;
檢測RAM不足的方法
RAM 不足的第一個跡像是 MS SQL Server 實例耗盡了分配給它的所有 RAM。
為此，我們將創建 inf.vRAM 的以下表示形式：
創建 inf.vRAM 視圖
CREATE view [inf].[vRAM] as
select a.[TotalAvailOSRam_Mb] --сколько свободно ОЗУ на сервере в МБ
, a.[RAM_Avail_Percent] --процент свободного ОЗУ на сервере
, a.[Server_physical_memory_Mb] --сколько всего ОЗУ на сервере в МБ
, a.[SQL_server_committed_target_Mb] --сколько всего ОЗУ выделено под MS SQL Server в МБ
, a.[SQL_server_physical_memory_in_use_Mb] --сколько всего ОЗУ потребляет MS SQL Server в данный момент времени в МБ
, a.[SQL_RAM_Avail_Percent] --поцент свободного ОЗУ для MS SQL Server относительно всего выделенного ОЗУ для MS SQL Server
, a.[StateMemorySQL] --достаточно ли ОЗУ для MS SQL Server
, a.[SQL_RAM_Reserve_Percent] --процент выделенной ОЗУ для MS SQL Server относительно всего ОЗУ сервера
--достаточно ли ОЗУ для сервера
, (case when a.[RAM_Avail_Percent]<10 and a.[RAM_Avail_Percent]>5 and a.[TotalAvailOSRam_Mb]<8192 then 'Warning' when a.[RAM_Avail_Percent]<=5 and a.[TotalAvailOSRam_Mb]<2048 then 'Danger' else 'Normal' end) as [StateMemoryServer]
from
(
select cast(a0.available_physical_memory_kb/1024.0 as int) as TotalAvailOSRam_Mb
, cast((a0.available_physical_memory_kb/casT(a0.total_physical_memory_kb as float))*100 as numeric(5,2)) as [RAM_Avail_Percent]
, a0.system_low_memory_signal_state
, ceiling(b.physical_memory_kb/1024.0) as [Server_physical_memory_Mb]
, ceiling(b.committed_target_kb/1024.0) as [SQL_server_committed_target_Mb]
, ceiling(a.physical_memory_in_use_kb/1024.0) as [SQL_server_physical_memory_in_use_Mb]
, cast(((b.committed_target_kb-a.physical_memory_in_use_kb)/casT(b.committed_target_kb as float))*100 as numeric(5,2)) as [SQL_RAM_Avail_Percent]
, cast((b.committed_target_kb/casT(a0.total_physical_memory_kb as float))*100 as numeric(5,2)) as [SQL_RAM_Reserve_Percent]
, (case when (ceiling(b.committed_target_kb/1024.0)-1024)<ceiling(a.physical_memory_in_use_kb/1024.0) then 'Warning' else 'Normal' end) as [StateMemorySQL]
from sys.dm_os_sys_memory as a0
cross join sys.dm_os_process_memory as a
cross join sys.dm_os_sys_info as b
cross join sys.dm_os_sys_memory as v
) as a;
然後，您可以通過以下查詢確定 MS SQL Server 的實例消耗了分配給它的所有內存：
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
如果 SQL_server_physical_memory_in_use_Mb 始終大於或等於 SQL_server_comfilled_target_Mb，則應檢查等待統計信息。
要通過等待統計信息確定 RAM 的不足，讓我們創建 inf.vWaits 視圖：
創建 inf.vWaits 視圖
CREATE view [inf].[vWaits] as
WITH [Waits] AS
(SELECT
[wait_type], --имя типа ожидания
[wait_time_ms] / 1000.0 AS [WaitS],--Общее время ожидания данного типа в миллисекундах. Это время включает signal_wait_time_ms
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],--Общее время ожидания данного типа в миллисекундах без signal_wait_time_ms
[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 [waiting_tasks_count]>0
and [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
)
, ress as (
SELECT
[W1].[wait_type] AS [WaitType],
CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],--Общее время ожидания данного типа в миллисекундах. Это время включает signal_wait_time_ms
CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],--Общее время ожидания данного типа в миллисекундах без signal_wait_time_ms
CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],--Разница между временем сигнализации ожидающего потока и временем начала его выполнения
[W1].[WaitCount] AS [WaitCount],--Число ожиданий данного типа. Этот счетчик наращивается каждый раз при начале ожидания
CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 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
)
SELECT [WaitType]
,MAX([Wait_S]) as [Wait_S]
,MAX([Resource_S]) as [Resource_S]
,MAX([Signal_S]) as [Signal_S]
,MAX([WaitCount]) as [WaitCount]
,MAX([Percentage]) as [Percentage]
,MAX([AvgWait_S]) as [AvgWait_S]
,MAX([AvgRes_S]) as [AvgRes_S]
,MAX([AvgSig_S]) as [AvgSig_S]
FROM ress
group by [WaitType];
在這種情況下，您可以使用以下查詢確定 RAM 是否不足：
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
這裡需要關注Percentage和AvgWait_S兩個指標。 如果它們總體上很重要，則很有可能沒有足夠的 RAM 用於 MS SQL Server 實例。 有效值是針對每個系統單獨確定的。 但是，您可以從以下值開始：Percentage>=1 且 AvgWait_S>=0.005。
要將指標輸出到監控系統（例如Zabbix），您可以創建以下兩個查詢：
- RAM 佔用了多少種等待類型的百分比（所有此類等待類型的總和）：
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - 有多少 RAM 等待類型以毫秒為單位（所有此類等待類型的所有平均延遲的最大值）：
select coalesce(max([AvgWait_S])*1000, 0.00) as [AvgWait_MS] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' );
根據獲取的這兩個指標值的動態變化，我們可以得出MS SQL Server實例是否有足夠的RAM的結論。
CPU過載檢測方法
要確定處理器時間不足，使用 sys.dm_os_schedulers 系統視圖就足夠了。 這裡，如果runnable_tasks_count持續大於1，那麼很有可能MS SQL Server實例的核心數量不夠。
要將指標輸出到監控系統（例如Zabbix），您可以創建以下查詢：
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
根據該指標獲得的值的動態，我們可以得出MS SQL Server實例是否有足夠的處理器時間（CPU核心數量）的結論。
但是，請務必記住，請求本身可以同時請求多個線程。 有時優化器無法正確估計查詢本身的複雜性。 那麼該請求可能被分配了太多的線程，無法在給定時間同時處理。 這還會導致與處理器時間不足相關的等待類型，以及使用特定 CPU 核心的調度程序的隊列增長，即 runnable_tasks_count 指示器在這種情況下會增長。
這種情況下，在增加CPU核心數之前，需要正確配置MS SQL Server實例本身的並行屬性，從2016版本開始，正確配置所需數據庫的並行屬性：
這裡需要注意以下幾個參數：
- 最大並行度 - 設置可以分配給每個請求的最大線程數（默認值為 0 - 僅受操作系統本身和 MS SQL Server 版本的限制）
- Cost Threshold for Parallelism - 估計的並行成本（默認為 5）
- Max DOP - 設置可以在數據庫級別分配給每個查詢的最大線程數（但不超過“最大並行度”屬性的值）（默認值為 0 - 僅受操作系統本身限制，並且MS SQL Server 的版本，以及對 MS SQL Server 整個實例的“最大並行度”屬性的限制）
在這裡不可能為所有情況提供同樣好的方法，即您需要分析繁重的查詢。
根據我自己的經驗，我建議 OLTP 系統使用以下操作算法來設置並行屬性：
- 首先通過將實例範圍的最大並行度設置為 1 來禁用並行性
- 分析最重的請求並為其選擇最佳線程數
- 將最大並行度設置為從步驟 2 中獲得的選定最佳線程數，對於特定數據庫，設置從步驟 2 中為每個數據庫獲得的最大 DOP 值
- 分析最重的請求並確定多線程的負面影響。 如果是，則增加並行度的成本閾值。
對於1C、Microsoft CRM和Microsoft NAV等系統，大多數情況下，禁止多線程是合適的
另外，如果有標準版，那麼在大多數情況下，禁止多線程是合適的，因為該版本的CPU核心數量有限。
對於OLAP系統，上述算法並不適合。
根據我自己的經驗，我建議 OLAP 系統使用以下操作算法來設置並行屬性：
- 分析最重的請求並為其選擇最佳線程數
- 將最大並行度設置為從步驟 1 中獲得的選定最佳線程數，對於特定數據庫，設置從步驟 1 中為每個數據庫獲得的最大 DOP 值
- 分析最繁重的查詢並確定限制並發性的負面影響。 如果是，則降低並行成本閾值，或重複此算法的步驟 1-2
也就是說，對於OLTP系統，我們從單線程轉向多線程，而對於OLAP系統，相反，我們從多線程轉向單線程。 因此，您可以為特定數據庫和整個 MS SQL Server 實例選擇最佳並行設置。
同樣重要的是要了解，根據 MS SQL Server 性能監控的結果，並行屬性的設置需要隨著時間的推移而更改。
設置跟踪標誌的指南
根據我自己和同事的經驗，為了獲得最佳性能，我建議在 2008-2016 版本的 MS SQL Server 服務的運行級別設置以下跟踪標誌：
- 610 - 減少索引表插入的記錄。 可以幫助插入到具有許多記錄和許多事務的表中，並頻繁地長時間等待索引的更改
- 1117 - 如果文件組中的某個文件滿足自動增長閾值要求，則文件組中的所有文件都會增長
- 1118 - 強制所有對象位於不同的範圍（禁止混合範圍），這最大限度地減少了掃描 SGAM 頁的需要，該頁用於跟踪混合範圍
- 1224 - 根據鎖的數量禁用鎖升級。 但是，過多的內存使用可能會觸發鎖升級
- 2371 - 將固定自動統計更新閾值更改為動態自動統計更新閾值。 對於更新大型表的查詢計劃很重要，其中不正確的記錄計數會導致錯誤的執行計劃
- 3226 - 禁止在錯誤日誌中顯示備份成功消息
- 4199 - 包括對 CU 和 SQL Server Service Pack 中發布的查詢優化器的更改
- 6532-6534 - 包括空間數據類型查詢操作的性能改進
- 8048 - 將 NUMA 分區內存對象轉換為 CPU 分區內存對象
- 8780 - 為查詢計劃啟用額外的時間分配。 一些沒有此標誌的請求可能會被拒絕，因為它們沒有查詢計劃（非常罕見的錯誤）
- 8780 - 9389 - 為批處理模式語句啟用額外的動態授予內存緩衝區，這允許批處理模式操作員請求更多內存，並避免在有更多內存可用時將數據移動到 tempdb
同樣在 2016 年之前，啟用跟踪標誌 2301 很有用，它可以增強決策支持優化，從而有助於選擇更正確的查詢計劃。 然而，從 2016 版本開始，它通常會對相當長的整體查詢執行時間產生負面影響。
另外，對於具有大量索引的系統（例如，對於 1C 數據庫），我建議啟用跟踪標誌 2330，它會禁用索引使用情況的收集，這通常會對系統產生積極影響。
有關跟踪標誌的更多信息，請參閱
從上面的鏈接來看，考慮 MS SQL Server 的版本和構建也很重要，對於較新的版本，某些跟踪標誌默認啟用或無效。
您可以分別使用 DBCC TRACEON 和 DBCC TRACEOFF 命令打開和關閉跟踪標誌。 欲了解更多詳情，請參閱
您可以使用 DBCC TRACESTATUS 命令獲取跟踪標誌的狀態：
為了將跟踪標誌包含在 MS SQL Server 服務的自動啟動中，您必須轉到 SQL Server 配置管理器並通過服務屬性中的 -T 添加這些跟踪標誌：
結果
本文對監控 MS SQL Server 的一些方面進行了分析，借助這些方面您可以快速識別 RAM 和空閒 CPU 時間的不足，以及其他一些不太明顯的問題。 已經回顧了最常用的跟踪標誌。
來源：
»
»
»
»
»
»
»
來源： www.habr.com