Oracle DBA のための SQL Server 2017 構成と管理のポイント

Presentation on theme: "Oracle DBA のための SQL Server 2017 構成と管理のポイント"— Presentation transcript:

1 Oracle DBA のための SQL Server 2017 構成と管理のポイント
エディフィスト ラーニング株式会社

2 Agenda SQL Server と Oracle のアーキテクチャ比較 SQL Server の構成と稼働状況の確認
運用管理タスクと自動化 パフォーマンス監視

3 1. SQL Server と Oracle のアーキテクチャ比較
インスタンス メモリとプロセス データベース データベースと物理ファイル SQL Server のトランザクション ログ ファイル データベースのバックアップ SQL Server データベースの復元と復旧 インデックス SQL Server の付加列インデックス 列ストア インデックス 認証 SQL Server の包含データベース データベースに対する多層防御

4 ① インスタンス SQL Server Oracle サーバー上での SQL Server サービスの実行単位
既定のインスタンス (1 個のみ) インスタンス ID は 「MSSQLSERVER」 接続時はコンピューター名のみを使用 名前付きインスタンス (コンピューターに 49 個まで) コンピューター名の後ろに \ 記号で区切られたインスタンス ID を指定 各インスタンスには 32,767 個までのデータベースを作成できる Oracle サーバー上でのデータベースの実行単位 複数のインスタンスを構成可能 インスタンスは単一のデータベースを持つ (シングル テナント) Standard Edition では、プラガブル DB を 1 個だけ追加できる 通常、データベースの移行・統合時に使用 サービス サービス サービス サービス 既定のインスタンス 名前付きインスタンス Ora1 Ora2 SQL1 SQL1\Staging Microsoft SQL Server \MSSQL14.MSSQLSERVER Microsoft SQL Server \MSSQL14.STAGING 10.2.0¥admin¥Ora1 10.2.0¥admin¥Ora2 データベース コンテナー データベース プラガブル データベース システム データベース ユーザー データベース システム データベース ユーザー データベース ユーザー データベース

5 ② メモリとプロセス Oracle SQL Server 最小 IO 単位は、2～32 Kのブロック
REDO ログ ファイルは、アーカイブ ログ モードを使用することで、自動でアーカイブされる SQL Server 最小 IO 単位は、8 K 固定のページ トランザクション ログ ファイルは、循環使用され、選択した復旧モデルにより異なる処理となる システム グローバル領域 データベース バッファ プール データベース データ ファイル 共有プール プロシージャ キャッシュ データ ファイル データ ブロック (2~32K) Worker Thread 実行計画 データ ページ (8K 固定) サーバー プロセス 実行プラン 制御ファイル Lazy Writer DBWn バッファ キャッシュ データベース バッファ キャッシュ CKPT Check Point トランザクション ログ ファイル 一時ファイル データページ データブロック DBWn Log Writer ログ キャッシュ REDO ログ バッファ REDO ログ ファイル クエリ ワークスペース tempdb LGWR Worker Thread ロック アーカイブ ログ ファイル ARCn その他

6 ③ データベース ・・・・ Oracle SQL Server データベースは用途の異なる複数の表領域で構成される
データベースは、システム データベース (5 種類) とユーザー データベースに分類される データベース システム データベース 制御ファイル master tempdb model msdb resource SYSTEM 表領域 SYSAUX 表領域 一時 表領域 UNDO 表領域 ユーザー データベース ユーザー 表領域 ユーザー データベース ユーザー データベース ・・・・ 名前 説明 制御ファイル データベースの物理構造を記録したバイナリ ファイル SYSTEM 表領域 データディクショナリ (テーブル、ビュー、インデックスの定義情報、組み込みパッケージ、ユーザー定義のストアドプロシージャのソースやコンパイル済みコード) を格納 SYSAUX 表領域 オプション機能のメタデータを格納 一時表領域 ソート、結合で使用される作業用表領域 UNDO 表領域 データベースの変更をロールバックするための情報を格納する表領域 ユーザー表領域 ユーザー定義のデータベース オブジェクト、およびデータを永続的に格納する表領域 名前 説明 master インスタンスとデータベースの構成情報、インスタンス レベルのオブジェクトを格納 model ユーザー データベースのテンプレート msdb ジョブ、警告、オペレータを格納し、SQL Server エージェントが使用する tempdb ソート、結合、インデックス操作、行バージョン管理などで使用される作業用データベースで、SQL Server 起動時に初期化される resource システム オブジェクト (sys) が格納される読み取り専用の隠しデータベース ユーザー データベース ユーザー定義のデータベース オブジェクト、およびデータを永続的に格納する

7 データベースと物理ファイル Oracle SQL Server SYSTEM 表領域にデータ ディクショナリが格納される
ユーザー表領域にユーザー定義のデータベース オブジェクト、およびデータを永続的に格納する データ ファイルとREDO ログ ファイルは配置先を分離する SQL Server PRIMARY ファイルグループのプライマリ データファイル (.mdf) にデータベース オブジェクトの定義情報が格納される ファイル グループは、テーブル、インデックスの配置先で複数のデータ ファイルで構成できる データ ファイルとトランザクション ログ ファイルは配置先を分離する 制御ファイル SYSTEM 表領域 (必須) PRIMARY ファイル グループ (必須) ユーザー データベース プライマリ データファイル：データベース オブジェクトの定義情報 データ ディクショナリ .mdf ユーザー表領域 (任意) ユーザー定義のファイル グループ (任意) データベース データ ファイルに構成 ・最大サイズ ・自動拡張 データ ファイルに構成 ・最大サイズ ・自動拡張 .ndf .ndf .ndf REDO ログ ファイル トランザクション ログ ファイル 一方のファイルがアーカイブ中に、他方のファイルへの書込みを保証するため、最低 2 つ作成する 各データベースごとに 1 つ以上作成する .ldf

8 SQL Server の復旧モデル トランザクション ログの保存形式と切り捨て方法に影響する 一括操作とは？
一括インポート操作 (bcp、BULK INSERT、INSERT・・・SELECT) CREATE INDEX 操作 ALTER INDEX REBUILD 操作 復旧モデル 説明 単純 トランザクション ログは、チェックポイント時に自動で切り捨てられる 完全バックアップの時点、差分バックアップの時点への復元が可能 完全 トランザクション ログ バックアップを実行しない限り、ログは切り捨てられない 障害直前の COMMIT 時点まで復旧が可能 完全バックアップの時点、差分バックアップの時点に加えて、トランザクション ログ バックアップを実行した特定時点 (STOPAT 句) を指定する復旧も可能 一括ログ 一括操作のパフォーマンス向上とログ領域の効率利用 一括操作を行っていない場合、「完全」 復旧モデルと同じ動作 一括操作を行った場合、最小ログが記録され、障害直前の COMMIT 時点までの復旧は可能だが、特定時点 (STOPAT 句) を指定する復旧は不可 Oracle のアーカイブ ログ モードに相当

9 SQL Server のトランザクション ログ ファイル 「完全」、および 「単純」 復旧モデルでの動作
トランザクション ログ ファイルは、内部的に仮想ログに分割され、循環使用される 各トランザクションには、ログ シーケンス番号 (LSN) が割り当てられる Min LSN は、最小復旧ログシーケンス番号と呼ばれ、最後のチェックポイント前に確定したトランザクションのログ シーケンス番号 仮想ログ 1 トランザクション ログ バックアップの実行 仮想ログ 2 仮想ログ 3 Min LSN 空きがない場合、ファイル サイズを自動拡張可能 論理ログの末尾 仮想ログ 4 仮想ログ 5 論理ログの先頭 仮想ログ 6 仮想ログ ファイル 論理ログ ファイル トランザクション ログ ファイル (物理) バックアップ後、切り捨てられる 仮想ログ 1 仮想ログ 2 トランザクション ログ バックアップの実行により、Min LSN を含む仮想ログより 前の仮想ログは切り捨てられる 仮想ログ 3 論理ログの先頭 仮想ログ 4 仮想ログ 5 Min LSN 論理ログの末尾 仮想ログ 6 バックアップ後、切り捨てられる 仮想ログ ファイル 論理ログ ファイル

