統計スクリプトを生成して、SQL Serverで統計専用データベースを作成する方法

  • [アーティクル]

この記事では、SQL Serverで統計専用データベースを作成するためのデータベース メタデータを使用して統計スクリプトを生成する方法について説明します。

元の製品バージョン: SQL Server 2014、SQL Server 2012、SQL Server 2008

元の KB 番号: 914288

概要

DBCC CLONEDATABASE は、パフォーマンスの問題を調査するために、データベースのスキーマのみの複製を生成する推奨される方法です。 を使用できない場合にのみ、この記事の手順を使用 DBCC CLONEDATABASEします。

Microsoft SQL Serverのクエリ オプティマイザーでは、次の種類の情報を使用して最適なクエリ プランを決定します。

  • データベース メタデータ
  • ハードウェア環境
  • データベース セッションの状態

通常、テスト システムでクエリ オプティマイザーの動作を再現するには、これらすべての同じ種類の情報をシミュレートする必要があります。

Microsoft カスタマー サポート サービスは、クエリ オプティマイザーの問題を調査するために、データベース メタデータのスクリプトを生成するように求められる場合があります。 この記事では、統計スクリプトを生成する手順と、クエリ オプティマイザーが情報を使用する方法についても説明します。

注:

このデータ内に保存されたキーには、PII 情報が含まれている場合があります。 たとえば、テーブルに統計を含む 電話番号 列が含まれている場合、各ステップの高いキー値は生成された統計スクリプトに含まれます。

データベース全体をスクリプト化する

統計のみの複製データベースを生成する場合、個々のオブジェクトをスクリプト化するのではなく、データベース全体をスクリプト化する方が簡単で信頼性が高い場合があります。 データベース全体をスクリプト化すると、次の利点が得られます。

  • 問題を再現するために必要な依存オブジェクトが見つからない問題は回避できます。
  • 必要なオブジェクトを選択するには、少ない手順が必要です。

データベースのスクリプトを生成し、データベースのメタデータに何千ものオブジェクトが含まれている場合、スクリプト作成プロセスでは大量の CPU リソースが消費されることに注意してください。 ピーク時以外の時間帯にスクリプトを生成するか、2 番目のオプション [ 個別オブジェクトのスクリプト] を使用して個々のオブジェクトのスクリプトを生成することをお勧めします。

クエリによって参照される各データベースをスクリプト化するには、次の手順に従います。

  1. SQL Server Management Studioを開きます。

  2. オブジェクト エクスプローラーで、[データベース] を展開し、スクリプトを作成するデータベースを見つけます。

  3. データベースを右クリックし、[ タスク] をポイントし、[ スクリプトの生成] を選択します。

  4. スクリプト ウィザードで、正しいデータベースが選択されていることを確認します。 [ データベース全体とすべてのデータベース オブジェクトのスクリプト] をクリックして選択し、[ 次へ] を選択します。

  5. [ スクリプト オプションの選択] ダイアログで、[ 詳細設定 ] ボタンを選択して、次の設定を既定値から次の表に示す値に変更します。

    スクリプト オプション 選択する値
    Ansi パディング はい
    エラー時のスクリプトの続行 はい
    依存オブジェクトのスクリプトを生成する はい
    システム制約名を含める はい
    スクリプト照合順序 はい
    スクリプト ログイン はい
    スクリプト オブジェクト レベルのアクセス許可 はい
    スクリプト統計 統計とヒストグラムをスクリプト化する
    スクリプト インデックス はい
    スクリプト トリガー はい

    注:

    スキーマに dbo 以外のログインが所有するオブジェクトが含まれていない限り、[スクリプト ログイン] オプションと [スクリプト オブジェクト レベルのアクセス許可] オプションは必要ない場合があることに注意してください。

  6. [ OK] を選択 して変更を保存し、[ 高度なスクリプトオプション] ページを 閉じます。

  7. [ ファイルに保存] を 選択し、[ 単一ファイル ] オプションを選択します。

  8. 選択内容を確認し、[ 次へ] を選択します。

  9. [完了] を選択します。

