Free Press

データベーステーブルにSQLインデックスを作成する背景には、データ検索プロセスを高速化することによって、ベーステーブルからデータを読み取るクエリのパフォーマンスを向上させるという目的があります。 しかし、これらのトランザクション テーブルは静的ではなく、時間の経過とともに非常に頻繁に変更されます。 データベース・テーブルで行われるこれらの変更には、新しいレコードの追加、既存のレコードの変更または削除が含まれます。 これらの変更は、関連するテーブル インデックスに複製される必要があるため、テーブル インデックスは時間の経過とともに断片化されます。

SQL Server Index Fragmentation Overview

SQL インデックス断片化には、主に内部断片化と外部断片化の 2 種類があります。 内部フラグメンテーションは、新しいレコードを挿入したり、データページの現在の空き領域に収まらない値で既存のレコードを更新すると、新しい値に合うようにページを2つのページに分割することによって発生します。 この場合、SQL Server エンジンは、SQL インデックスツリーのバランスを保つために、現在のページから、分割操作によって生成された新しいデータページに、データの約 50 % を移動させます。 内部的な断片化は、ランダムな削除操作によってデータページに空き領域が生じることでも発生する可能性があります。 このタイプの操作では、データ ページが埋められずに残り、SQL Server インデックス サイズが大きくなり、要求されたデータを取得するために多くのデータ ページを読み取る必要があるためパフォーマンスが低下します。

外部フラグメントは、SQL Server インデックスの論理順序が下位のディスク ドライブ上の物理順序と一致していないために、SQL Server インデックス データ ページがデータベース物理ファイル上に散在していると発生します。 外部フラグメントは、エクステント自体が予約されているにもかかわらず、エクステント・ページのいくつかを空のままにするランダムな削除操作によって発生することがあります。 このタイプの断片化は、下位のディスク ドライブから要求されたデータを取得するために、散在したデータ ページ間をジャンプするのに多くの時間とリソースを消費するため、パフォーマンスの低下を招きます。 データがデータベース ファイル内の連続したデータ ページに格納されている健全な SQL インデックスから特定の数の行を読み取る場合と、データがデータベース ファイル内の連続しないデータ ページに散らばっている、非常に断片化した SQL Server インデックスから同じ数の行を読み取る場合の時間とディスク読み込み回数の差を想像してみてください。 たとえば、断片化率が30％未満のSQL Serverインデックスは再編成することができ、断片化率が30％以上のSQLインデックスは再構築する必要があります。

• 注意：詳細については、SQL Serverインデックスの保守を確認してください。

インデックスを適切に設計し、Fill Factor と pad_index インデックス作成オプションを propervalues で設定することにより、SQL Server インデックス断片化とページ分割の問題を積極的に克服できます。

• 注：詳細は SQL Server Index Operations を参照してください。

SQL Server Index Fragmentation Report

データ検索操作の強化を利用するためにデータベース テーブルに SQL インデックスを作成し、その断片化を継続的に監視して維持せずにその SQL Server インデックスを永遠に放置することは、ベスト プラクティスとは見なされていないでしょう。

SQL Server Index Properties

最初の方法は、SQL Server Index Properties ウィンドウの Fragmentation ページを使用する方法です。 確認したいインデックスを右クリックし、[プロパティ]オプションを選択します。 SQL Server インデックス プロパティ］ページから［フラグメンテーション］ページを参照すると、以下のように［フラグメンテーション］セクションにインデックスの断片化率とSQL Serverインデックスページがどれだけいっぱいになっているかが表示されます：

数ステップで、インデックスのプロパティ ウィンドウから選択したインデックスのSQL Serverインデックス断片化率を簡単にチェックすることができます。 しかし、すべてのテーブルのインデックスまたはすべてのデータベース テーブルのインデックスの断片化率の概要を確認する必要がある場合はどうしたらよいでしょうか。 この場合、1つ1つ確認する必要があります。 その結果、1つのデータベース・レポートに対して1週間では足りなくなります！

sys.dm_db_index_physical_stats