10 「一括ログ」 復旧モデルを使用した運用 「一括ログ」 復旧モデルでは、一括操作以外の操作は 「完全」 復旧モデルと同様 利点 欠点
一括操作のトランザクション ログを最小化 トランザクション ログファイルには、一括操作が行われたという情報と領域の拡張情報等しか書き込まれない 一括操作のパフォーマンスが向上 欠点 トランザクション ログ バックアップ時に一括操作による変更エクステントがバックアップされるため、トランザクション ログ バックアップ時間が長くなる 復元時に時間指定ができない 推奨される運用 復旧モデルの切り替え 　完全　⇒ 一括ログ 復旧モデルの切り替え 　一括ログ　⇒ 完全 「完全」 「一括ログ」 「完全」 トランザクション ログ バックアップ トランザクション ログバックアップ 一括操作

11 ④ データベースのバックアップ データベースのオンライン物理バックアップ Oracle SQL Server 説明 全体バックアップ
完全バックアップ データベース全体をバックアップし、他のバックアップのベースラインとなる ファイル、または、ファイルグループを対象としたバックアップも可能 増分バックアップ 差分バックアップ 完全バックアップの作成後に変更されたエクステントのみをバックアップする Oracle のレベル 1 差分増分バックアップ REDO ログのアーカイブ トランザクション ログ バックアップ トランザクション ログのバックアップとログの非アクティブな部分の切り捨て バックアップ計画の例 時間の流れ 完全 バックアップ① トランザクション ログ バックアップ① トランザクション ログ バックアップ② 差分 バックアップ① トランザクション ログ バックアップ③ トランザクション ログ バックアップ④ ・・・・

12 SQL Server データベースの復元と復旧
障害時は、最初に末尾のトランザクション ログのバックアップを取得する その後、以下の順序で復元することで最新の状態に復元できる 直近の完全バックアップ 直近の差分バックアップ 直近の差分バックアップ以降のすべてのトランザクション ログ バックアップ 末尾のトランザクション ログのバックアップの復元時にデータベースを復旧する WITH NORECOVERY WITH RECOVERY ここで データベースが破損 データベース復元のプロセス 時間の流れ 完全 バックアップ① トランザクション ログ バックアップ① トランザクション ログ バックアップ② 差分 バックアップ① トランザクション ログ バックアップ③ トランザクション ログ バックアップ④ 末尾のトランザクション ログ バックアップ⑤

13 ⑤ インデックス Oracle SQL Server 説明 クラスター化インデックス 非クラスター化インデックス 索引構成表
リーフ ノードにデータ行を格納 テーブルに対して 1 個 作成可能、PRIMARY KEY 制約を設定した列に既定で作成される 索引 非クラスター化インデックス ヒープ テーブルの場合、リーフ ノードに 「ファイルID：ページ番号：行番号」 で構成される行識別子 (RID) を格納 クラスター化インデックスが作成されたテーブルの場合、RID の代わりにクラスター化インデックス キー値を格納 SQL Server では テーブルに対して 999 個まで作成可能、UNIQUE 制約を設定した列に既定で作成される コンポジット索引 複合インデックス SQL Server では、最大 32 列、キー列の合計サイズが 1,700 バイトまでの制約があるが、付加列インデックス(INCLUDE 句) を使用することで、サイズ制限を回避できる！ クラスター化インデックス 非クラスター化インデックス キー値 キー値 ルート ノード ルート ノード キー値 キー値 中間ノード 中間ノード キー値 キー値 キー値で物理的にソートされたリーフノード データ行 データ行 データ行 データ行 キー値で物理的にソートされたデータ行 リーフ ノード キー値：RID キー値：RID キー値：RID キー値：RID リーフ ノード RID Lookup ヒープ構造 データ行 データ行 データ行 データ行 データ行 CREATE CRUSTERED INDEX <インデックス名> ON <テーブル名>(キー列) RID Lookup を回避するためには、複合インデックスを活用する CREATE [NONCRUSTERED] INDEX <インデックス名> ON <テーブル名>(キー列)

14 最大 32 列、キー列の合計サイズが 1,700 バイトまでの制約がある
SQL Server の付加列インデックス 複合インデックス 付加列インデックス CREATE INDEX ncix_ID_Number_Color ON Product( ID,Number,Color ) CREATE INDEX ncix_ID_include_Number_Color ON Product( ID ) INCLUDE( Number,Color ) 最大 32 列、キー列の合計サイズが 1,700 バイトまでの制約がある 複合インデックスの制約を回避する ページ:170 ページ:170 680 FR-R92B-58 Black 01:171 715 LJ-0192-M Multi 01:172 ・・ ・・・・・・ ・・・ ・・・・ ルート ノード 680 01:171 715 01:172 ・・ ・・・・ ルート ノード ページ:171 ページ:172 ページ:171 ページ:172 中間ノード 680 FR-R92B-58 Black 01:180 710 SO-B909-L White 01:181 ・・ ・・・・・・ ・・・ ・・・・ 715 LJ-0192-L Multi 01:182 720 FR-R92R-52 Red 01:183 ・・ ・・・・・・ ・・・ ・・・・ 中間ノード 680 01:180 710 01:181 ・・ ・・・・ 715 01:182 720 01:183 ・・ ・・・・ ページ:180 ページ:181 ページ:182 ページ:180 ページ:181 ページ:182 680 FR-R92B-58 Black 01:120:03 706 FR-R92R-58 Red 01:110:04 707 HL-U509-R 01:110:02 708 HL-U509 01:120:01 709 SO-B909-M White 01:100:05 ・・・ ・・・・ 710 SO-B909-L White 01:110:03 711 HL-U509-B Blue 01:130:04 712 CA-1098 Multi 01:100:04 713 LJ-0192-S 01:110:01 714 LJ-0192-M 01:120:05 ・・ ・・・・・・ ・・・ ・・・・ 715 LJ-0192-L Multi 01:100:03 716 LJ-0192-X 01:130:03 717 FR-R92R-62 Red 01:120:04 718 FR-R92R-44 01:120:02 719 FR-R92R-48 01:110:05 ・・ ・・・・・ ・・・・ 680 FR-R92B-58 Black 01:120:03 706 FR-R92R-58 Red 01:110:04 707 HL-U509-R 01:110:02 708 HL-U509 01:120:01 709 SO-B909-M White 01:100:05 ・・・ ・・・・ 710 SO-B909-L White 01:110:03 711 HL-U509-B Blue 01:130:04 712 CA-1098 Multi 01:100:04 713 LJ-0192-S 01:110:01 714 LJ-0192-M 01:120:05 ・・ ・・・・・・ ・・・ ・・・・ 715 LJ-0192-L Multi 01:100:03 716 LJ-0192-X 01:130:03 717 FR-R92R-62 Red 01:120:04 718 FR-R92R-44 01:120:02 719 FR-R92R-48 01:110:05 ・・ ・・・・・ ・・・・ リーフ ノード リーフ ノード

