この記事では、SQL Server で統計専用データベースを作成するためのデータベース メタデータを使用して統計スクリプトを生成する方法について説明します。
元の製品バージョン: SQL Server
元の KB 番号: 914288
はじめに
DBCC CLONEDATABASE は、パフォーマンスの問題を調査するために、データベースのスキーマのみの複製を生成する場合に推奨される方法です。 この記事の手順は、 DBCC CLONEDATABASEを使用できない場合にのみ使用してください。
Microsoft SQL Server のクエリ オプティマイザーは、次の種類の情報を使用して最適なクエリ プランを決定します。
- データベース メタデータ
- ハードウェア環境
- データベース セッションの状態
通常、テスト システムでクエリ オプティマイザーの動作を再現するには、これらすべての同じ種類の情報をシミュレートする必要があります。
Microsoft カスタマー サポート サービスは、クエリ オプティマイザーの問題を調査するために、データベース メタデータのスクリプトを生成するように求められる場合があります。 この記事では、統計スクリプトを生成する手順と、クエリ オプティマイザーが情報を使用する方法についても説明します。
注
このデータ内に保存されたキーには PII 情報が含まれている可能性があります。 たとえば、テーブルに統計を含む Phone 番号 列が含まれている場合、各ステップの高いキー値は生成された統計スクリプトに含まれます。
データベース全体のスクリプトを作成する
統計のみの複製データベースを生成する場合、個々のオブジェクトをスクリプト化する代わりに、データベース全体をスクリプト化する方が簡単で信頼性が高い場合があります。 データベース全体のスクリプトを作成すると、次の利点があります。
- 問題を再現するために必要な依存オブジェクトが見つからない問題を回避できます。
- 必要なオブジェクトを選択するために必要な手順は少なくなります。
データベースのスクリプトを生成し、データベースのメタデータに何千ものオブジェクトが含まれている場合、スクリプト処理では CPU リソースが大量に消費されることに注意してください。 ピーク時間外にスクリプトを生成するか、2 番目のオプション Script Individual Objects を使用して個々のオブジェクトのスクリプトを生成することをお勧めします。
クエリによって参照される各データベースをスクリプト化するには、次の手順に従います。
SQL Server Management Studio を開きます。
オブジェクト エクスプローラーで、Databases を展開し、スクリプト化するデータベースを見つけます。
データベースを右クリックし、 Tasks をポイントし、 Generate Scripts を選択します。
スクリプト ウィザードで、正しいデータベースが選択されていることを確認します。 クリックしてデータベース全体とすべてのデータベース オブジェクト Script を選択し次へ を選択。
[スクリプト オプションの選択] ダイアログで、[Advanced] ボタンを選択して、次の設定を既定値から次の表に示す値に変更します。
スクリプト オプション 選択する値 Ansi 埋め込み はい エラー時のスクリプトの続行 はい 依存オブジェクトのスクリプトを生成する はい システム制約名を含める はい スクリプトの照合順序 はい スクリプト ログイン はい スクリプト オブジェクト レベルのアクセス許可 はい 統計のスクリプトを作成 スクリプトの統計とヒストグラム インデックスのスクリプトを作成 はい トリガーのスクリプトを作成 はい 注
Script Logins オプションと Script オブジェクト レベルのアクセス許可 オプションは、dbo以外のログインによって所有されているオブジェクトがスキーマに含まれていない場合は必要ありません。
OKを選択して変更を保存し、Advanced Scripting Options ページを閉じます。
[ファイルに保存]を選択しSingle ファイル オプションを選択します。
選択内容を確認し、 次へを選択します。
完了 を選択します。
個々のオブジェクトをスクリプト化する
完全なデータベースをスクリプト化する代わりに、特定のクエリによって参照される個々のオブジェクトのみをスクリプト化できます。 ただし、すべてのデータベース オブジェクトが WITH SCHEMABINDING 句を使用して作成されていない限り、 sys.depends システム テーブル内の依存関係情報が常に正確であるとは限りません。 この誤りにより、次のいずれかの問題が発生する可能性があります。
スクリプト作成プロセスでは、依存オブジェクトはスクリプト化されません。
スクリプト 処理では、オブジェクトを正しくない順序でスクリプト化する可能性があります。 スクリプトを正常に実行するには、生成されたスクリプトを手動で編集する必要があります。
そのため、データベースに多数のオブジェクトがあり、それ以外の場合はスクリプト作成に時間がかかりすぎる場合を除き、個々のオブジェクトをスクリプト化することはお勧めしません。 個々のオブジェクトのスクリプトを使用する必要がある場合は、次の手順に従います。
SQL Server Management Studio で、 Databases を展開し、スクリプト化するデータベースを見つけます。
データベースを右クリックし、 Script Database As をポイントし、 CREATE To をポイントして、 File を選択します。
ファイル名を入力し、 保存を選択します。
コア データベース コンテナーがスクリプト化されます。 このコンテナーには、ファイル、ファイル グループ、データベース、およびプロパティが含まれます。
データベースを右クリックし、 Tasks をポイントし、 Generate Scripts を選択します。
正しいデータベースが選択されていることを確認し、 次へを選択します。
[ Choose オブジェクトの種類 ] ダイアログボックスで、[特定のデータベース オブジェクト 選択問題のあるクエリが参照するすべてのデータベース オブジェクトの種類を選択します。
たとえば、クエリがテーブルのみを参照する場合は、 Tables を選択します。 クエリがビューを参照している場合は、 Views と Tables を選択します。 問題のあるクエリでユーザー定義関数が使用されている場合は、 Functions を選択します。
クエリによって参照されるすべてのオブジェクトの種類を選択したら、 Nextを選択します。
[スクリプト オプションの設定] ダイアログ で、[Advanced] ボタンを選択し、次の設定を既定値から [Advanced Scripting Options ページの次の表に示す値に変更します。
スクリプト オプション 選択する値 Ansi 埋め込み はい エラー時のスクリプトの続行 はい システム制約名を含める はい 依存オブジェクトのスクリプトを生成する はい スクリプトの照合順序 はい スクリプト ログイン はい スクリプト オブジェクト レベルのアクセス許可 はい 統計のスクリプトを作成 スクリプトの統計とヒストグラム USE DATABASE のスクリプトを作成 はい インデックスのスクリプトを作成 はい トリガーのスクリプトを作成 はい 注
スキーマに dbo 以外のログインによって所有されているオブジェクトがスキーマに含まれていない限り、Script ログインおよび Script オブジェクト レベルのアクセス許可 オプションは必要ない場合があることに注意してください。
OKを選択して、[Advanced Scripting Options ページを保存して閉じます。
手順 7 で選択したデータベース オブジェクトの種類ごとにダイアログが表示されます。
各ダイアログで、特定のテーブル、ビュー、関数、またはその他のデータベース オブジェクトを選択し、 Next を選択します。
Script to File オプションを選択し、手順 3 で入力したのと同じファイル名を指定します。
Finish を選択してスクリプトを開始します。
スクリプトが完了したら、スクリプト ファイルを Microsoft サポート エンジニアに送信します。 Microsoft サポート エンジニアは、次の情報を要求することもできます。
プロセッサの数や物理メモリの量など、ハードウェア構成。
クエリの実行時にアクティブだった SET オプション。
SQLDiag レポートまたは SQL Profiler トレースを送信して、この情報を既に提供している可能性があることに注意してください。 この情報を提供するために別の方法を使用したこともあります。
情報の使用方法
次の表は、クエリ オプティマイザーがこの情報を使用してクエリ プランを選択する方法を説明するのに役立ちます。
メタデータ
| 回答内容 | 説明 |
|---|---|
| 制約 | クエリ オプティマイザーは、クエリと基になるスキーマの間の矛盾を検出するために制約を頻繁に使用します。 たとえば、クエリに WHERE col = 5 句が含まれており、基になるテーブルに CHECK (col < 5) 制約が存在する場合、クエリ オプティマイザーは一致する行がないことを認識します。 クエリ オプティマイザーでは、null 許容性に関する同様の種類の推論が行われます。 たとえば、 WHERE col IS NULL 句は、列の null 許容性と、その列が外部結合の外部テーブルからのかどうかに応じて true または false であることがわかされています。 FOREIGN KEY 制約の存在は、カーディナリティと適切な結合順序を決定するのに役立ちます。 クエリ オプティマイザーでは、制約情報を使用して結合を排除したり、述語を簡略化することができます。 これらの変更により、ベース テーブルにアクセスするための要件が削除される場合があります。 |
| 統計 | 統計情報には、密度と、インデックスキーと統計キーの先頭列の分布を示すヒストグラムが含まれています。 述語の性質によっては、クエリ オプティマイザーで密度、ヒストグラム、またはその両方を使用して述語のカーディナリティを推定できます。 正確なカーディナリティ推定には、最新の統計が必要です。 カーディナリティ推定は、演算子のコストを見積もる際の入力として使用されます。 そのため、最適なクエリ プランを取得するには、適切なカーディナリティの見積もりが必要です。 |
| テーブル サイズ (行とページの数) | クエリ オプティマイザーでは、ヒストグラムと密度を使用して、特定の述語が true または false である確率を計算します。 最終的なカーディナリティ推定は、確率に子演算子が返す行数を乗算して計算されます。 テーブルまたはインデックス内のページ数は、IO コストを見積もる要因です。 テーブルサイズはスキャンのコストを計算するために使用され、インデックスシーク中にアクセスされるページ数を見積もるときに便利です。 |
| データベース オプション | 最適化には、いくつかのデータベース オプションが影響を与える可能性があります。
AUTO_CREATE_STATISTICSオプションとAUTO_UPDATE_STATISTICSオプションは、クエリ オプティマイザーが新しい統計を作成するか、古い統計を更新するかに影響します。 パラメーター化レベルは、入力クエリがクエリ オプティマイザーに渡される前の入力クエリのパラメーター化方法に影響します。 パラメーター化はカーディナリティ推定に影響を与え、インデックス付きビューやその他の種類の最適化との照合を防ぐこともできます。
DATE_CORRELATION_OPTIMIZATION設定により、オプティマイザーは列間の相関関係を検索します。 この設定はカーディナリティとコスト見積もりに影響します。 |
環境
| 回答内容 | 説明 |
|---|---|
| セッション SET オプション |
ANSI_NULLS設定は、NULL = NULL式が true と評価されるかどうかに影響します。 外部結合のカーディナリティ推定は、現在の設定によって変わる場合があります。 さらに、あいまいな式も変更される可能性があります。 たとえば、 col = NULL 式は、設定に基づいて異なる方法で評価されます。 ただし、 col IS NULL 式は常に同じ方法で評価されます。 |
| ハードウェア リソース | 並べ替え演算子とハッシュ演算子のコストは、SQL Server で使用できるメモリの相対的な量によって異なります。 たとえば、データのサイズがキャッシュより大きい場合、クエリ オプティマイザーは、データを常にディスクにスプールする必要があることを認識します。 ただし、データのサイズがキャッシュよりもはるかに小さい場合、操作はメモリ内で実行される可能性があります。 また、サーバーに複数のプロセッサがあり、 MAXDOP ヒントまたは並列処理の最大次数構成オプションを使用して並列処理が無効になっていない場合は、異なる最適化も考慮されます。 |