個々のオブジェクトをスクリプト化する

完全なデータベースをスクリプト化する代わりに、特定のクエリによって参照される個々のオブジェクトのみをスクリプト化できます。 ただし、句を使用して WITH SCHEMABINDING すべてのデータベース オブジェクトが作成されていない限り、システム テーブル内の sys.depends 依存関係情報が常に正確であるとは限りません。 この不正確さが原因で、次のいずれかの問題が発生する可能性があります。

  • スクリプト 処理では、依存オブジェクトはスクリプト化されません。

  • スクリプト プロセスでは、オブジェクトを正しくない順序でスクリプト化する可能性があります。 スクリプトを正常に実行するには、生成されたスクリプトを手動で編集する必要があります。

そのため、データベースに多数のオブジェクトがあり、それ以外の場合はスクリプト作成に時間がかかる場合を除き、個々のオブジェクトをスクリプト化することはお勧めしません。 個々のオブジェクトのスクリプトを使用する必要がある場合は、次の手順に従います。

  1. SQL Server Management Studioで、[データベース] を展開し、スクリプトを作成するデータベースを見つけます。

  2. データベースを右クリックし、[ スクリプト データベース] をポイントし、[ CREATE To] をポイントし、[ファイル] を選択 します。

  3. ファイル名を入力し、[保存] を選択 します。

    コア データベース コンテナーがスクリプト化されます。 このコンテナーには、ファイル、ファイル グループ、データベース、およびプロパティが含まれます。

  4. データベースを右クリックし、[ タスク] をポイントし、[ スクリプトの生成] を選択します。

  5. 正しいデータベースが選択されていることを確認し、[ 次へ] を選択します。

  6. [ オブジェクトの種類の選択 ] ダイアログで、[ 特定のデータベース オブジェクトの選択] を選択し、問題のあるクエリが参照するすべてのデータベース オブジェクトの種類を選択します。

    たとえば、クエリがテーブルのみを参照する場合は、[ テーブル] を選択します。 クエリがビューを参照している場合は、[ ビューとテーブル] を選択します。 問題のあるクエリでユーザー定義関数が使用されている場合は、[関数] を選択 します。

  7. クエリによって参照されるすべてのオブジェクトの種類を選択したら、[ 次へ] を選択します。

  8. [ スクリプト オプションの設定] ダイアログで、[ 詳細設定 ] ボタンを選択し、次の設定を既定値から [ 詳細スクリプト オプション] ページの次の表に示す値に変更します。

    スクリプト オプション 選択する値
    Ansi パディング はい
    エラー時のスクリプトの続行 はい
    システム制約名を含める はい
    依存オブジェクトのスクリプトを生成する はい
    スクリプト照合順序 はい
    スクリプト ログイン はい
    スクリプト オブジェクト レベルのアクセス許可 はい
    スクリプト統計 統計とヒストグラムをスクリプト化する
    USE DATABASE のスクリプト はい
    スクリプト インデックス はい
    スクリプト トリガー はい

    注:

    スキーマに dbo 以外のログインが所有するオブジェクトが含まれていない限り、[スクリプト ログイン] オプションと [スクリプト オブジェクト レベルのアクセス許可] オプションは必要ない場合があることに注意してください。

  9. [ OK] を選択 して、[ 詳細スクリプト オプション] ページを保存して 閉じます。

    手順 7 で選択したデータベース オブジェクトの種類ごとにダイアログが表示されます。

  10. 各ダイアログで、特定のテーブル、ビュー、関数、またはその他のデータベース オブジェクトを選択し、[ 次へ] を選択します。

  11. [ ファイルへのスクリプト ] オプションを選択し、手順 3 で入力したのと同じファイル名を指定します。

  12. [ 完了] を選択 してスクリプトを開始します。

    スクリプトの作成が完了したら、スクリプト ファイルを Microsoft サポート Engineer に送信します。 Microsoft サポート エンジニアは、次の情報も要求する場合があります。

    • プロセッサの数や物理メモリの量など、ハードウェア構成。

    • クエリの実行時にアクティブだった SET オプション。

    SQLDiag レポートまたは SQL Profiler トレースを送信して、この情報を既に提供している可能性があることに注意してください。 この情報を提供するために別のメソッドを使用したこともあります。

