SQL Server の並列処理の最適化

最高のパフォーマンスを得るための SQL Server MaxDoP の値

Microsoft は、「Configure the max degree of parallelism Server Configuration Option」で MaxDoP の設定を推奨しています。

SQL Server 2016 (13.x) 以降のバージョンでは、ソフト NUMA 機能はデフォルトで有効になっており、8 個を超える論理プロセッサーを搭載したハードウェア NUMA ノードを小さいソフト NUMA ノードに自動的に分割するように設定されています。ソフト NUMA ノードを手動で作成するように、または完全に無効にするようにソフト NUMA 機能を構成できます。詳細については、「Soft-NUMA (SQL Server)」を参照してください。

MaxDoP を変更しても、SQL Server 2012 または 2019 を再起動する必要はありません。

最高のパフォーマンスを得るための SQL Server CTFP の値

Microsoft では、CTFP の設定に関する推奨事項は提供していません。

一般的には、この最適な値がワークロードに基づくということを理解したうえで、15 ～ 50 の値に設定することをお勧めします。

BigFix サーバーが生成するデータベース・ワークロードの場合、CTFP を 50 に設定した方が、5 のままにするよりもパフォーマンスが向上することがテスト結果で示されています。

CTFP を変更しても、SQL Server 2012 以降のバージョン (2019 など) を再起動する必要はありません。

トラブルシューティング・シナリオ 1: VM でライセンス交付されたコアの使用率が低下する

ライセンスの制限により、SQL Server では使用可能なすべての CPU コアを使用できない場合があります。

特に、一部の SQL Server エディション (Express、Web、および Standard) のライセンスは、「n 個のソケットまたは m 個コアのうちの小さい方に制限」されます。

例えば、SQL Server 2019 Express Edition のデータベース・インスタンスは、その最大コンピューティング・キャパシティーに関して「1 個のソケットまたは 4 個のコアのうちの小さい方に制限」されます。「Editions and supported features of SQL Server 2019 (15.x)」の「Scale Limits」セクションを参照してください。そのため、このエディションの SQL Server では、同じソケットで使用できるコアは 4 個のみです。

この制限が原因で、SQL Server が仮想マシンにインストールされている場合に予期しない問題が発生する可能性があります。実際、(多くの仮想ソケットを使用し、ソケットあたりのコア数が少ない) 一般的な VM 構成を使用すると、SQL Server のライセンス制限により、SQL Server が使用できるコアの数が大幅に制限される場合があります。

例えば、SQL Server 2019 Express では最大 4 個のコアを使用できますが、SQL Server 2019 Express が 4 個のコアと 4 個のソケット (ソケットあたり 1 コア) を備えた VM にインストールされている場合は、1 個のコアしか使用できません。

別の例として、SQL Server 2019 Web ライセンスでは、「4 個のソケットまたは 16 個のコアのうちの小さい方」を使用できます。

VM に 16 個の (仮想) ソケットがあり、ソケットあたりのコア数が 1 個の場合、使用できるコア数は 16 個のうち 4 個のみです。つまり、それぞれ 1 個のコアを持つ 4 個のソケットを使用することになります。

ただし、VM CPU の構成を変更して、例えば、4 個のソケットを使用し、ソケットあたりのコア数が 4 個の場合、SQL Server ですべてのコアを使用できます。

新しい VM を作成する場合、CPU ソケットの数と CPU コアの合計数を慎重に選択してください。

VMware vSphere Client を使用する場合、VM の作成時またはその設定の編集時に、「仮想ハードウェア」タブの「CPU」メニューを展開して、 「ソケットあたりのコア数」の数を構成できます。これにより、ソケットの数が決まります。

SQL Server が VM で実行されているかどうかを検出するために、次の照会を実行して、以下の結果を確認できます。

SELECT virtual_machine_type
FROM sys.dm_os_sys_info

select COUNT(*) AS sqlUsedLogicProcs 
from sys.dm_os_schedulers 
where status = 'VISIBLE ONLINE'

SELECT cpu_count AS LogicalCpuCount
FROM sys.dm_os_sys_info

SELECT SERVERPROPERTY( 'edition' )

Microsoft では、「SERVERPROPERTY (Transact-SQL)」で返される値の全リストを提供しています。Azure 値は無視してください。

トラブルシューティング・シナリオ 2: 使用されるコアの分配が不均一

SQL Server でライセンス交付されたすべてのコアを使用できる場合でも、パフォーマンスが最適ではないことがあります。

理想的には、SQL Server でコンピューターの (仮想または物理) ハードウェアのすべてのコアを使用できるライセンスを取得します。

