Skip to content

MS_SQLServerBasicSettings

Jump to bottom
nishi_74322014 edited this page Aug 13, 2026 · 1 revision

SQL Server の基本的な設定

概要

ここでは、SQL Server 構築における一般的な設定と、
性能の向上につながる重要な「環境設定オプション」の設定と見込まれる効果について解説する。

基本的な考え方

  • SQL Server は、Windows Server（Windows 自動パラメタとチューニング（MS_WindowsAutoTuning.md））と同様に、
    既定のパラメータ設定で利用しても、大部分のユーザの負荷に対して適切に実行される。

  • ただし、高い負荷が長時間変化しないような場合、
    サーバの設定をチューニングすることにより、性能が向上する可能性がある。

  • 設定を誤ると、SQL Server が正しく動作しなくなるため、変更には注意が必要である。

    • 設定を変更する場合は、最初にシステム データベースをバックアップすること。
    • 設定の妥当性の確認には、性能検証テストや、運用テストが必要になる。

  • 参考

チューニング可能なメモリ領域

SQL Server インスタンスのアドレス空間は、SQL Server インスタンスが使用している、

  • 「実行コード」領域
  • 「プール メモリ」領域

の 2 つの領域から構成される。

このうち、チューニング可能な領域は、「プール メモリ」領域である。
「プール メモリ」は、SQL Server によって、動的にサイズが変更される。

「プール メモリ」と役割

項番 領域名 役割
1 システム レベルのデータ構造体 DB 記述子、ロック、テーブルなどの、SQL Server インスタンスにグローバルなデータを保持するデータ構造体
2 バッファ キャッシュ データ ページが読み取られるバッファ
3 プロシージャ・キャッシュ T-SQL の実行プランが入っているバッファ
4 ログ キャッシュ ログ ページの読み書きに使用するキャッシュ。ログ バッファとデータ バッファの同期を減らすため、バッファ キャッシュとは別に管理される。
5 接続コンテキスト 接続ごとに、「クエリやストアド プロシージャのパラメータ値」、「カーソル位置情報」、「現在参照されているテーブル」等の状態を記録するデータ構造体。

「プール メモリ」の自己チューニング

「プール メモリ」の自己チューニング

補足（最新化：「プール メモリ」という呼称）: 本文の「プール メモリ」は
SQL Server 2000 世代の用語で、現在のドキュメントでは
バッファ プールおよびメモリ クラークという枠組みで説明される。
また、SQL Server 2012 以降は
max server memory がバッファ プール以外の領域も含むようになり、
それ以前より広い範囲を制御する設定に変わっている。
現在の実使用量は以下で確認できる。

SELECT
    (physical_memory_in_use_kb / 1024) AS physical_memory_in_use_mb,
    (locked_page_allocations_kb / 1024) AS locked_pages_mb,
    process_physical_memory_low, process_virtual_memory_low
FROM sys.dm_os_process_memory;

SELECT TOP 10 type, SUM(pages_kb) / 1024 AS mb
FROM sys.dm_os_memory_clerks
GROUP BY type ORDER BY mb DESC;

「環境設定オプション」の確認

設定可能な「環境設定オプション」の「設定可能範囲」、「設定値」を確認するには、
以下を使用する。

Management Studio

SSMS（MS_SSMS.md）

sp_configure システム ストアドプロシージャ

環境設定オプション

種類

項番 種類 略号 説明
1 拡張オプション A このオプションを変更する場合は、SQL Server に詳しい技術者に確認する。
参照や設定をする場合は show advanced options を 1 に設定する必要がある。
2 再起動オプション RR 設定を有効にするには SQL Server インスタンスを再起動する必要がある。
3 自己設定オプション SC SQL Server がシステムのニーズに合わせて自己設定する。

確認・設定の方法

確認

sp_configure '<option name>'

  • 「SSMS（MS_SSMS.md）」などを使用して sp_configure を呼び出し、
    「環境設定オプション」を参照する。

  • パラメータを指定せずに実行した場合、sp_configure は、
    次に示す「環境設定オプション」の情報を、
    オプション名のアルファベットの昇順に並べて返す。

  • sp_configure で出力される情報

項番 列名 データ型 説明
1 name nvarchar(70) 環境設定オプションの名前
2 minimum int 環境設定オプションの最小値
3 maximum int 環境設定オプションの最大値
4 config_value int sp_configure で設定した環境設定オプションの値
5 run_value int 現在実行中の環境設定オプションの値

設定

sp_configure '<option name>', <value>

  • sp_configure を呼び出し、「環境設定オプション」を変更する。
  • 変更後、RECONFIGURE ステートメントを実行していない場合、
    変更結果が即時反映されない（config_value と、run_value が異なる）。
  • 即時反映させるためには、RECONFIGURE ステートメントを使用する（run_value を更新する）。
  • 「環境設定オプション」によっては、設定変更後、SQL Server インスタンスを
    再起動しないと、現在実行中の値（run_value）を更新できないものがあるため注意が必要である。
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure
GO