データベース・インデックスの断片化率をチェックするために使用できる別の方法は、非推奨の DBCC SHOWCONTIG コマンドの代わりとして SQL Server 2005 で初めて導入された動的管理関数 sys.dm_db_index_physical_stats にクエリーを実行することです。 これは、データベースインデックスのサイズと断片化率に関する情報を提供します。

sys.dm_db_index_physical_stats DMFから指定したデータベース下のすべてのインデックスの断片化率に関する意味のある情報を得るために、これをsys.DB_index_physical_stats DMFと結合することが必要です。indexes DMVと結合する必要があります。以下のT-SQLスクリプトのように:

SELECT OBJECT_NAME(Index_Info.OBJECT_ID) AS TableName ,Index_Info.name AS IndexName ,Index_Stat.index_type_desc AS IndexType ,Index_Stat.avg_fragmentation_in_percent IndexFragmPercentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) Index_StatINNER JOIN sys.indexes Index_Info ON Index_Info.object_id = Index_Stat.object_id AND Index_Info.index_id = Index_Stat.index_idORDER BY IndexFragmPercent DESC

SQL開発スキルに基づいて、SQLインデックス断片化を取得するために必要なフィルターを実行することができます。 この場合、結果は次のようになります。

SQL Server Index Physical Statistics Standard Report

SQL Server には、選択したデータベースに関する有用な統計情報を含むいくつかの標準レポートが用意されています。 インデックス物理統計レポートは、SQL Server 標準レポートの 1 つで、最後の SQL Server サービスの再起動以来、データベース レベルでの SQL Server インデックス パーティション、断片化率、および各 SQL インデックス パーティションのページ数に関する統計情報を返し、SQL Server インデックスの断片化率に基づいてインデックス断片化の問題を修正する方法を推奨するものです。

SQL Server インデックス物理統計レポートを表示するには、データベースを右クリックして、レポート -> 標準レポート を選択し、インデックス物理統計レポートを選択します（下図）。

ApexSQL Defrag

ApexSQL Defragツールは、SQL Serverインデックス断片化および使用情報をチェックし、インデックス断片化問題に対して適切な修正を実行またはスケジュールするために簡単に使用できるサードパーティSQLインデックスデフラグ・ツールです。

SQL Server のインデックスレベル、テーブルレベル、データベースレベル、そして SQL Server インスタンスレベルなど、異なるレベルの断片化および使用情報を一元的に分析および確認することが可能です。

ApexSQL Defrag では、テーブルインデックスに対する Fill Factor の設定、すべてのデフラグジョブとポリシーに対するアラートの作成、ジョブの成功または失敗に対する電子メール通知の送信を行うことも可能です。 これは、この情報が ApexSQL Defrag ツール用の中央リポジトリデータベースに保存されるためです。

ApexSQL Defrag のインストール

ApexSQL Defrag は、ApexSQL Download Center から簡単にダウンロードでき、次のような簡単なインストールウィザードに従ってサーバーにインストールできます：

最初に、ApexSQL Defrag エージェントの実行コンテキストとして使用されるサービスアカウントを指定し、コマンドを SQL Server インスタンスに伝達します。

ApexSQL Defragサービスアカウントを指定した後、ApexSQL Defragツールをインストールする場所とデスクトップにツールのショートカットアイコンを作成するかどうかを尋ねるダイアログが表示されます。 そして、以下の通知が表示され、ApexSQL Defragを直接起動することができます。

ApexSQL Defragを初めて実行する場合、これらのデータの履歴と設定データを格納する中央リポジトリデータベースを作成するかどうか、以下のように確認されます。

Add New Server

リポジトリデータベースを作成した後、SQLインスタンスがない状態でApexSQL Defragを開始します。 特定のSQL ServerインスタンスでSQLインデックスの断片化率を確認するには、そのインスタンスをそのツールに追加する必要があります。 そのためには、以下のように [ホーム] タブの [追加] ボタンをクリックします。

Connect to SQL Server ウィンドウでは、以下のように使用する SQL Server インスタンスの名前とそのサーバーへの接続に必要な資格情報を入力するよう求められます。

SQL Server Index Analysis