SQL Server でコンピューターのすべてのコアを使用できない場合、SQL Server で使用できるコアが、そのコンピューターのハードウェア NUMA ノード間で均等に分配されている場合には、パフォーマンスへの影響は小さくなります。SQL Server で使用可能なコアのごく一部しか使用できない場合 (これは理想的ではありません)、使用ライセンスがある n 個のソケットをホストする NUMA ノードのコアのみ使用できます。

物理ハードウェアでは、ハードウェア NUMA ノードの数は、通常、ソケットの数に一致します。または、ソケットの数の倍数となる場合も少数ですがあります。例えば、物理ソケットに 1 つ以上の NUMA ノードが含まれる場合があります。

仮想ハードウェアでは、これが反対になる場合があります。ハードウェア NUMA ノードの数が、ソケットの数よりも少ない場合があります。つまり、複数のソケットが同じハードウェア NUMA ノードに属する場合があります。

Windows では、リソース・モニター (resmon.exe) を使用して、コンピューターのハードウェア NUMA ノードの数を確認できます。

「CPU」タブの右側のパネルに、NUMA ノードと CPU プロセッサーごとのグラフが表示されます。

パネルに CPU グラフのみ表示される場合は、すべての CPU をホストする 1 つの NUMA ノードのみあることを意味します。

select COUNT( DISTINCT memory_node_id ) as hwNumaNodes 
from sys.dm_os_memory_nodes 
where memory_node_id <> 64

SQL Server 2016 以降では、自動ソフト NUMA 機能により、コア数が 8 個を超える仮想ハードウェア NUMA ノードまたは物理ハードウェア NUMA ノードは、複数のソフト NUMA ノードに分割されます。この分割によって、異なるハードウェア NUMA ノードにコアを不均等に割り当てた場合のパフォーマンスの低下が解決されるとは限りません。実際にはパフォーマンスの低下が隠されるだけかもしれません。

select COUNT(*) as usedNumaNodes, 
MIN(online_scheduler_count) as minUsedLogicProcsPerNumaNode, 
MAX(online_scheduler_count) as maxUsedLogicProcsPerNumaNode 
from sys.dm_os_nodes 
where online_scheduler_count > 0 and node_state_desc not like '%DAC%'

2 個のソケットと 20 個のコア (ソケットあたり 10 個のコア) を搭載したコンピューター上の SQL Server Web をシナリオ例として考えてみます。

このセットアップでは、SQL Server Web で、システムが提供する合計 20 個のコアのうち、ライセンスを受けた 16 個のコアをすべて使用できます。

ただし、合計 20 個のコアから 16 個の使用コアをどのように選択するかで、パフォーマンスに違いが出てくる可能性があります。

ソケットごとに NUMA ノードがあると仮定すると、次の例のように、使用コアが不均一に分配される可能性があります。

• NUMA ノード 0 で 10 個のコア
• NUMA ノード 1 での 6 個コア

次の例は、使用コアのより適切な分配を示しています。

• NUMA ノード 0 で 8 個のコア
• NUMA ノード 1 で 8 個のコア

ALTER SERVER
CONFIGURATION SET PROCESS AFFINITY CPU

詳細については、次の Microsoft の資料の「Setting process affinity」セクションを参照してください:ALTER SERVER CONFIGURATION (Transact-SQL)

追加情報の収集

追加情報を収集するには、BESAdmin を使用して、/checksqlserverparallelism コマンドに /extrainfo フラグを渡します。

また、SQL Server のログを調べて、有用な詳細情報を抽出することもできます。

出力が返されない場合、ログのローテーションによって対象のログ行が削除された可能性があります。

SET NOCOUNT ON; 
DECLARE @logData TABLE( LogDate DATETIME, ProcInfo NVARCHAR(64), LogText NVARCHAR(1024) ); 
INSERT INTO @logData 
EXEC sys.xp_readerrorlog 0, 1, N'SQL Server detected ', N' socket', null, null, N'DESC'; 
SELECT TOP 1 [LogText] 
FROM @logData;

出力の例:

SQL Server detected 1 sockets with 2 cores per socket and 2 logical processors per socket, 2 total logical processors; using 2 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

SET NOCOUNT ON; 
DECLARE @logData TABLE( LogDate DATETIME, ProcInfo NVARCHAR(64), LogText NVARCHAR(MAX) ); 
INSERT INTO @logData 
EXEC sys.xp_readerrorlog 0, 1, N'Node configuration: ', N' CPU mask: ', null, null, N'DESC'; 
SELECT [LogText] 
FROM @logData;
LogText

出力の例:

Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

CPU マスクが手動で設定されている場合、SQL Server Management Studio を使用して表示できます。

DB インスタンスを右クリックし、「プロパティー」をクリックし、次に「プロセッサー」をクリックします。