拡張オプションの設定

「拡張オプション」の設定を表示または変更するには、
show advanced options オプションを 1（有効）に設定する必要がある。

  • このオプションは、既定では 0（無効）に設定されている。
  • 必要に応じて show advanced options オプションを 1（有効）にする。
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure
GO

重要な環境設定オプション

max server memory

確保する「プール メモリ（ページングされるメモリ、仮想記憶のメモリ）」の最大値を設定する。
値が「物理メモリ」のサイズを超える場合、実際に確保される「プール メモリ」は、
「物理メモリ」の最大値に自動調整される。

  • 既定値 : 2,147,483,647（MB）
    • 既定値の場合、DB サーバに搭載されている全ての「物理メモリ」に合わせて、
      SQL Server インスタンスは可能な限りの「プール メモリ」を確保する。
    • このため、他のアプリケーションが「物理メモリ」を必要としている時には、
      ページングにより他のアプリケーションに「物理メモリ」を割り当てる。

max server memory

  • サーバ種類

    • SQL Server 専用の DB サーバの場合
      既定値の設定を使用する。

    • DB サーバが SQL Server 専用でない場合
      （他のアプリケーションまたは、他の SQL Server インスタンスが
      定期的に「物理メモリ」を必要とする場合）

      • 他のアプリケーションまたは、他の SQL Server インスタンスの使用する
        「物理メモリ」が不足しないように、当該 SQL Server インスタンスの、
        max server memory オプションの設定を変更する。
      • DB サーバに搭載されている全ての「物理メモリ」の合計から、
        他のアプリケーションに必要な「物理メモリ」を引いて、
        SQL Server インスタンスが確保する「プール メモリ」の最大値を計算する。
      • 他のアプリケーションに必要な「物理メモリ」は、
        起動に必要な「物理メモリ」の量だけでなく、
        ニーズの変化に対応できるように残す「物理メモリ」の量を考慮する。

  • sp_configure による設定

EXEC sp_configure 'max server memory', nnnn
RECONFIGURE
EXEC sp_configure
GO

nnnn の単位は、MB で指定する。

  • 現在の推奨値の目安（専用 SQL Server の場合）
物理メモリ 推奨 max server memory OS＆他プロセスに残す目安 備考
16–32 GB 物理メモリの 75–85% 4–6 GB 小規模～中規模
32–64 GB 物理メモリの 80–88% 6–10 GB 最も多いゾーン
64–128 GB 物理メモリの 85–90% 10–16 GB -
128 GB 以上 物理メモリの 88–92% 16–24 GB 以上 大規模環境

補足（専用サーバでも既定のままにしない）: 本文には
「SQL Server 専用の DB サーバの場合、既定値の設定を使用する」とあるが、
上の推奨値の表のとおり、専用サーバでも max server memory は明示設定する
のが現在の定石である。既定値（無制限）のままだと、

  • OS 自身やバックアップ エージェント、監視エージェントが
    メモリ不足に陥り、SQL Server のワーキング セットが
    ページ アウトされて劇的に遅くなる
  • 複数インスタンスを同居させている場合に取り合いになる

といった問題が起きうる。
併せて、SQL Server のサービス アカウントに
**「メモリ内のページのロック」**権限を付与しておくと、
OS によるページ アウトを防げる。

min server memory

確保する「プール メモリ（ページングされるメモリ、仮想記憶のメモリ）」の最小値を設定する。

min server memory オプションを 1GB に設定しても、
SQL Server が自動的に 1GB の「物理メモリ」を取得するわけではない。
その時々のサーバの負荷に基づいて、必要に応じて割り当てられる。

  • 既定値 : 0（MB）
    • 既定値の場合、他のアプリケーションが「物理メモリ」を必要として、
      ページングが発生している場合、動的に確保した「プール メモリ」を解放する。

min server memory

  • サーバ種類

    • SQL Server 専用の DB サーバの場合
      既定値の設定を使用する。

    • DB サーバが SQL Server 専用でない場合
      （他のアプリケーションまたは、他の SQL Server インスタンスにより
      「物理メモリ」を奪われ、当該 SQL Server の使用する「物理メモリ」が
      不足する可能性がある場合）

      • 「物理メモリ」が不足しないように、当該 SQL Server インスタンスの、
        min server memory オプションの設定を変更する。
      • 例えば、サーバが SQL Server の DBMS 機能と、印刷とファイル サービス機能を
        提供する場合、印刷とファイル サービス機能に「物理メモリ」を消費され、
        SQL Server 用の「物理メモリ」が不足し、応答時間が遅くならないようにする。

  • sp_configure による設定