15 ⑥ 列ストア インデックス Oracle (Enterprise Edition のオプション)
集計クエリの高速化 SELECT OrderDate,SUM(Amount) FROM SalesOrder GROUP BY OrderDate Oracle (Enterprise Edition のオプション) 表 データベース バッファ キャッシュ インメモリ列ストア SQL Server (2016 SP1 以降は全エディションで使用可能) 行ストア テーブル ・・・行データをページに格納 非クラスター化列ストア インデックス ・・・列単位でページに格納 クラスター化列ストア インデックス ・・・列単位でページに格納 SalesOrder SalesOrder OrderDate Product Qty Price Amount Region Shop AR-5381 2 \1024 \2048 Tokyo A-Store FR-R92R-48 1 \4069 Osaka B-Store BK-M68B-46 \256 \512 OrderDate Product Qty Price Amount Region Shop AR-5381 2 \1024 \2048 Tokyo A-Store FR-R92R-48 1 \4069 Osaka B-Store BK-M68B-46 \256 \512 ALTER TABLE SalesOrder INMEMORY CREATE [NONCLUSTERED] CULUMNSTORE INDEX nccix_SalesOrder ON SalesOrder (OrderDate,Amount) 追加される 圧縮されてディスクに永続化される OrderDate Amount \2048 \4069 \512 OrderDate Product Qty Price Amount Region Shop AR-5381 2 \1024 \2048 Tokyo A-Store FR-R92R-48 1 \4069 Osaka B-Store BK-M68B-46 \256 \512 CREATE CLUSTERED CULUMNSTORE INDEX ccix_SalesOrder ON SalesOrder OrderDate Product AR-5381 FR-R92R-48 BK-M68B-46 Qty 2 1 Price \1024 \4069 \256 Amount \2048 \4069 \512 Region Tokyo Osaka Shop A-Store B-Store 変換される 圧縮されてディスクに永続化される OrderDate Product AR-5381 FR-R92R-48 BK-M68B-46 Qty 2 1 Price \1024 \4069 \256 Amount \2048 \4069 \512 Region Tokyo Osaka Shop A-Store B-Store メモリに展開される

16 ⑦ 認証 Oracle SQL Server OS 認証 データベース認証 特権ユーザー Windows 認証 SQL Server 認証
パスワード ファイル認証 (SYSDBA、SYSOPER、SYSTEM) Windows グループ (ORA_DBA、ORA_OPER) SQL Server Windows 認証 SQL Server 認証 特権ユーザー sa (SQL Server 認証) sysadmin サーバーロール Windows インスタンス Windows インスタンス レベル データベース レベル SID SID SID ユーザー SID グループ SID パスワード付きユーザー SID ログイン データベース ユーザー master ユーザー データベース ユーザー インスタンス インスタンス レベル データベース レベル SID SID パスワード付きログイン データベース ユーザー パスワード付きユーザー master ユーザー データベース

17 SQL Server の包含 (コンテインメント) データベースにおける認証
包含データベース master データベースやほかのユーザー データベースから分離されたユーザー データベース ログイン、サーバー照合順序を使用しないため、可搬性が高い Windows 認証 SQL Server 認証 アプリからの接続する場合、接続文字列に 接続先のユーザー データベース名を指定する Windows インスタンス レベル データベース レベル SID ADO.NET SQL Server 認証 SID SID グループ Server=<サーバー名>;Initial Catalog=<データベース名>; Integrated Security=true; SID データベース ユーザー master ユーザー データベース ユーザー ADO.NET Windows 認証 Server=<サーバー名>; Initial Catalog=<データベース名>; User ID=<ログイン>;Password=<パスワード>; インスタンス レベル データベース レベル SID パスワード付き データベース ユーザー パスワード付きログイン master ユーザー データベース

18 SQL Server に対する多層防御 SQL Server 各層でアクセス制御、暗号化キー、および監査の構成が可能 ネットワーク OS
Windows ファイアウォール Windows アカウント Windows 認証、GPO DPAPI 監査 SQL Server SQL Server 認証、GPO 証明書 ログイン サーバーロール サービス マスター キー ポリシーベース管理 監査 master データベース データベース ユーザー データベース ロール 監査 ポリシーベース管理 ADO.NET Library データベース 暗号化キー Always Encrypted 列暗号化キー 透過的なデータ暗号化 (Enterprise) アプリケーション 動的データ マスキング バックアップの暗号化 列マスター キー 行レベル セキュリティ ユーザー データベース バックアップ

19 2. SQL Server の構成と稼働状況の確認
インストール時の構成 照合順序の設定 データ ディレクトリと TempDB の設定 インスタンス レベルの構成 データベース レベルの構成 SQL Server の稼働状況の確認

20 クロスプラットフォームに対応した Microsoft SQL Operations Studio (Preview) も利用可能に
① インストール時の構成 ネットワーク環境の確認とサービスアカウントの作成 前提となるハードウェアとソフトウェアの準備 インストール ウィザードによる構成 セットアップ サポート ルール 機能の選択 インスタンスの構成 既定のインスタンス / 名前付きインスタンス サーバーの構成 サービス アカウント 照合順序 データベース エンジンの構成 認証モード / サーバー管理者の指定 データ ディレクトリ TempDB FILESTREAM エラーと使用状況レポート Windows ファイア ウォールの構成 SQL Server Management Studio のインストール クロスプラットフォームに対応した Microsoft SQL Operations Studio (Preview) も利用可能に

21 機能の選択 使用するサービスのみを選択する インスタンス機能は、サーバーに 50 までインストール可能
Reporting Services は、Web からダウンロードしてインストール インスタンス機能 説明 データベース エンジン サービス データベース エンジンのコア機能を提供するサービス エンジン内に、レプリケーション、フルテキスト検索、ML サービス、Data Quality Services (DQS) サービス、PolyBase クエリ サービスを含む Analysis Services オンライン分析処理 (OLAP) および、データ マイニング アプリを作成および管理するツール Reporting Services 表形式、マトリックス形式、グラフィカル形式、および自由形式のレポートを作成、管理、配置するためのサーバー コンポーネントとクライアント コンポーネント 共有機能 説明 Data Quality Client DQS サーバーに接続するスタンドアロン アプリケーション クライアント ツール接続 DB-Library、OLEDB for OLAP、ODBC、ADODB、ADOMD+ 用のネットワーク ライブラリなど、クライアントとサーバー間の通信を行うためのコンポーネント Integration Services データの移動、コピー、変換するためのGUI ツールとプログラミング可能なオブジェクトのセット クライアント ツールの旧バージョンとの互換性 SQL 分散管理オブジェクト (SQL-DMO)、Decision Support オブジェクト (DSO) クライアント ツール SDK プログラマのためのリソースを含むソフトウェア開発キット 分散再生コントローラー 分散再生クライアントのアクションを統制する 分散再生クライアント SQL Server インスタンスに対するワークロードをシミュレートする SQL クライアント SDK データベース アプリケーション開発用の SQL Server Native Client (ODBC/OLE DB) SDK

22 インスタンスの構成 既定のインスタンスで構成するのか、名前付きインスタンスで構成するのかの決定
既定のインスタンスの場合、インスタンス ID は、MSSQLSERVER になる インスタンスごとに、ディレクトリ構造、レジストリ構造が分離して作成される サービス サービス 既定のインスタンス 名前付きインスタンス SQL1 SQL1\Staging Microsoft SQL Server \MSSQL14.MSSQLSERVER Microsoft SQL Server \MSSQL14.STAGING システム データベース ユーザー データベース システム データベース ユーザー データベース ユーザー データベース

23 サーバーの構成 <サービス アカウント>
既定のサービス アカウント 既定では、管理された仮想ローカル アカウントが設定される パスワード管理しなくてよい <ドメイン名>\<コンピューター名>$ の形式で、コンピューター アカウントの資格情報を使用し、ネットワーク リソースにアクセス可能 最小のユーザー権限割り当てで動作させる クラスターの各ノードで同じ SID を使用することができないため、 SQL Server フェールオーバー クラスター インスタンスでは使用できない 管理された仮想アカウント