サーバーを追加すると、以下のFragmentationタブからすべてのSQLインデックス断片化情報の新しいチェックを実行できます：

ApexSQL Defragでは3つのモードに基づいてそのインスタンスのすべてのインデックスを分析できる機能が提供されます。

• DETAILED: スキャンプロセス中にすべてのデータと SQL インデックスページが読み込まれます
• SAMPLED: SQL Server インデックスには 10,000 ページ以上あることを考慮して、ページの 1% しか読み込まれません
• LIMITED: スキャンプロセス中にすべてのデータページが読み込まれます。 B-treeの親レベルに位置するページのみが読み込まれる

接続したSQL ServerインスタンスのSQL Serverインデックス情報を分析するには、FragmentationタブのAnalyzeボタンをクリックし、Fast分析またはDeep分析を選択する必要があります。 ここでは、以下のように、完全な情報を得るために、Deep分析を実行します。

SQL Server インスタンス、データベース、テーブル、インデックスなど、異なるレベル間を同じ中央ウィンドウでシングル クリックで移動でき、特定の断片化レベルまたは閾値に基づいて取得した結果をフィルタリングする機能は、次のとおりです。

また、レポートカラムから任意のカラムをドラッグして、そのカラムの値に基づいてレポート全体をグループ化することができます（以下の例では、テーブル名に基づいてレポートデータをグループ化しています：

ApexSQL Defrag レポート

ApexSQL Defrag ではデータベースインデックスに関する統計情報を示すいくつかのレポートが表示されます。 これらのレポートは、以下のレポートタブで設定および確認できます。

最初のレポートは、合計レポートです。 例えば、インスタンス名をクリックすると、以下のように SQL Server インスタンスレベルの断片化率グラフとスペース使用量グラフが表示されます。 このレポートは、以下のように、選択されたレベルでのインデックスサイズと断片化の合計とインデックスタイプの分布に関する統計情報を表示します：

3番目のレポートは、トップ10レポートで、指定したレベルで、最大のサイズと最高のSQLインデックス断片化率を持つクラスタ化および非クラスタ化インデックスのトップ10を表示します。 たとえば、次のレポートは、SQL サーバーインスタンスレベルで、サイズと断片化に基づいて、高価なインデックスの上位 10 個を表示します：

特定のデータベースに移動すると、レポートは、以下のように、選択したデータベースレベルで、サイズと断片化に基づいて、高価なインデックスの上位 10 個を自動的に表示します。

そのデータベースの下の特定のテーブルに深く潜って、レポートは次のように、サイズと断片化に基づいて、高価なインデックスの上位10個を表示します。

ApexSQL Defrag Report Export

ApexSQL Defragでは、断片化レポートをPDF、IMG、HTML、CS、XMLなどの異なるファイル形式でエクスポートでき、生成前にレポート設定をカスタマイズすることも可能です。 レポート] タブで [オプション] ボタンをクリックします。

[エクスポート オプションの編集] ウィンドウから、以下に示すように、生成ファイルの命名規則、ファイルの保存先、ページの向き、そして最後に各レポートの内容など、異なるオプションを設定することができます。

例えば、生成された合計レポートを特定のレベルでエクスポートするには、レポートタブでエクスポートボタンをクリックし、この合計レポートをエクスポートするファイルの種類を選択します。 数秒後、レポートが生成され、生成されたレポートを開くかどうか尋ねられます。

さらに、レポートタブの下にあるエクスポートボタンをクリックし、エクスポートファイルの種類を指定すると、指定したレベルのトップ10レポートをエクスポートすることができ、レポートはすぐに生成され、生成されたレポートを直接表示するかどうか尋ねられます。

前の例から明らかなように、ApexSQL Defragツールを使用して、SQL ServerインスタンスレベルからSQL Serverインデックスレベルまで、異なるレベルでのSQLインデックス断片化率情報およびダイビングを簡単に確認および分析し、異なるエクスポート形式で表示することができ、サーバーまたはサービスの再起動によって表示情報が影響を受けることはなく、これらはリポジトリデータベースに保存されます。