SQL Server の並列処理の最適化
SQL Server データベース・インスタンスのパフォーマンスは、多くの場合、小さな調整によって改善できます。パフォーマンスは、単純な見落としによって低下することもあります。実際、SQL Server の一部の並列処理設定のデフォルト値は準最適です。さらに、ハードウェアのアップグレード後に、これらを再調整する必要があります。特に SQL Server が仮想マシン (VM) でホストされている場合、不適切なハードウェア構成からその他の問題が発生することがあります。
- 並列処理の最大限度 (MaxDoP)
- 並列処理のコストしきい値 (CTFP)
BigFix 10.0.2 以降では、/checksqlserverparallelism
BESAdmin コマンドを使用して、データベース・インスタンスの MaxDoP および CTFP 設定が適切に構成されているかどうかを確認すること、および後述する他の問題を検出することができます。
最高のパフォーマンスを得るための SQL Server MaxDoP の値
Microsoft は、「Configure the max degree of parallelism Server Configuration Option」で MaxDoP の設定を推奨しています。
サーバー構成 | プロセッサー数 | SQL Server 2008 ~ 2014 (10.x ~ 12.x) | SQL Server 2016 (13.x) 以降 |
単一の NUMA ノードを持つサーバー | 8 個以下の論理プロセッサー | 常に MAXDOP を論理プロセッサーの数以下にする | 常に MAXDOP を論理プロセッサーの数以下にする |
単一の NUMA ノードを持つサーバー | 8 個を超える論理プロセッサー | 常に MAXDOP を 8 にする | 常に MAXDOP を 8 にする |
複数の* NUMA ノードを持つサーバー | NUMA ノードあたり 8 個以下の論理プロセッサー | 常に MAXDOP を NUMA ノードあたりの論理プロセッサー数以下にする | 常に MAXDOP を NUMA ノードあたりの論理プロセッサー数以下にする |
複数の* NUMA ノードを持つサーバー | NUMA ノードあたり 8 個を超える論理プロセッサー | 常に MAXDOP を 8 にする | 常に MAXDOP を、NUMA ノード (MAX 値 16) あたりの論理プロセッサー数の半分にする |
SQL Server 2016 (13.x) 以降のバージョンでは、ソフト NUMA 機能はデフォルトで有効になっており、8 個を超える論理プロセッサーを搭載したハードウェア NUMA ノードを小さいソフト NUMA ノードに自動的に分割するように設定されています。ソフト NUMA ノードを手動で作成するように、または完全に無効にするようにソフト NUMA 機能を構成できます。詳細については、「Soft-NUMA (SQL Server)」を参照してください。
最高のパフォーマンスを得るための 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 で実行されているかどうかを検出するために、次の照会を実行して、以下の結果を確認できます。
virtual_machine_type >= 1
.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 インスタンスを右クリックし、「プロパティー」をクリックし、次に「プロセッサー」をクリックします。