情報の使用方法

次の表は、クエリ オプティマイザーがこの情報を使用してクエリ プランを選択する方法を説明するのに役立ちます。

メタデータ

オプション 説明
制約 クエリ オプティマイザーは、クエリと基になるスキーマの間の矛盾を検出するために、制約を頻繁に使用します。 たとえば、クエリに句CHECK (col < 5)がWHERE col = 5含まれており、基になるテーブルに制約が存在する場合、クエリ オプティマイザーは一致する行がないことを認識します。 クエリ オプティマイザーは、null 許容性に関する同様の種類の推論を行います。 たとえば、 句は、列の WHERE col IS NULL null 許容性と、その列が外部結合の外部テーブルからのかどうかに応じて、true または false であることがわかります。 FOREIGN KEY 制約の存在は、カーディナリティと適切な結合順序を決定するのに役立ちます。 クエリ オプティマイザーでは、制約情報を使用して結合を排除したり、述語を簡略化することができます。 これらの変更により、ベース テーブルにアクセスするための要件が削除される場合があります。
統計 統計情報には、インデックスキーと統計キーの先頭列の分布を示す密度とヒストグラムが含まれています。 述語の性質によっては、クエリ オプティマイザーで密度、ヒストグラム、またはその両方を使用して述語のカーディナリティを推定できます。 正確なカーディナリティ推定には、最新の統計が必要です。 カーディナリティの見積もりは、演算子のコストを見積もる際の入力として使用されます。 そのため、最適なクエリ プランを取得するには、適切なカーディナリティの見積もりが必要です。
テーブル サイズ (行数とページ数) クエリ オプティマイザーは、ヒストグラムと密度を使用して、特定の述語が true または false である確率を計算します。 最終的なカーディナリティ推定は、確率に子演算子が返す行数を乗算して計算されます。 テーブルまたはインデックス内のページ数は、IO コストを見積もる要因です。 テーブル サイズはスキャンのコストを計算するために使用され、インデックス シーク中にアクセスされるページ数を見積もるときに便利です。
データベース オプション 最適化には、いくつかのデータベース オプションが影響を与える可能性があります。 オプションと AUTO_UPDATE_STATISTICS オプションはAUTO_CREATE_STATISTICS、クエリ オプティマイザーが新しい統計を作成するか、古い統計を更新するかに影響します。 パラメーター化レベルは、入力クエリをクエリ オプティマイザーに渡す前の入力クエリのパラメーター化方法に影響します。 パラメーター化はカーディナリティ推定に影響を与える可能性があり、インデックス付きビューやその他の種類の最適化との照合を防ぐこともできます。 この DATE_CORRELATION_OPTIMIZATION 設定により、オプティマイザーは列間の相関関係を検索します。 この設定は、カーディナリティとコスト見積もりに影響します。

環境

オプション 説明
セッション SET オプション この設定は ANSI_NULLS 、式が NULL = NULL true と評価されるかどうかに影響します。 外部結合のカーディナリティ推定は、現在の設定に応じて変更される場合があります。 さらに、あいまいな式も変更される可能性があります。 たとえば、式は col = NULL 設定に基づいて異なる方法で評価されます。 ただし、式は col IS NULL 常に同じ方法で評価されます。
ハードウェア リソース 並べ替え演算子とハッシュ演算子のコストは、SQL Serverで使用できるメモリの相対的な量によって異なります。 たとえば、データのサイズがキャッシュよりも大きい場合、クエリ オプティマイザーは、データを常にディスクにスプールする必要があることを認識します。 ただし、データのサイズがキャッシュよりもはるかに小さい場合、操作はメモリ内で実行される可能性があります。 SQL Serverは、サーバーに複数のプロセッサがあり、ヒントまたは並列処理の最大レベルの構成オプションを使用MAXDOPして並列処理が無効になっていない場合にも、さまざまな最適化が考慮されます。

関連項目