序文
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_committed_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 インジケーターに注意する必要があります。 全体として重要な場合は、MS SQL Server インスタンスに十分な RAM がない可能性が非常に高くなります。 必須の値はシステムごとに個別に決定されます。 ただし、次のインジケーターから始めることができます: Percentage>=1 および AvgWait_S>=0.005。
インジケーターを監視システム (Zabbix など) に出力するには、次の XNUMX つのクエリを作成できます。
- 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' );
これら XNUMX つのインジケーターで取得された値のダイナミクスに基づいて、MS SQL Server インスタンスに十分な RAM があるかどうかを結論付けることができます。
CPUの過負荷を検知する方法
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 のエディションによってのみ制限されます)
- 並列処理のコストしきい値 - 並列処理の推定コスト (デフォルトは 5)
- Max DOP - データベース レベルで各クエリに割り当てることができるスレッドの最大数を設定します (ただし、「Max Degree of Parallelism」プロパティの値を超えない) (デフォルトでは 0 - オペレーティング システムによってのみ制限されます) MS SQL Server 自体と MS SQL Server のエディション、および MS SQL Server インスタンス全体の「並列処理の最大度」プロパティの制限)
すべてのケースに対して同等に優れたレシピを提供することは不可能です。つまり、難しいクエリを分析する必要があります。
私自身の経験に基づいて、OLTP システムで並列処理プロパティを構成するには、次のアクション アルゴリズムをお勧めします。
- まず、インスタンス全体のレベルで Max Degree of Parallelism を 1 に設定して、並列処理を無効にします。
- 最も重いクエリを分析し、それらに最適なスレッド数を選択します。
- ステップ 2 で取得した、選択した最適なスレッド数に [最大並列度] を設定し、特定のデータベースについては、各データベースのステップ 2 で取得した [最大 DOP] 値を設定します。
- 最も重いクエリを分析し、マルチスレッドの悪影響を特定します。 その場合は、並列処理のコストしきい値を増やします。
1C、Microsoft CRM、Microsoft NAV などのシステムの場合、ほとんどの場合、マルチスレッドを禁止することが適切です。
また、Standard エディションをお持ちの場合は、このエディションでは CPU コアの数が制限されているため、ほとんどの場合、マルチスレッドの禁止が適切です。
上記のアルゴリズムは OLAP システムには適していません。
私自身の経験に基づいて、OLAP システムで並列処理プロパティを構成するには、次のアクション アルゴリズムをお勧めします。
- 最も重いクエリを分析し、それらに最適なスレッド数を選択します。
- ステップ 1 で取得した、選択した最適なスレッド数に [最大並列度] を設定し、特定のデータベースについては、各データベースのステップ 1 で取得した [最大 DOP] 値を設定します。
- 最も重いクエリを分析し、同時実行性の制限による悪影響を特定します。 その場合は、並列処理のコストしきい値を下げるか、このアルゴリズムのステップ 1 ～ 2 を繰り返します。
つまり、OLTP システムの場合はシングル スレッドからマルチ スレッドに移行し、OLAP システムの場合は逆にマルチ スレッドからシングル スレッドに移行します。 このようにして、特定のデータベースと MS SQL Server インスタンス全体の両方に対して最適な並列処理設定を選択できます。
MS SQL Server のパフォーマンスの監視結果に基づいて、同時実行プロパティの設定を時間の経過とともに変更する必要があることを理解することも重要です。
トレース フラグの設定に関する推奨事項
私自身の経験と同僚の経験から、最適なパフォーマンスを得るために、2008 ～ 2016 バージョンの MS SQL Server サービス実行レベルで次のトレース フラグを設定することをお勧めします。
- 610 - インデックス付きテーブルへの挿入のログを削減します。 インデックスの変更で頻繁に長い WRITELOG 待機が発生する、多数のレコードと多数のトランザクションを含むテーブルへの挿入に役立ちます。
- 1117 - ファイル グループ内のファイルが自動拡張のしきい値を満たしている場合、ファイル グループ内のすべてのファイルが拡張されます
- 1118 - すべてのオブジェクトを強制的に異なるエクステントに配置します (混合エクステントを禁止します)。これにより、混合エクステントの追跡に使用される SGAM ページをスキャンする必要性が最小限に抑えられます。
- 1224 - ロック数に基づいてロック エスカレーションを無効にします。 ただし、過剰なメモリ使用量によりロックのエスカレーションが発生する可能性があります。
- 2371 - 固定自動統計更新しきい値を動的な自動統計更新しきい値に変更します。 レコード数を誤って定義すると誤った実行プランが発生する、大規模なテーブルのクエリ プランを更新する場合に重要です。
- 3226 - エラー ログ内のバックアップ成功メッセージを抑制します
- 4199 - SQL Server 更新プログラムのロールアップとサービス パックでリリースされたクエリ オプティマイザーへの変更が含まれています
- 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 時間の不足、およびその他のあまり明らかではない問題を迅速に特定できます。 最も一般的に使用されるトレース フラグが確認されました。
ソース：
»
»
»
»
»
»
»
出所： habr.com