SQL ServerのMAXDOPとCost Threshold for Parallelismを確認・変更する方法

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: VIEW SERVER STATE または VIEW ANY DEFINITION
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    c.configuration_id,
    c.name,
    c.value           AS configured_value,   -- 構成された値
    c.value_in_use    AS running_value,      -- 実際に使われている値
    c.minimum,
    c.maximum,
    c.is_dynamic,     -- 1: 再起動なしで反映される
    c.is_advanced,    -- 1: show advanced options が必要
    c.description
FROM sys.configurations AS c
WHERE c.name IN ('max degree of parallelism', 'cost threshold for parallelism')
ORDER BY c.name;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: VIEW SERVER STATE
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    cpu_count                       AS logical_cpu_count,
    hyperthread_ratio,
    scheduler_count,
    softnuma_configuration_desc     -- SQL Server 2016 以降。それ以前の版では列を外すこと
FROM sys.dm_os_sys_info;

-- NUMA ノードごとのスケジューラ配置
SELECT
    parent_node_id                  AS numa_node_id,
    COUNT(*)                        AS scheduler_count
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
GROUP BY parent_node_id
ORDER BY parent_node_id;

-- 対象: SQL Server 2008 以降
-- 権限: ALTER SETTINGS（sysadmin / serveradmin 相当）
-- 変更作業: あり（show advanced options の切り替えはサーバー構成の変更）
-- Production 実行: 可能。ただし確認目的なら sys.configurations を推奨
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

EXEC sp_configure 'max degree of parallelism';
EXEC sp_configure 'cost threshold for parallelism';

-- 対象: SQL Server 2008 以降（Amazon RDS ではパラメータグループ経由）
-- 権限: ALTER SETTINGS（sysadmin / serveradmin 相当）
-- 変更作業: あり（サーバー全体の実行プラン選択に影響）
-- Production 実行: 可能。ただし変更前後の測定と切り戻し手順が前提
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

-- 下の値は「例示」であり推奨値ではない。必ず自環境の測定結果に置き換えること
DECLARE @maxdop         int = 4;
DECLARE @cost_threshold int = 50;

EXEC sp_configure 'max degree of parallelism', @maxdop;
RECONFIGURE;

EXEC sp_configure 'cost threshold for parallelism', @cost_threshold;
RECONFIGURE;
GO

-- 反映結果の確認
SELECT name, value, value_in_use
FROM sys.configurations
WHERE name IN ('max degree of parallelism', 'cost threshold for parallelism');

-- 対象: SQL Server 2016 以降 / Azure SQL Database
-- 権限: ALTER ANY DATABASE SCOPED CONFIGURATION
-- 変更作業: あり（対象データベース全体の実行プラン選択に影響）
-- Production 実行: 可能。対象DB全体に効くため影響範囲を確認すること
USE [SampleDB];
GO

-- 現在値の確認（参照のみ）
SELECT configuration_id, name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name = 'MAXDOP';

-- 変更（0 はサーバー設定に従う）
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: 対象オブジェクトへの参照権限
-- 変更作業: あり（このクエリの実行プランのみ変わる）
-- Production 実行: 可能（影響はこのクエリに限定）
SELECT col1, col2
FROM dbo.SampleTable
WHERE col1 > 0
OPTION (MAXDOP 1);