24 サーバーの構成 <照合順序の設定>
照合順序とは データ セット内の各文字を表すビット パターンの指定 データの並べ替えおよび比較を行うための規則が決定される 照合順序指定子で 「Japanese」 を選択した場合、日本語辞書順で格納される 指定の単位 サーバ レベル（インスタンス レベル） セットアップ時に照合順序を設定 日本語版は Japanese_CI_AS が既定値 照合順序の変更はインスタンスの再構築が必要 データベース レベル、列レベル データベース、および、テーブル作成時に設定可能 式レベル COLLATE 句を用いることで指定可能 SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI 単一の照合順序のみを使用する 複数の照合順序を使用する場合でも、使用頻度の高い照合順序をサーバ レベルに設定する 照合順序の指定により、クエリの実行結果が異なる可能性があるため、アプリケーション要件を分析し、適切な照合順序を選択する

25 [参考] 照合順序の構成内容 選択項目と設定内容 照合順序の構成例 Japanese_CI_AS
CI (Case Insensitive)：大文字、小文字を区別しない AS (Accent Sensitive)：アクセント、濁音、半濁音を区別する 選択項目 設定される値 説明 バイナリ _BIN 各文字に定義されているビット パターンに基づいて、SQL Server テーブルのデータの並べ替えおよび比較が行われる Unicode データは最初の文字が WCHAR として比較された後、続いてバイト単位の比較が行われる バイナリ並べ替え順では、大文字小文字とアクセントが区別される バイナリは最速の並べ替え順 バイナリ コード ポイント _BIN2 Unicode データの Unicode コード ポイントに基づいて、SQL Server テーブル内のデータが並べ替えられ、比較される 非 Unicode データの場合、バイナリ並べ替えと同じ比較が行われる 大文字小文字を区別する _CS 大文字と小文字が区別される 最初に小文字、その後で大文字が並べ替えられる このオプションを選択しない場合 (_CI)、大文字と小文字を同じものとして並べ替えが行われる アクセントを区別する _AS アクセントのある文字とアクセントのない文字が区別される (たとえば、“a” と “ấ” は等しくない) このオプションを選択しない場合 (_AI)、アクセントのある文字とアクセントのない文字が同じものと見なされ、並べ替えが行われる かなを区別する _KS 日本語のひらがな文字とカタカナ文字が区別される このオプションを選択しない場合 (_KI)、ひらがなとカタカナを同じものと見なして並べ替えが行われる 文字幅を区別する _WS 同一文字の 1 バイト表現と 2 バイト表現が区別される このオプションを選択しない場合 (_WI)、同一文字の 1 バイト表現と 2 バイト表現を同じものと見なして、並べ替えられる

26 データベース エンジンの構成 <認証モード / サーバー管理者の指定>
サーバー認証モードの選択 Windows 認証モード 混合モード (SQL Server 認証モードと Windows 認証モード) sa は、無効化しておくことを推奨 sa は SQL Server 認証の管理者アカウント ここで指定したWindows アカウントは、sysadmin サーバーロールに登録される 管理者アカウントは、監査可能な Windows 認証で接続する

27 データベース エンジンの構成 <データ ディレクトリと TempDB の設定>
データベース ファイル、ログ ファイル、バック アップ ファイルを分離する TempDB のデータ ファイル数とサイズの設定 TempDB の使用状況に応じて、初期サイズ、自動拡張サイズを調整する 論理コア数もしくは 8 以上の場合 8 に設定 (パフォーマンに問題があるようあれば調整) データ、ログ、および、バックアップ ファイルを分散配置する 行バージョン管理に必要なサイズを見積り 初期サイズを増やしておく

28 Windows ファイア ウォールの構成 既定のインスタンス 名前付きインスタンス SQL Browser サービスの動作 TCP 1433
UDP 1434 (SQL Browser サービス) 受信規則でプログラム (MSSQL\Binn\sqlservr.exe) に対して接続を許可 SQL Browser サービスの動作 以下の構成では SQL Browser サービスは停止できる TCP1433 でリッスンする既定のインスタンスのみが稼働している場合 固定ポートを使用するインスタンスのみの構成で、アプリ側でポート指定している場合 名前付きインスタンス B への接続を要求 UDP:1434 SQL Browser サービス アプリケーション TCP:50172 を返す SQL Server 既定のインスタンス 名前付きインスタンスのポート番号を取得 TCP:1433 (既定値) TCP:50172 を使用して接続 SQL Server 名前付きインスタンス A TCP:50171 (起動時に決定) SQL Server 名前付きインスタンス B TCP:50172 (起動時に決定)

29 [参考] インスタンスが使用する TCP ポートの構成
SQL Server 構成マネージャーにより、インスタンスに対し、動的ポート、および、静的ポートの割り 当てが可能 動的ポート使用時は [IPAll] の [TCP 動的ポート] に 「0」 を設定 静的ポート使用時は [IPAll] の [TCP ポート] にポート番号を記入する

30 ② インスタンス レベルの構成 インスタンス レベルの主要な構成項目 メモリ サイズの構成 プロセッサの構成
データベースに対する既定値の設定 セキュリティ 可用性構成 (Always On フェールオーバー クラスター インスタンス) Enterprise：64 ノード Standard：2 ノード Always On フェールオーバー クラスター インスタンス SQL Server 運用ノード 待機ノード 共有ディスク

31 複数インスタンス構成では、各インスタンスの 最大サーバーメモリを調整する
メモリ サイズの構成 バッファ プール (8 KB 単位で動的チューニングされる領域) バッファ キャッシュ プロシージャ キャッシュ クエリ ワークスペース ロック その他 バッファ プールの最大値は起動時、計算され確保 物理メモリ サイズに応じて決定 最大サーバー メモリのサイズまで 自動拡張可能 動的チューニング対象範囲外の領域 スレッド スタック MemToLeave 領域 オペレーティング システム バッファ プール クエリ ワークスペース プロシージャ キャッシュ バッファ キャッシュ SQL Server その他 ロック 複数インスタンス構成では、各インスタンスの 最大サーバーメモリを調整する スレッド スタック MemToLeave 領域

32 複数インスタンス構成では、各インスタンスが使用するプロセッサを調整する
プロセッサの構成 プロセッサ関係マスク 特定のワーカー スレッドを特定の CPU に割り当てることで、 コンテキスト スイッチを抑える I/O関係 既定値 (0) の場合、ReadFile/WriteFile API 呼び出しによる I/O の ポストやチェックサム計算などの I/O 完了処理を含む I/O 処理は、 クエリを実行しているワーカースレッドによって実行される I/O 専用ワーカースレッドを作成したい場合、I/O 関係マスクを構成する 複数インスタンス構成では、各インスタンスが使用するプロセッサを調整する

33 ③ データベース レベルの構成 データベース レベルの主要な構成項目 ファイルとファイルグループの構成 データベース オプションの調整
トランザクション分離レベル セキュリティ 可用性構成 (Always On 可用性グループ) Enterprise 1~8 までのセカンダリ レプリカ Standard では 1 つのセカンダリ レプリカ Always On 可用性グループ SQL Server SQL Server プライマリ セカンダリ

34 トランザクション分離レベル Oracle SQL Server
ダーティ リード 反復不能読み取り ファントム ロック方式 READ UNCOMMITED 可 READ COMMITED (既定) 不可 REPEATABLE READ SERIALIZABLE 行バージョン管理方式 READ COMMITED SNAPSHOT READ ONLY SNAPSHOT SQL Server の READ COMMITED (ロック方式) での動作 ロックの互換性 トランザクション 1 トランザクション 2 BEGIN TRANSACTION UPDATE T1 SET VER = '2017' WHERE ID = 1 COMMIT TRANSACTION SELECT * FROM T1 WHERE ID = 1 要求されたロック0 既に取得しているロック IS S U IX X インテント共有 (IS) ○ × 共有 (S) 更新 (U) インテント排他 (IX) 排他 (X) 排他ロック(X) ID PROD VER 1 SQL 2017 2 ORA 12c 待機状態になる ID PROD VER 1 SQL 2017 2 ORA 12c ID PROD VER 1 SQL 2017

35 SQL Server での行バージョン管理方式
READ COMMITED SNAPSHOT ステートメント レベルの読み取り一貫性 SNAPSHOT トランザクション レベルの読み取り一貫性 トランザクション 1 トランザクション 2 BEGIN TRANSACTION UPDATE T1 SET VER = '2017' WHERE ID = 1 COMMIT TRANSACTION SELECT * FROM T1 WHERE ID = 1 トランザクション 1 トランザクション 2 BEGIN TRANSACTION UPDATE T1 SET VER = '2017' WHERE ID = 1 COMMIT TRANSACTION SELECT * FROM T1 WHERE ID = 1 更新前の 行バージョン 更新前の 行バージョン ID PROD VER 1 SQL 2016 2 ORA 12c ID PROD VER 1 SQL 2016 2 ORA 12c ID PROD VER 1 SQL 2016 ID PROD VER 1 SQL 2016 ID PROD VER 1 SQL 2017 2 ORA 12c ID PROD VER 1 SQL 2017 2 ORA 12c tempdb tempdb 確定したデータ 確定したデータ ID PROD VER 1 SQL 2017 2 ORA 12c ID PROD VER 1 SQL 2017 2 ORA 12c ID PROD VER 1 SQL 2017 ID PROD VER 1 SQL 2016 ID PROD VER 1 SQL 2017

36 SQL Server で暗黙的なトランザクションを実行するには SET IMPLICIT_TRANSACTIONS ON を記述する
[参考] トランザクション処理 Oracle 暗黙的なトランザクション トランザクションは自動的に開始され、終了または取り消しを記述 ANSI-92 標準 SQL Server 明示的なトランザクション 明示的にトランザクションの開始、終了、取り消しを記述 明示的に開始しない場合、ステートメント実行時一文ずつ確定 する自動コミット モード SET IMPLICIT_TRANSACTIONS ON BEGIN TRAN TRN1 処理 1 処理 1 トランザクション トランザクション 処理 2 処理 2 COMMIT TRAN TR1 COMMIT BEGIN TRAN TRN2 処理 3 トランザクション 処理 3 トランザクション ROLLBACK ROLLBACK TRAN TR2 SQL Server で暗黙的なトランザクションを実行するには SET IMPLICIT_TRANSACTIONS ON を記述する

37 行バージョン管理方式の設定手順 トランザクション分離レベルの確認 READ COMMITED SNAPSHOT SNAPSHOT
Oracle の既定の分離レベルと同等の動作にするには、 行バージョン管理方式を使用する トランザクション分離レベルの確認 DBCC USEROPTIONS READ COMMITED SNAPSHOT SNAPSHOT --データベース レベルの設定 ALTER DATABASE <データベース名> SET READ_COMMITTED_SNAPSHOT ON --データベース レベルの設定 ALTER DATABASE <データベース名> SET ALLOW_SNAPSHOT_ISOLATION ON --セッション レベルの設定 SET TRANSACTION ISOLATION LEVEL SNAPSHOT すべてのセッションが READ COMMITED SNAPSHOT で動作する セッション レベルの設定をしないセッションでは、 READ COMMITED または READ COMMITED SNAPSHOT で動作する

38 ④ 稼働状況の確認 SQL Server Management Studio SQL Server ログ フォルダーから確認
インスタンスが起動しない場合、メモ帳で直接開くことが可能 7 世代分の SQL Server ログと対応するイベント ログの参照が可能 現在

39 SQL Server ログの重大度 (Severity) レベル
重大度レベルを参照し、発生したエラーが情報レベルか、より重大で対処を要する深刻なものなの かを判断する 0 ～ 10：エラーは情報レベルのメッセージ 11～16：ユーザーが訂正できるエラー 17～19：ユーザーが訂正できないエラー 19 以上：致命的なエラーで、実行中のバッチは停止する トレース フラグの設定により、エラー ログ出力を拡張できる デッドロック情報：-T1204,1222 重大度 内容 19 データベース エンジンの制限を超えたため、現在のバッチ プロセスが終了させられたことを示す 20 現在のプロセスのステートメントに問題が発生したことを示すが、データベースは破損していない 21 現在のデータベース プロセスのすべてのタスクに影響する問題が発生したことを示す。データベースは破損していない 22 メッセージに示されたテーブルまたはインデックスが、ソフトウェアまたはハードウェアの問題によって損傷していることを示す 23 ハードウェア、またはソフトウェアの問題によって、データベース全体の整合性に疑いがあることを示す。場合によっては、データベースの復元が必要となる 24 メディア障害を示す。場合によっては、データベースの復元が必要となる。または、ハードウェアの修復が必要な場合もある

40 3. 運用管理タスクと自動化 データベースの整合性チェック データベースのバックアップ データベースの復元 インデックス操作
データベース メンテナンスの自動化

41 ① データベースの整合性チェック DBCC (データベース コンソール コマンド) を使用した整合性チェック
sys.dm_exec_requests カタログ ビューを使用した進捗確認 percent_complete 列：コマンドの完了率 command 列：コマンド実行の現在のフェーズ DBCC コマンド 内容 内部データベース スナップショットの使用 DBCC CHECKALLOC 指定されたデータベースのディスク領域の割り当てとページ使用の一貫性をチェック 〇 DBCC CHECKTABLE 指定されたテーブル、およびインデックス付きビューを構成するすべてのページ割り当てと構造的整合性をチェック DBCC CHECKFILEGROUP 指定されたファイル グループのすべてのテーブル、およびインデックス付きビューのページ割り当てと構造的整合性をチェック DBCC CHECKDB 指定されたデータベースのすべてのテーブル、およびインデックス付きビューのページ割り当てと構造的整合性をチェック (DBCC CHECKALLOC、DBCC CHECKTABLE、DBCC CHECKCATALOG の処理を含む) DBCC CHECKDBは、検出されたエラーの修復も指定できる 但し、修復オプションを使用する場合、対象のデータベースはシングル ユーザー モードになっている必要がある DBCC CHECKCATALOG システム カタログに格納されているメタデータの一貫性をチェック DBCC UPDATEUSAGE テーブルまたはインデックスのパーティションごとに、行、使用済みページ、予約済みページ、リーフ ページ、およびデータ ページのカウントの情報に不一致がある場合、それらのレポートと修正を行う

42 ② データベースのバックアップ バックアップ計画と準備作業 バックアップ計画策定 論理バックアップデバイスの作成
完全、および差分バックアップ スクリプトの 作成 トランザクション ログ バックアップ スクリ プトの作成

43 データベースのバックアップ計画の策定 バックアップ計画の考慮事項 格納するデータの特性に応じた復旧モデルを選択 データの特性 復旧モデル
データ損失が許容される 単純 データ損失の許容される時間間隔で 「完全」 および 「差分」 バックアップをスケ ジュールする データ損失が許容されな い 完全 「完全」、「差分」 バックアップをスケジュールする 一括操作時に「一括ログ」復旧モデルを併用する (但し、「一括ログ」 復旧モデル時の操作中の時間帯への復旧はできない) データ損失が許容されず、 頻繁に更新されるデータ が含まれる (更新系) 「完全」、「差分」 、 「トランザクション ログ」 バックアップをスケジュールす る 読み取り専用 (参照系) 「完全」 バックアップを 1 回実施する

44 論理バックアップ デバイスの定義 論理デバイス名にバックアップを作成することで物理的な変更に対応 物理バックアップ デバイスの例
物理デバイスは、データ ディスク、ログ ディスクとは別のディスクを使用する バックアップ デバイスにバックアップが書き込まれたときに初期化される 物理バックアップ デバイスの例 EXEC sp_addumpdevice 'disk', 'AdventureWorks_full' , 'H:\Backup\AdventureWorks_full.bak' 論理バックアップ デバイス 物理バックアップ デバイス デバイスの種類 例 ローカル ディスク (disk) <ドライブ文字>:\<ローカル フォルダー名>\MyDB.bak 共有フォルダー (disk) \\<サーバー名>\<共有名>\MyDB.bak Azure Blob ストレージ (URL) アカウント名>.blob.core.windows.net/<コンテナー名>/MyDB.bak

45 完全、および差分バックアップ スクリプトの作成
構文 主要な WITH オプション DIFFERENTIAL 差分バックアップ INIT 既存のデータを上書き NOINIT (既定値) 同じファイルに追加 COMPRESSION 圧縮 COPY_ONLY 履歴を残さない 例文 BACKUP DATABASE <データベース名> TO <バックアップ デバイス名> [WITH オプション] BACKUP DATABASE Sales TO Sales_full WITH INIT ,COMPRESSION GO

46 トランザクション ログ バックアップ スクリプトの作成
構文 主要な WITH オプション INIT　　　 既存のデータを上書き NOINIT　 同じファイルに追加 (既定値) NO_TRUNCATE バックアップしてもトランザクションログファイルを切り捨てない (ログ末尾のバックアップ時) COPY_ONLY トランザクションログファイルを切り捨てず、履歴も残さない 例文 BACKUP LOG <データベース名> TO <バックアップ デバイス名> [WITH オプション] BACKUP LOG Sales TO Sales_log GO

47 ③ データベースの復元 データベース復元の手順 バックアップ デバイスの確認 データベース復元シーケンスの確定 復元スクリプトの作成
復元スクリプトの実行

48 バックアップ デバイスの確認 バックアップ デバイスからバックアップ セットの情報を取得する
バックアップの種類、作成日、バックアップ セットの位置 (FILE オプションで使用)、含まれる LSN、圧縮の状態など バックアップ デバイス情報を取得する主要なステートメント RESTORE HEADERONLY FROM <バックアップ デバイス名> ステートメント 内容 RESTORE VERIFYONLY バックアップ セットが完全で、ボリュームが読み取り可能であることを確認 RESTORE LABELONLY メディア ヘッダー情報を含む結果セットを返す RESTORE HEADERONLY バックアップ デバイス上にあるすべてのバックアップ セットについて、すべてのバックアップ ヘッダー情報を含む結果セットを返す RESTORE FILELISTONLY バックアップ セットに保存されているデータ ファイルとログ ファイルの一覧を含んだ結果セットを返す

49 データベース復元シーケンスの確定 データベース ファイルの確認 データベース復元時の判断 YES NO ログ ファイルの保護は 重要！
データベース ファイルの確認 データベース復元時の判断 PRIMARY ファイルグループ (必須) ユーザー データベース プライマリ データファイル .mdf ユーザー定義のファイル グループ (任意) ログ ファイルも 損傷？ NO_TRUNCATE オプションを使用して 末尾のログをバックアップ データ ファイルが 損傷 セカンダリ データファイル .ndf .ndf .ndf NO トランザクション ログ ファイル ログ ファイル YES .ldf すべてのデータ ファイルを復元し、 前回のログ バックアップ時点まで復旧可能 壊れたファイルのみ 復元し、最新の時点 まで復旧可能 ログ ファイルの保護は 重要！

50 復元スクリプトの作成 完全、差分バックアップからの復元 トランザクション ログ バックアップからの復元 主要な WITH オプション
FILE バックアップ セットの位置 NORECOVERY　　　 復旧操作(未コミット トランザクションのロールバック) をしない RECOVERY　　　 復旧操作をする STANDBY　　　 復旧操作しないで、データベースを読み取りを可能にする STOPAT 時刻指定の復元 MOVE データベース ファイルを新しい場所に配置する REPLACE データベース ファイルを上書きする RESTORE DATABASE <データベース名> FROM <バックアップ デバイス名> [WITH オプション] RESTORE LOG <データベース名> FROM <バックアップ デバイス名> [WITH オプション]

51 復元スクリプトの実行 末尾のトランザクション ログのバックアップを取得 (NORECOVERY オプション)
BACKUP LOG Sales TO Sales_Backup WITH NORECOVERY RESTORE DATABASE Sales FROM Sales_Backup WITH FILE = 1, NORECOVERY RESTORE DATABASE Sales FROM Sales_Backup WITH FILE = 4, NORECOVERY RESTORE LOG Sales FROM Sales_Backup WITH FILE = 5, NORECOVERY RESTORE LOG Sales FROM Sales_Backup WITH FILE = 6, NORECOVERY RESTORE LOG Sales FROM Sales_Backup WITH FILE = 7, RECOVERY

52 ④ インデックス操作 インデックス操作のフロー 許容範囲 再編成 再構築 動的管理ビューによる、インデックス断片化の判定
SELECT * FROM sys.dm_db_index_physical_stats ( <データベース id>, <オブジェクト id>, <インデックス id>, <パーティション番号>, <モード>) avg_fragmentation_in_percent 列 0～10 % 30 % 以下 30 % より大きな値 再編成 再構築 参考：

53 インデックスの再編成と再構築 インデックスの再編成 インデックスの再構築 オンライン デフラグ操作 アトミック操作
MAXDOP ヒント、ONLINE オプションを指定できる ALRER INDEX <インデックス名> ON <テーブル名> REORGANIZE ALRER INDEX <インデックス名> ON <テーブル名> REBUILD WITH (FILLFACTOR = nn, ONLINE = ON, SORT_IN_TEMPDB = ON)

54 ⑤ データベース メンテナンスの自動化 SQL Server エージェント 成功、失敗の状態によりステップの実行順序を指定
「ジョブ」 と呼ばれる一連の管理タスクをスケジュール、または 「警告」 に関連付けて実行する Windows サービス ジョブは複数の 「ステップ」 で構成される ジョブ完了時に 「オペレーター」 に状態を通知できる 構成情報や履歴の保存に msdb システム データベースを使用 成功、失敗の状態によりステップの実行順序を指定 ジョブ完了時に選択できるアクション 成功、失敗、完了の状態により通知や事後処理が行える 電子メール (データベース メールを構成する) Windows アプリケーション イベント ログへの書き込み ジョブの削除 SQL Server エージェント ジョブ ジョブ スケジュール sp_start_job 失敗 ステップ 1: データベースの整合性チェック 成功 通知: エラーを報告 応答 通知 ステップ 2: データベースの完全バックアップ 失敗 警告 オペレーター 通知

55 データベース メールによる電子メール通知の構成
データベース エンジンから SMTP ベースのメール送信を可能にする sp_send_dbmail ストアドプロシージャでメール送信 msdb システム データベース Databas UserRole データベース ロールのメンバ登録が必要 データベース メール構成ウィザードで構成 データベース メール アカウント sp_send_dbmail Databas 90.exe SQL Server SMTP サーバー

56 メンテナンス プランによる自動化 ウィザードによりメンテナンス タスク (SSIS パッケージ実行ジョブ) を構成し、SQL Server エージェントを使用してスケ ジュール実行する機能 設定できるメンテナンス タスク データベース整合性の確認 データベースの圧縮 インデックスの再構成 インデックスの再構築 統計の更新 履歴のクリーンアップ SQL Server エージェントジョブの 実行 データベースのバックアップ 完全 差分 トランザクション ログ メンテナンス クリーンアップ タスク

57 [参考] メンテナンス プランの構成例 データベースのバックアップ計画の例 バックアップ計画を実行するメンテナス プランの例 ・・・・ 処理
完全 バックアップ① トランザクション ログ バックアップ① トランザクション ログ バックアップ② 差分 バックアップ① トランザクション ログ バックアップ③ トランザクション ログ バックアップ④ ・・・・ 処理 スケジュール メンテナンス タスク 説明 完全バックアップ 毎週日曜日 0:00 メンテナンス クリーンアップ タスク 不要な世代のバックアップとテキスト ログ ファイルを削除 履歴のクリーンアップ 不要な世代の履歴を削除 データベースの整合性確認 対象データベースへの DBCC CHECKDB 実行 データベースのバックアップ (完全) 対象データベースの完全バックアップ 差分バックアップ 毎日 3:00 データベースのバックアップ (差分) 対象データベースの差分バックアップ トランザクション ログ バックアップ 毎日 60 分間隔 データベースのバックアップ (トランザクション ログ) 対象データベースのトランザクション ログ バックアップ

58 4. パフォーマンスの監視 パフォーマンス監視と最適化 パフォーマンス監視のための主要なツール 動的管理オブジェクト 利用状況モニター
パフォーマンス モニター SQL Server Management Studio 標準レポート パフォーマンス データ コレクション SQL Server Profiler クエリ ストア 動的管理オブジェクト

59 ① パフォーマンス監視と最適化 パフォーマンス監視と最適化のサイクル 定常的な監視・ベースラインの設定 問題の発見 分析・最適化 テスト
展開 利用状況モニター SSMS 標準レポート サーバー データベース SQL Server Profiler クエリ クエリ ストア パフォーマンス データ コレクション SQL Server インスタンス パフォーマンス モニター SQL Server カウンター システム カウンター Windows OS ハードウェア・ネットワーク

60 ② パフォーマンス監視のための主要なツール
現在の利用状況 利用状況モニター アクティブなユーザー タスク、リソースの待機、データ ファイルの I/O の表示 現在、ブロックされているトランザクションと、ブロックしているトランザクションを確認し、強制終了も可能 定常的な監視 パフォーマンス モニター OS レベルで各サブシステム (CPU、メモリ、ディスクなど) の利用状況収集 パフォーマンス ログと警告 SQL Server Management Studio 標準レポート パフォーマンス ダッシュボード、サーバー ダッシュボード 現在の利用状況、パフォーマンス統計、構成履歴の表示 パフォーマンス データ コレクション 中長期にわたるパフォーマンス データ収集のためのコンポーネント ディスク使用量、クエリ統計、サーバーの利用状況 クエリと実行プランの分析 SQL Server Profiler クエリのトレース、再生、分析 クエリ ストア 1つのクエリに複数の実行プランを保持 実行プランをグラフィカルに確認 クエリに特定の実行プランを強制 利用状況モニター SSMS 標準レポート サーバー データベース SQL Server Profiler クエリ クエリ ストア パフォーマンス データ コレクション SQL Server インスタンス パフォーマンス モニター SQL Server カウンター システム カウンター Windows OS ハードウェア・ネットワーク

61 利用状況モニター アクティブなユーザー タスク、リソースの待機、データ ファイルの I/O の表示
現在、ブロックされているトランザクションと、ブロックしているトランザクションを確認し、強制終了も可能

62 パフォーマンス モニター © 2011 Microsoft Corporation Microsoft Confidential
Windows 標準ツール グラフ、ログ、警告ログ、およびレポート表示 パフォーマンス データの抽出し Excel にインポート可能 複数のサーバーを対象にしたリモート監視が可能 SQL Server をインストールすると SQL Server 用のオブジェクト カウンターが追加される システム カウンターと SQL Server カウンターを比較し、原因の絞り込みに役立てる 主要な監視項目 ディスク利用状況 プロセッサ使用率 メモリ使用量 ネットワーク

63 [参考] OS リソースの監視で使用するシステム カウンターの例
© 2011 Microsoft Corporation Microsoft Confidential [参考] OS リソースの監視で使用するシステム カウンターの例 カテゴリ カウンター 閾値 状況 調査項目 CPU Processor\%Processor Time(_Total) 80 % 以下 CPU ボトルネックの発生 CPU 時間が高いクエリ 要求数に応じて %Processor Time が上昇、下降しているのか判断できる 「SQL Server要求数」、「コンパイル」、「ハッシュ」の項目をチェック 平均 CPU 時間が高いクエリを特定 System\Context Switches/sec 1 論理 CPU あたり 20,000 以下が望ましい sqlservr プロセスの thread count をチェック System\Processor Queue Length 1 論理 CPU あたり 2 以下 メモリ Memory\Available Mbytes 100 MB 以上 メモリを使用しているプロセスの特定 max server memory の設定有無確認 以下のカウンターをチェック SQL Server:Memory Manager\Total Server Memory Processオブジェクト タスク マネージャでメモリが割り当てられているプロセスを特定 ディスク PhysicalDisk\Avg Disk Queue Length ディスク数の 1.5 ～ 2 倍以内 ディスク ボトルネックの発生 「SQL Server IO 操作」、「バッファプール」 の項目をチェック PhysicalDisk\Avg Disk sec/Read 20 ms 以下 PhysicalDisk\Avg Disk sec/Write PhysicalDIsk\%Idle Time 20 % 以下

64 [参考] SQL Server リソースの監視で使用する SQL Server カウンターの例
カテゴリ カウンター 閾値 状況、アクション 調査方法 バッファ プール SQL Server:Buffer Manager\Page life expectancy 300 秒以上 Lazy writes/sec, checkpoint pages/sec カウンター値が高く、Page life expectancy が低い場合、バッファプールが枯渇している 平均読み取りページ数が多いクエリを特定 SQL トレースでの情報採取 SQL Server:Buffer Manager\Checkpoint pages/sec なし SQL Server:Buffer Manager\Lazy writes/sec SQL Server:Buffer Manager\Buffer cache hit ratio 97%以上 SQL Server の IO 操作 SQL Server:Buffer Manager\Page Reads/sec 読み取りページ数の高いクエリを特定する データコレクタで平均読み取りページ数が多いクエリを特定 SQL Server:Buffer Manager\Page Writes/sec SQL Server:Buffer Manager\Page Lookups/sec 待機状態 SQL Server:Wait Statistics\Average Wait Time (ms) 待機時間の長い Wait Resource を特定する 以下のカウンターから、待機時間の長いリソースを特定する Lock waits カウンターが上昇 ⇒ 「ブロッキング」 Memory grant queue waits カウンターが上昇 ⇒「ワークスペース」 Page IO latch waits カウンターが上昇 ⇒「ディスク」、「バッファプール」、「SQL Server IO 操作」 Wait for workerカウンターが上昇 ⇒ 「CPU」 Log write waitsカウンターが上昇 ⇒ 「ディスク」 (ログ ファイルのドライブ) Non-Page latch waits カウンターが上昇 ⇒ 「ラッチ」 参考：

65 [参考] SQL Server のメモリ使用状況
2/21/2019 © 2013 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION. [参考] SQL Server のメモリ使用状況 バッファープール内の各領域を確認するための SQL Server パフォーマンス オブジェクト カウンター Memory Manager データ ページ Memory Manager \Database Cache Memory (KB) Memory Manager \Total Server Memory (KB) Stolen ページ データページ以外 (プロシージャ キャッシュなど) で使用している領域 Memory Manager \Stolen Server Memory (KB) フリー ページ Memory Manager \Free Memory (KB) スレッド スタック 参考：

66 SQL Server Management Studio 標準レポート
パフォーマンス ダッシュボード、サーバー ダッシュボード 現在の利用状況、パフォーマンス統計、構成履歴の表示

67 複数の SQL Server からパフォーマンス データを収集
パフォーマンス データ コレクション 統合化されたパフォーマンス データ収集プラットフォーム 収集したパフォーマンス データをリレーショナル データ ウェアハウスへの格納 リモートからデータ収集するには、オペレーティング システム (CmdExec) サブシステムに対してアクティブな プロキシ アカウントを使用する 複数インスタンスからの収集と収集期間のスケジュールが可能 SQL Server エージェントで実行される SSIS パッケージ実行ジョブを使用 保存されている過去の履歴データからグラフィカルなレポート作成が可能 あらかじめ組み込まれているシステム データ コレクションを使って、主要な情報をすぐに収集することが可能 • ディスクの使用量 • サーバーの利用状況 • クエリ統計 環境に合わせてシステム データ コレクションのカスタマイズや独自のデータコレクションの作成が可能 複数の SQL Server からパフォーマンス データを収集 パフォーマンス データ コレクション チューニングや リソース配分などに 役立つ情報を収集可能 監視対象 監視対象 管理データウェアハウス 監視サーバー SQL Server 監視対象

68 パフォーマンス データ コレクションのレポート
© 2011 Microsoft Corporation Microsoft Confidential パフォーマンス データ コレクションのレポート グラフィカルなレポートで状況を確認 SQL Server Management Studio からサーバーの使用状況を視覚的に確認できるレポートを生成し、表示できる ディスク使用量の概要レポート サーバーの利用状況の履歴レポート クエリ統計の履歴レポート

69 高負荷な環境では、代替として 拡張イベントによるトレースを使用する
© 2011 Microsoft Corporation Microsoft Confidential SQL Server Profiler SQL Server のイベントをトレースする GUI ツール サーバーに負荷を与えるクエリの検出に利用 問題となる処理の一連のクエリの実行情報を収集 データベース エンジン チューニング アドバイザーのワークロードとして使用 テンプレートの提供 TSQL Replay は一連の実行したステートメントを再生するためのイベントを定義 Tuning はデータベース チューニング アドバイザーのワークロードとして使用可能 主要な監視項目 実行時間 読み取りページ数 CPU 使用数 実行プラン 高負荷な環境では、代替として 拡張イベントによるトレースを使用する

70 クエリ ストア コンパイル時と実行時の情報をクエリー ストアに格納
Plan Store : 実行プラン / Runtime Stats : 実行時間等の情報 プラン キャッシュには最新の実行プランが保持されるが、クエリ ストアには実行時のプランが保存されている 途中で使用している実行プランが変更された場合、実行プランごとにクエリの実行状態を確認することができる 特定の実行プランを強制することができる SQL ステートメント クエリ ストア コンパイル プラン ストア コンパイル メッセージ クエリ ストア スキーマ 非同期 ライト バック 実行 実行統計 実行メッセージ

71 クエリ ストアを使用したプランの分析 時間経過により変化するデータベースの使用状況とパフォーマンスの追跡が可能
実行プラン、使用した統計情報、およびリソース消費に関する情報を含むクエリ履歴の蓄積 データベース レベルで有効化 SQL Server Management Studio からの表示 ALTER DATABASE <データベース名> SET QUERY_STORE = ON 使用する実行プランの強制も可能

72 ③ 動的管理オブジェクト 動的管理ビュー (DMV) / 動的管理関数 (DMF)
SQL Server サービスのメモリー上に保持している情報を基に詳細な実行状態やパフォーマンスを把握することが可能 Oracle の動的パフォーマンス ビュー (V$) に相当 主要な動的管理オブジェクト 監視内容 動的管理オブジェクト メモリ使用量の内訳 sys.dm_os_memory_clerks sys.dm_os_buffer_descriptors sys.dm_exec_cached_plans sys.dm_exec_query_plan 関数 sys.dm_os_performance_counters クエリの統計情報 sys.dm_exec_query_stats 実行中の接続プロセス情報 sys.dm_exec_requests sys.dm_exec_sql_text 関数 リソース待機情報 sys.dm_os_wait_stats sys.dm_os_waiting_tasks CPU の利用状況 sys.dm_os_schedulers sys.dm_exec_query_optimizer_info I/O の統計情報 I/O 待ちをしている要求 sys.dm_io_virtual_file_stats 関数 sys.dm_io_pending_io_requests tempdb の使用状況 sys.dm_db_file_space_usage sys.dm_db_task_space_usage sys.dm_db_session_space_usage インデックスの使用状況 sys.dm_db_index_usage_stats sys.dm_db_index_operational_stats sys.dm_db_index_physical_stats 関数

73 [参考] 動的管理オブジェクトの使用例 クエリの平均 CPU 時間とクエリ プランを返すクエリ インデックスの使用状況を返すクエリ
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time] , Plan_handle , query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) ORDER BY total_worker_time / execution_count DESC SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName] , i.name AS [IndexName] , i.index_id , user_seeks + user_scans + user_lookups AS [Reads] , user_updates AS [Writes] , i.type_desc AS [IndexType] , i.fill_factor AS [FillFactor] FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1 AND i.index_id = s.index_id AND s.database_id = DB_ID() ORDER BY OBJECT_NAME(s.[object_id]) , writes DESC , reads DESC

74 SQL Server 2017 の主要機能 - データの蓄積、収集、加工、BI、統計解析、機械学習までをワン パッケージで提供 (オプション購入は不要) -
Express Standard Enterprise ミッションクリティカル パフォーマンス 最大コア数 4 コア 24 コア 無制限 インスタンスあたりの最大メモリ 1410 MB 128 GB OS のサポートする最大 リレーショナル データベースの最大サイズ 10 GB 524 PB 基本的な OLTP  基本的な可用性 (2 ノードの単一データベース フェールオーバー、読み取り不可のセカンダリ エンタープライズ データ管理 (マスター データ サービス、Data Quality Services) 高度な OLTP (インメモリ OLTP、インメモリ列ストア インデックス、データベース スナップショット)  ※注1  ※ 注1 高度な HA (AlwaysOn - マルチ ノード、複数データベースのフェールオーバー、読み取り可能なセカンダリ) セキュリティ ユーザー定義ロール、包含データベース、行レベル セキュリティ、動的データ マスク、Always Encrypted、監査 バックアップの暗号化 透過的なデータ暗号化、拡張キー管理 データウェアハウス データ ウェアハウジング (インメモリ列ストア、パーティション分割、PolyBase)  ※注2  ※ 注2 高度なデータ統合 (データ変更キャプチャ) Business intelligence プログラミング & 開発者ツール (T-SQL、CLR、データ型、 FileTable、JSON) 基本的なデータ統合 (SSIS、組み込みのコネクタ) 基本的なレポートおよび分析サービス 基本的なコーポレート ビジネス インテリジェンス (多次元モデル、基本的な表形式モデル) モバイル BI 高度なコーポレート ビジネス インテリジェンス (高度な表形式モデル、DirectQuery、高度なデータ マイニング) 高度な分析 基本的な R 統合・基本的な Python 統合 高度な R 統合・高度な Python 統合 ハイブリッド クラウド Stretch Database 注1: インメモリ列ストアの並列度の上限は Standard で 2、Express では 1 に制限されています。 注2: Standard と Express は、パーティション分割を行った場合でも、並列処理は実行できません。（シリアルでのみの実行になります） 参照:

75 本書に記載した情報は、本書各項目に関する発行日現在の Microsoft の見解を表明するものです。Microsoftは絶えず変化する市場に対応しなければならないため、 ここに記載した情報に対していかなる責務を負うものではなく、提示された情報の信憑性については保証できません。 本書は情報提供のみを目的としています。 Microsoft は、明示的または暗示的を問わず、本書にいかなる保証も与えるものではありません。 すべての当該著作権法を遵守することはお客様の責務です。Microsoftの書面による明確な許可なく、本書の如何なる部分についても、転載や検索システムへの格納または挿入を行うことは、どのような形式または手段（電子的、機械的、複写、レコーディング、その他）、および目的であっても禁じられています。 これらは著作権保護された権利を制限するものではありません。 Microsoftは、本書の内容を保護する特許、特許出願書、商標、著作権、またはその他の知的財産権を保有する場合があります。Microsoftから書面によるライセンス契約が明確に供給される場合を除いて、本書の提供はこれらの特許、商標、著作権、またはその他の知的財産へのライセンスを与えるものではありません。 © 2017 Microsoft Corporation. All rights reserved. Microsoft, Windows, その他本文中に登場した各製品名は、Microsoft Corporation の米国およびその他の国における登録商標または商標です。 その他、記載されている会社名および製品名は、一般に各社の商標です。