第 3 章 SQL Server 2000 データベースの管理、監視、およびトラブルシューティング～ MCDBA をめざそう!! ～
NRI ラーニング ネットワーク株式会社
技術研修部
清藤 めぐみ
2001年12月14日
はじめに
MCP 試験を受験される方はご存知かと思いますが、Microsoft のサイト（https://www.microsoft.com/japan/learning/mcp/exam.aspx?cert=1&id=70-228 .gif)
）で試験範囲を確認することができます。一度は目を通しておくことをお勧めします。本連載は、試験トピックスの章立てに併せてポイントを解説していきます。
目次
.gif){kind=icon}
1.データベースパフォーマンスの最適化
2. データストレージの最適化
3. 障害回復操作
今回は作成したデータベースをどのように管理していくのか、また、トラブル発生時の対処方法を確認しましょう。
「SQL Server 2000 データベースの管理、監視、およびトラブルシューティング」では次の項目がポイントとなります。
データベースパフォーマンスの最適化（インデックス、ロック）
データストレージの最適化（ファイルとファイルグループ、圧縮）
障害回復操作（バックアップ、ログ配布）
1.データベースパフォーマンスの最適化
1.1 インデックス
インデックスを作成すると、データの取得が高速になります。SQL Server 2000 では、2 種類のインデックスを作成できます。
| クラスタ化インデックス | 非クラスタ化インデックス |
|---|---|
| 辞書のようなイメージ | 本の索引のようなイメージ テーブルに 249 個まで作成可能 行の物理的な順序は並べ替えられない クラスタ化インデックスがある場合、各インデックス行には、クラスタ化キーが格納される クラスタ化インデックスがない場合、各インデックス行には、データ行へのポインタが格納 |
表 1.1.1 インデックスの種類
インデックスは次のような頻繁に検索に使用する列に作成します。
主キー
テーブル結合時に使用される外部キーまたは列
並べ替え順で頻繁にアクセスされる列
集計中にグループ化される列
インデックスを作成しない列は次のような場合です。
クエリ内でほとんど参照しない列
一意の値をほとんど含まない列
text、ntext、image 型で定義されている列
また、次のような場合は複数の列をキー値として指定する複合インデックスを作成します。
検索キーとして最適な列が 2 つ以上ある場合（最大 16 個まで組み合わせ可能）
クエリがインデックス内の列だけを参照する場合
複合インデックスを構成する各列の長さの合計は 900 バイトを超えることはできません。
複合インデックスは姓と名で構成される電話帳のようなイメージです。
参照: |
SQL Server 2000 データベースプログラミング Vol.1（P.237～238、242～243、259、283） |
Books Online（クラスタ化インデックスの使用、非クラスタ化インデックスの使用） |
1.2 ロック
SQL Server 2000 は、トランザクションの同時実行トランザクションがリソースにアクセスする方法を決めるため、ロックモードを使用して、リソースをロックします。主なロックモードには次のものがあります。
| ロックモード |
| 説明 |
|---|---|---|
| 共有（S） |
| SELECTなど、読み取り専用の処理で使用 |
| 更新（U） |
| 更新可能なリソースで使用 |
| 排他（X） |
| UPDATE、INSERT、DELETE などのデータ変更処理で使用 1つのリソースを同時に更新しないようにするロックモード |
| イ ン テ ン ト | インテント共有（IS） | 階層の下位にある一部のリソースに S ロックが設定され、そのリソースを読み取るトランザクションがあることを示す |
| イ ン テ ン ト | インテント排他（IX） | 階層の下位にある一部のリソースに X ロックが設定され、そのリソースを変更するトランザクションがあることを示す |
| イ ン テ ン ト | インテント排他付き共有（SIX） | リソースの同時読み取りを許可し、階層の下位にある一部のリソースにIXロックを設定して、トランザクションがそのリソースを変更する予定があることを示す |
表 1.2.1 ロックモード
また、既にロックがかけられているリソースには、それと互換性のあるロックのみかけることができます。
|
| 既にかけられているモード | 既にかけられているモード | 既にかけられているモード | 既にかけられているモード | 既にかけられているモード | 既にかけられているモード |
|---|---|---|---|---|---|---|
| 要求されたモード | IS | S | U | IX | SIX | X |
| インテント共有（IS） | ○ | ○ | ○ | ○ | ○ | × |
| 共有（S） | ○ | ○ | ○ | × | × | × |
| 更新（U） | ○ | ○ | × | × | × | × |
| インテント排他（IX） | ○ | × | × | ○ | × | × |
| インテント排他付き共有（SIX） | ○ | × | × | × | × | × |
| 排他（X） | × | × | × | × | × | × |
表 1.2.2 ロックの互換性
ロックされるリソースは、粒度の細かいものから RID（行）、キー、ページ、エクステント、テーブル、データベースです。
例えば、次の図 1.2.1 のようになっている場合、SPID 57 の KEY の排他ロックを終了しないと、クエリを実行できない可能性があります。
.gif)
図 1.2.1 ロックの状態
参照: Books Online（ロックアーキテクチャ、SQL Server のロック、ロックの互換性）
2. データストレージの最適化
2.1 ファイルとファイルグループ
データベースは第 2 章で確認したように、データファイルとログファイルにわかれています。また、それぞれのファイルを複数持つことができるようになっています。SQL サーバに複数の物理ディスクがある場合は、データを各ディスクに分散して格納するために各ディスクにファイルを作成し、1 つ以上のファイルグループにまとめることができます。ファイルグループを複数作成することにより、システムテーブルとデータを分けて格納することができ、ファイルグループ内のすべてのファイルに対して、均衡のとれたデータの書き込みを行うことができます。また、データベース全体だけではなく、ファイル、またはファイルグループ単位でバックアップや復元を行うことができ、大規模データベースの場合、バックアップや復元にかかる時間を短縮することができます。
トランザクションログファイルはシーケンシャルに書き込まれるため、トランザクションログファイルをデータファイルとは別のディスク上に作成すれば、ディスクヘッドが次の書き込み位置に留まることができるので、パフォーマンスを高めることができます。
例えば、図 2.1.1 のように構成します。
.gif)
図 2.1.1 ファイルとファイルグループの配置例
参照: MSUテキスト「SQL Server 2000 システム管理（P.130～140)」
2.2 圧縮
データベースは自動的に圧縮されるように設定することができます。自動圧縮を有効にした場合は、ファイルの未使用領域が 25 % を超えたときにデータファイルおよびトランザクションログファイルが圧縮されます。トランザクションログの圧縮はデータベースの復元に必要なアクティブな論理ログが含まれていない場合に限って実行されます。
手動でデータベースを圧縮するには、DBCC SHRINKDATABASE ステートメントまたは、DBCC SHRINKFILE ステートメントを実行します。
DBCC SHRINKDATABASE
データベース単位、初期サイズよりも小さくすることはできないDBCC SHRINKFILE
ファイル単位、初期サイズ以下に圧縮可能
参照: SQL Server 2000システム管理（P.126～128）
3. 障害回復操作
3.1 バックアップ
データベースのバックアップはフルバックアップと差分バックアップがあります。また、トランザクションログのみバックアップを行うことができます。
.gif)
図 3.1.1 データベースバックアップ
図 3.1.1 のようにフルバックアップと差分バックアップとトランザクションログのバックアップを組み合わせて実行しているときに、障害が発生した場合、次の作業を行います。
NO_TRUNCATE オプション付きのトランザクションログのバックアップを実行
最新のフルバックアップを復元
最新の差分バックアップ（差分バックアップ（B））を復元
最新のバックアップ以降のすべてのトランザクションログバックアップ（トランログバックアップ(C)、（D）、（E））を復元
NO_TRUNCATE オプション付きでトランザクションログをバックアップすると、データベースを障害発生直前の状態に戻すことができます。
参照: MSUテキスト「SQL Server 2000 システム管理 (P.332～338)」
3.2 ログ配布
Windows 2000 Server では、ログ配布機能を使用して、データベースのトランザクションログを別のデータベースにコピーすることができます。配布元になるサーバがプライマリサーバ、配布先になるサーバがスタンバイサーバです。障害発生時には、プライマリサーバとスタンバイサーバの役割を変更することができます。また、プライマリサーバの負荷を軽減するためにスタンバイサーバを読み取り専用のサーバとして使用することも可能です。
配布先データベースの使用方法をオプションで設定できます（図 3.2.1）。[データベースの読み込み状態] を [復旧モード以外] に設定すると配布先データベースは使用不可能になり、[スタンバイモード] に設定すると、読み取り専用で使用できます。また、[データベースがプライマリロールを想定することを許可する] を有効にすると、障害発生時に、プライマリサーバとして動作するように役割変更が可能になります。
.gif)
図 3.2.1 配布先データベースの追加
参照: |
MSUテキスト「SQL Server 2000 システム管理 (P.517～520)」 |
Books Online（ログ配布、[配布先データベースの追加]または[配布先データベースの編集]ダイアログボックス） |
3 章の内容に関しては、70-228 試験の対応コースになっている SQL Server 2000 システム管理のテキストのみの勉強だけでは不十分です。インデックスの問題はかなり出題率が高いのですが、インデックスの詳細は「SQL Server 2000 データベースプログラミング」のテキストに記載されています。ですので、時間のある方はコースを受講されるといいでしょう。
.jpg)
清藤 めぐみ : 1997 年に Microsoft 認定トレーナ（MCT）の資格を取得し、Windows NT のトレーニングを担当するようになりました。現在は NRI ラーニングネットワークに所属し、Windows 2000 などのトレーニングや、技術書籍の監修を行う毎日で、あっという間に時間が過ぎてしまいます。そんな状態なので、最近はちょっとストレスがたまり気味。ストレス解消のため、ショッピングにはまってしまい、財布の中身が心許ない今日この頃です。 皆さんのご期待にそえるような記事作りをめざしていきます。よろしくお願いします。