EXEC sp_configure 'min server memory', nnnn
RECONFIGURE
EXEC sp_configure
GO

nnnn の単位は、MB で指定する。

max server memory = min server memory (メモリの固定)

  • max server memory オプションと min server memory オプションを同じ値に設定する。
  • これにより、設定したサイズまで確保した「プール メモリ」を、その後、解放しないようになる。
  • DB サーバが SQL Server 専用でない場合
    SQL Server インスタンスで確保する「プール メモリ」を固定し、
    他の「物理メモリ」が不足しないように、この設定を適用する。
    • 当該 SQL Server の使用する「物理メモリ」
    • 他のアプリケーションまたは、他の SQL Server インスタンスの使用する「物理メモリ」

補足（固定は仮想化環境で特に有効）: 仮想マシン上の SQL Server では、
ハイパーバイザ側の動的メモリ（バルーニング）と
SQL Server 側の動的確保がぶつかると性能が不安定になる。
仮想マシンのメモリを固定し、max = min で SQL Server 側も固定する
のが安定運用の定石である
（仮想化アーキテクチャ参照）。

recovery interval （min）

SQL Server の障害復旧を参照。

データベースの設定

自動拡張・自動圧縮

データ ファイルの圧縮と拡張（MS_DataFileShrinkAndGrow.md）

復旧モデル

SQL Server の障害復旧

補足（最新化：まず確認したい構成オプション）: 元ページはメモリ設定を
中心に構成されているが、現在の新規構築で併せて確認すべき項目を挙げておく。

オプション 既定 推奨と理由
max degree of parallelism 0（無制限） OLTP では NUMA ノードあたりの論理コア数（最大 8）を目安に
cost threshold for parallelism 5 25〜50。既定値は 1990 年代基準で小さすぎる
optimize for ad hoc workloads 0 1。1 回しか実行されないアドホック クエリでプラン キャッシュが埋まるのを防ぐ
backup compression default 0 1。バックアップ時間とサイズを削減（CPU は増える）
remote admin connections 0 1。緊急時に DAC でリモート接続できるようにする
xp_cmdshell 0 0 のまま。有効化は攻撃面を大きく広げる
blocked process threshold 0 一時的に 5〜20 秒に設定してブロッキング レポートを採取（SQL Server でのロック・タイムアウト）

SQL Server 2016 以降は、DB 単位で調整できる
ALTER DATABASE SCOPED CONFIGURATION（MAXDOP、レガシー基数推定、
パラメータ スニッフィングの無効化など）もあるため、
インスタンス全体を変えずに済むケースが増えている。

内部リンク

参考

Microsoft SQL Server Japan Support Team Blog

＠IT：SQL Server 2000チューニング全工程

真・Dr. K's SQL Serverチューニング研修

サーバー構成オプション (SQL Server)

https://learn.microsoft.com/ja-jp/sql/database-engine/configure-windows/server-configuration-options-sql-server

元ページには全 69 個の構成オプションの一覧（最小値・最大値・既定値）が
掲載されていたが、値がバージョンによって変わるため、
本ページでは特に重要なものに絞って掲載する。
網羅的な一覧は上記の公式ドキュメント、または
SELECT * FROM sys.configurations ORDER BY name; で確認すること。

構成オプション 種類 最小 最大 既定
show advanced options - 0 1 0
max server memory (MB) A、SC 16 2147483647 2147483647
min server memory (MB) A、SC 0 2147483647 0
recovery interval (min) A、SC 0 32767 0
max degree of parallelism A 0 32767 0
cost threshold for parallelism A 0 32767 5
optimize for ad hoc workloads A 0 1 0
backup compression default - 0 1 0
blocked process threshold (s) A 0 86400 0
fill factor (%) A、RR 0 100 0
max worker threads A 128 32767 0（自動）
remote admin connections - 0 1 0
user connections A、RR、SC 0 32767 0（無制限）
nested triggers - 0 1 1
clr enabled - 0 1 0
xp_cmdshell A 0 1 0

移行メモ（旧バージョン専用のオプション）: 元の一覧に含まれていた
allow updates / open objects / set working set size /
affinity mask 系 / priority boost / lightweight pooling などは、
非推奨または旧バージョン専用であり、現在は使用しない
（priority boost と lightweight pooling は
有効化すると不安定になるため特に触らないこと）。
affinity mask 系は ALTER SERVER CONFIGURATION SET PROCESS AFFINITY に
置き換えられている。

Tags: 移行, データアクセス, SQL Server

このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。

NetDevInfraWiki

マイクロソフト系技術情報 Wiki
Open 棟梁 Wiki

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally