[アーティクル]
09/28/2010

クエリのパフォーマンスを向上させるための統計の使用

クエリオプティマイザーでは、クエリのパフォーマンスを向上させるクエリプランを作成するために統計を使用します。ほとんどのクエリでは、高品質のクエリプランに必要な統計がクエリオプティマイザーによって既に生成されていますが、最適な結果を得るために追加の統計情報を作成したりクエリのデザインを変更したりする必要がある場合もあります。

このトピックでは、クエリの最適化に関する統計の概念と、それを効果的に使用するためのガイドラインについて説明します。

クエリの最適化に関する統計とは
データベース全体の統計オプションの使用
統計を作成する場合の判断
統計を更新する場合の判断
統計を効果的に使用したクエリのデザイン

クエリプラン、およびクエリプランとクエリパフォーマンスの関係性の詳細については、「クエリの分析」を参照してください。

クエリの最適化に関する統計とは

クエリの最適化に関する統計は、テーブルまたはインデックス付きビューの 1 つまたは複数の列の値の分布に関する統計情報を格納するオブジェクトです。クエリオプティマイザーでは、これらの統計を使用してクエリ結果の基数、つまり行数を推定します。これらの基数の推定に基づいて、クエリオプティマイザーでは高品質なクエリプランを作成できます。たとえば、クエリオプティマイザーでは、基数の推定に基づいて、リソースの消費が多い Index Scan 操作ではなく Index Seek 操作が使用される場合があります。この場合、クエリのパフォーマンスが向上します。

統計オブジェクトは 1 つ以上のテーブル列で構成されるリストごとに作成され、それぞれに最初の列の値の分布を示すヒストグラムが含まれます。複数列の統計オブジェクトには、さらに、列間の値の相関関係に関する統計情報も格納されます。これらの相関関係の統計情報 (密度) は、個別の列値を持つ行の数から得られます。統計オブジェクトの詳細については、「DBCC SHOW_STATISTICS (Transact-SQL)」を参照してください。

フィルター選択された統計情報

適切に定義されたデータのサブセットから選択するクエリでは、フィルター選択された統計情報を使用するとクエリのパフォーマンスを向上させることができます。フィルター選択された統計情報では、統計情報に含まれるデータのサブセットを選択するためにフィルター述語を使用します。統計情報を適切にフィルター選択すると、テーブル全体の統計情報を使用する場合と比べて、クエリ実行プランが向上します。フィルター述語の詳細については、「CREATE STATISTICS (Transact-SQL)」を参照してください。フィルター選択された統計情報を作成する場合の詳細については、このトピックの「統計を作成する場合の判断」を参照してください。ケーススタディについては、SQLCAT Web サイトのブログ「Using Filtered Statistics with Partitioned Tables」(英語) を参照してください。

データベース全体の統計オプションの使用

以下のデータベース全体の統計オプションの機能を理解し、それらが適切に設定されていることを確認することが重要です。

AUTO_CREATE_STATISTICS
AUTO_UPDATE_STATISTICS
AUTO_UPDATE_STATISTICS_ASYNC

AUTO_CREATE_STATISTICS オプションを使用する

統計の自動作成オプション AUTO_CREATE_STATISTICS がオンの場合、クエリプランの基数の推定を向上させるために、クエリオプティマイザーによってクエリ述語内の個々の列に関する統計が必要に応じて作成されます。これらの 1 列ずつの統計は、既存の統計オブジェクトにまだヒストグラムがない列について作成されます。

次のクエリを使用すると、クエリオプティマイザーでクエリ述語列の統計が作成されたかどうかを判断できます。このクエリでは、sys.stats カタログビューと sys.stats_columns カタログビューに対してクエリを実行し、1 列ずつの統計があるすべての列について、データベースオブジェクト名、列名、および統計名を返します。AUTO_CREATE_STATISTICS オプションを使用した結果としてクエリオプティマイザーによって統計が 1 列ずつ作成された場合、その統計名は _WA で始まります。

USE AdventureWorks;
GO
SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s Join sys.stats_columns AS sc
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;
GO

AUTO_CREATE_STATISTICS オプションでは、インデックスに対する統計を作成するかどうかは判断されません。また、フィルター選択された統計情報も生成されません。このオプションは、テーブル全体の 1 列ずつの統計にのみ適用されます。

AUTO_UPDATE_STATISTICS オプションを使用する

統計の自動更新オプション AUTO_UPDATE_STATISTICS がオンの場合、古くなっている可能性がある統計がクエリオプティマイザーによって判断され、それらがクエリで使用されると更新されます。挿入、更新、削除、またはマージの各操作によってテーブルまたはインデックス付きビューのデータの分布が変わると、統計は古くなったと判断されます。クエリオプティマイザーでは、統計が前回更新されてから発生したデータ変更の数をカウントし、その変更の数をしきい値と比較することで、統計が古くなっている可能性がないかを判断します。このしきい値は、テーブルまたはインデックス付きビューの行数に基づいて決められます。

クエリオプティマイザーによる古い統計の確認は、クエリをコンパイルする前と、キャッシュされたクエリプランを実行する前に行われます。クエリをコンパイルする前は、クエリオプティマイザーで、クエリ述語内の列、テーブル、およびインデックス付きビューを使用して古くなっている可能性がある統計が判断されます。キャッシュされたクエリプランを実行する前は、データベースエンジンで、クエリプランが最新の統計を参照しているかどうかが確認されます。

AUTO_UPDATE_STATISTICS オプションは、インデックスに対して作成された統計オブジェクト、クエリ述語内の列に対して 1 列ずつ作成された統計オブジェクト、および CREATE STATISTICS ステートメントを使用して作成された統計に適用されます。また、フィルター選択された統計情報にも適用されます。

AUTO_CREATE_STATISTICS オプションと AUTO_UPDATE_STATISTICS オプションをオンに設定する

統計の自動作成オプション AUTO_CREATE_STATISTICS と統計の自動更新オプション AUTO_UPDATE_STATISTICS は、既定でオンになっています。ほとんどのユーザーデータベースでは、この既定の設定を使用することをお勧めします。次の SELECT ステートメントを使用すると、すべてのユーザーデータベースについてこれらのオプションの現在の値を表示できます。

SELECT name AS "Name", 
    is_auto_create_stats_on AS "Auto Create Stats",
    is_auto_update_stats_on AS "Auto Update Stats",
    is_read_only AS "Read Only" 
FROM sys.databases
WHERE database_ID > 4;
GO

次の例では、AdventureWorks データベースの AUTO_CREATE_STATISTICS と AUTO_UPDATE_STATISTICS を ON に設定します。

USE AdventureWorks;
GO
ALTER DATABASE AdventureWorks
    SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE AdventureWorks
    SET AUTO_UPDATE_STATISTICS ON;
GO

これらの統計オプションの設定方法の詳細については、「ALTER DATABASE の SET オプション (Transact-SQL)」を参照してください。

一部の統計の AUTO_UPDATE_STATISTICS を無効化および再有効化する

AUTO_UPDATE_STATISTICS がオンの場合、アプリケーションの要件に応じて、データベース全体に対する統計の更新動作をオーバーライドし、テーブル、インデックス、または列に対する統計の自動更新を個別にオフに設定することができます。AUTO_UPDATE_STATISTICS がオンの場合に、テーブル、インデックス、または列に対する統計の自動更新を無効化および再有効化する方法は次のとおりです。

sp_autostats システムストアドプロシージャを使用する。この方法では、テーブルまたはインデックスに対する統計の更新を無効化または再有効化することができます。
UPDATE STATISTICS ステートメントで NORECOMPUTE オプションを指定する。統計の更新を再有効化するには、NORECOMPUTE オプションを指定せずに UPDATE STATISTICS をもう一度実行します。
CREATE STATISTICS ステートメントで NORECOMPUTE オプションを指定する。統計の更新を再有効化するには、DROP STATISTICS で統計を削除してから、NORECOMPUTE オプションを指定せずに CREATE STATISTICS を実行します。
CREATE INDEX ステートメントで STATISTICS_NORECOMPUTE オプションを指定する。統計の更新を再有効化するには、STATISTICS_NORECOMPUTE を OFF に設定して ALTER INDEX を実行します。

AUTO_UPDATE_STATISTICS がオフの場合は、テーブル、インデックス、または列に対する自動更新を個別にオンに設定することはできません。統計の自動更新を再有効化すると、AUTO_UPDATE_STATISTICS オプションで指定された元の動作に戻ります。AUTO_UPDATE_STATISTICS オプションがオフの場合、統計は更新されません。

統計の同期更新を使用する場合と非同期更新を使用する場合

統計の更新には、同期更新 (既定) と非同期更新があります。統計の同期更新では、クエリは常に最新の統計を使用してコンパイルおよび実行されます。統計が古い場合、クエリオプティマイザーでは、統計が更新されるまでクエリのコンパイルおよび実行を待機します。統計の非同期更新では、クエリは、既存の統計が古い場合でもその統計を使用してコンパイルされます。クエリのコンパイル時に古い統計が使用された場合、クエリオプティマイザーで最適なクエリプランが選択されない可能性があります。非同期更新の完了後にコンパイルされるクエリには、更新された統計を使用できます。

データベース全体に対する統計の非同期更新オプション AUTO_UPDATE_STATISTICS_ASYNC によって、クエリオプティマイザーで統計の同期更新と非同期更新のどちらを使用するかが決まります。既定では、統計の非同期更新オプションはオフであり、クエリオプティマイザーによる統計の更新は同期更新になります。AUTO_UPDATE_STATISTICS_ASYNC オプションは、インデックスに対して作成された統計オブジェクト、クエリ述語内の列に対して 1 列ずつ作成された統計オブジェクト、および CREATE STATISTICS ステートメントを使用して作成された統計に適用されます。

次のコマンドを使用すると、すべてのデータベースの非同期自動更新オプションを表示できます。

SELECT name AS "Name", 
    is_auto_update_stats_async_on AS "Asynchronous Update" 
FROM sys.databases;
GO

次のような場合は、同期統計を使用することを検討してください。

テーブルの切り捨てや大部分の行の一括更新を行うなど、データの分布が変わる操作を実行する場合。操作が完了した後に統計を更新していない場合、同期統計を使用すれば、変更されたデータに対するクエリを実行する前に統計が最新になります。

次のような場合は、非同期統計を使用してクエリの応答時間を予測しやすくすることを検討してください。

アプリケーションで同じクエリ、類似のクエリ、またはキャッシュされた類似のクエリプランを頻繁に実行する場合。クエリの応答時間は、統計の同期更新を使用するよりも非同期更新を使用した方が予測しやすくなります。非同期更新の場合、クエリオプティマイザーでは、統計が最新になるまで待機せずに着信クエリを実行できるためです。これにより、一部のクエリの遅延については回避することができます。類似のクエリを見つける方法の詳細については、「クエリおよびクエリプランハッシュを使用した類似クエリの検索およびチューニング」を参照してください。
アプリケーションで統計の更新を待機している 1 つ以上のクエリによって、クライアント要求がタイムアウトする場合。場合によっては、同期統計を待機していることが原因で、厳しいタイムアウト時間が設定されたアプリケーションが失敗することがあります。

統計を作成する場合の判断

クエリオプティマイザーによって、既に次のようにして統計が作成されています。

インデックスの作成時に、クエリオプティマイザーによってテーブルまたはビューのインデックスに対する統計が作成されます。これらの統計は、インデックスのキー列について作成されます。インデックスがフィルター選択されたインデックスの場合は、フィルター選択されたインデックスに指定された行のサブセットと同じ行のサブセットについて、フィルター選択された統計が作成されます。フィルター選択されたインデックスの詳細については、「フィルタ選択されたインデックスのデザインガイドライン」および「CREATE INDEX (Transact-SQL)」を参照してください。
AUTO_CREATE_STATISTICS がオンの場合、クエリオプティマイザーによってクエリ述語内の列に対して 1 列ずつ統計が作成されます。

ほとんどのクエリでは、これらの 2 つの方法で作成された統計を使用すれば、高品質のクエリプランになります。ただし、CREATE STATISTICS ステートメントを使用して追加の統計を作成することで、クエリプランが向上する場合もあります。これらの追加の統計では、クエリオプティマイザーでインデックスまたは 1 列ずつの統計を作成する場合には考慮されない統計的相関関係を取り込むことができます。アプリケーションのテーブルデータには、計算して統計オブジェクトに含めればクエリオプティマイザーでクエリプランを向上させることができる、他の統計的相関関係が含まれている場合があります。たとえば、データ行のサブセットに関するフィルター選択された統計情報や、クエリ述語列の複数列統計を使用することで、クエリプランが向上することがあります。

CREATE STATISTICS ステートメントを使用して統計を作成する場合、AUTO_CREATE_STATISTICS オプションをオンのままにし、クエリ述語列に対する 1 列ずつの統計がクエリオプティマイザーによって通常どおり作成されるようにしておくことをお勧めします。クエリ述語の詳細については、「検索条件 (Transact-SQL)」を参照してください。

次のいずれかに該当する場合は、CREATE STATISTICS ステートメントを使用して統計を作成することを検討してください。

データベースエンジンチューニングアドバイザーで統計を作成するように提示される。
相関関係にある複数の列がクエリ述語に含まれているが、それらがまだ同じインデックスに存在しない。
データのサブセットから選択するクエリを使用する。
クエリに統計がない。

データベースエンジンチューニングアドバイザーで統計を作成するように提示される

データベースエンジンチューニングアドバイザーは、1 つ以上のデータベースのワークロードがパフォーマンスに与える影響を分析するツールです。パフォーマンスを向上させるための推奨事項 (インデックスの作成など) が示され、CREATE STATISTICS を使用してクエリの最適化に関する統計を作成するように提示されることもあります。この推奨事項に従ってください。データベースエンジンチューニングアドバイザーの詳細については、「物理データベースデザインのチューニング」を参照してください。

相関関係にある複数の列がクエリ述語に含まれている

列間に相関関係や依存関係がある複数の列がクエリ述語に含まれている場合、複数列の統計を使用するとクエリプランが向上することがあります。複数列の統計には、密度と呼ばれる列間の相関関係の統計が含まれます。これは、1 列ずつの統計では使用できません。複数の列間のデータの相関関係によってクエリ結果が異なる場合、密度を使用すると基数の推定が向上します。

列が同じインデックスに既に存在する場合、複数列統計オブジェクトは既に存在するため、手動で作成する必要はありません。列が同じインデックスにまだ存在しない場合は、列のインデックスを作成するか CREATE STATISTICS ステートメントを使用することによって、複数列統計を作成できます。メンテナンスに必要なシステムリソースは、インデックスの方が統計オブジェクトよりも多くなります。複数列のインデックスを必要としないアプリケーションの場合は、インデックスを作成せずに統計オブジェクトを作成すると、システムリソースを節約できます。

複数列統計を作成する場合、統計オブジェクト定義内の列の順序によって、基数の推定に密度を使用した場合の効果が変わります。統計オブジェクトには、統計オブジェクト定義内のキー列の各プレフィックスの密度が格納されます。密度の詳細については、「DBCC SHOW_STATISTICS (Transact-SQL)」を参照してください。

基数の推定に効果的な密度を作成するには、クエリ述語内の列が、統計オブジェクト定義内の列のいずれかのプレフィックスに一致する必要があります。たとえば、次の例では、列 LastName、MiddleName、および FirstName に対する複数列の統計オブジェクトを作成しています。

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.stats
    WHERE name = 'LastFirst'
    AND object_ID = OBJECT_ID ('Person.Contact'))
DROP STATISTICS Person.Contact.LastFirst;
GO
CREATE STATISTICS LastFirst ON Person.Contact (LastName, MiddleName, FirstName);
GO

この例では、統計オブジェクト LastFirst に、列プレフィックス (LastName)、(LastName, MiddleName)、および (LastName, MiddleName, FirstName) の密度が格納されています。(LastName, FirstName) の密度は使用できません。MiddleName を使用せずに LastName と FirstName を使用したクエリの場合は、基数の推定に密度を使用することはできません。

データのサブセットから選択するクエリを使用する

クエリオプティマイザーでは、1 列ずつおよびインデックスに対して統計を作成する際、すべての行の値に対する統計を作成します。行のサブセットから選択するクエリの場合、その行のサブセットのデータ分布が一意であれば、フィルター選択された統計情報を使用することでクエリプランを向上させることができます。フィルター選択された統計情報は、CREATE STATISTICS ステートメントを WHERE 句と共に使用してフィルター述語式を定義することで作成できます。

たとえば、AdventureWorks を使用する場合、Production.Product テーブルの各製品は、Production.ProductCategory テーブルの 4 つのカテゴリ (Bikes、Components、Clothing、および Accessories) のいずれかに属しています。各カテゴリでは、重量に関するデータ分布が異なります。自転車の重量は 13.77 ～ 30.0、部品の重量は 2.12 ～ 1050.00 (一部 NULL 値)、衣類の重量はすべて NULL、付属品の重量も NULL です。

たとえば Bikes の場合、自転車のすべての重量についてのフィルター選択された統計情報を使用すると、テーブル全体の統計情報を使用する場合や、Weight 列の統計情報が存在しない場合と比べて、より正確な統計情報がクエリオプティマイザーに提供され、クエリプランの品質が向上します。自転車の重量の列は、フィルター選択された統計情報には適していますが、重量の参照が比較的少ない場合、フィルター選択されたインデックスには必ずしも適しているとは限りません。フィルター選択されたインデックスを使用することで得られる参照のパフォーマンスの向上よりも、フィルター選択されたインデックスをデータベースに追加するためのメンテナンスコストとストレージコストの増加の方が大きい場合があります。

次のステートメントでは、Bikes のすべてのサブカテゴリについてのフィルター選択された統計 BikeWeights を作成します。フィルター選択された述語式で、比較 Production.ProductSubcategoryID IN (1,2,3) を使用して自転車のすべてのサブカテゴリを列挙することで、自転車を定義しています。Bikes カテゴリは Production.ProductCategory テーブルに格納されているため、述語でこのカテゴリ名を使用することはできません。また、フィルター式に含まれるすべての列が、同じテーブル内に存在する必要があります。

USE AdventureWorks;
GO
IF EXISTS ( SELECT name FROM sys.stats
    WHERE name = 'BikeWeights'
    AND object_ID = OBJECT_ID ('Production.Product'))
DROP STATISTICS Production.Product.BikeWeights;
GO
CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3);
GO

クエリオプティマイザーでは、BikeWeights というフィルター選択された統計情報を使用して、重量が 25 を超えるすべての自転車を選択する次のクエリのクエリプランを向上させることができます。

SELECT P.Weight AS Weight, S.Name AS BikeName
FROM Production.Product AS P
    JOIN Production.ProductSubcategory AS S 
    ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductSubcategoryID IN (1,2,3) AND P.Weight > 25
ORDER BY P.Weight;
GO

クエリに統計がない

クエリオプティマイザーでは、エラーやその他のイベントによって統計を作成できない場合、統計を使用せずにクエリプランを作成します。クエリオプティマイザーでは存在しない統計をマークし、次回のクエリの実行時に再生成しようとします。

統計が存在しない場合は、SQL Server Management Studio を使用してクエリの実行プランをグラフィカルに表示すると、警告 (赤色のテーブル名) が表示されます。詳細については、「グラフィカル実行プランの表示 (SQL Server Management Studio)」を参照してください。また、SQL Server Profiler を使用して Missing Column Statistics イベントクラスを監視すると、統計がない場合はそのことがわかります。詳細については、「Errors and Warnings イベントカテゴリ (データベースエンジン)」を参照してください。

統計がない場合は、次の手順を実行します。

AUTO_CREATE_STATISTICS と AUTO_UPDATE_STATISTICS がオンになっていることを確認します。
データベースが読み取り専用ではないことを確認します。データベースが読み取り専用の場合、クエリオプティマイザーでは統計を保存できません。
存在しない統計を CREATE STATISTICS ステートメントを使用して作成します。

統計を更新する場合の判断

クエリオプティマイザーでは、古くなっている可能性がある統計を判断し、それらがクエリプランに必要な場合は更新します。場合によっては、AUTO_UPDATE_STATISTICS をオンにした場合より頻繁に統計を更新することで、クエリプランが向上し、クエリのパフォーマンスが向上することがあります。統計は、UPDATE STATISTICS ステートメントまたは sp_updatestats ストアドプロシージャを使用して更新できます。

統計を更新すると、クエリが最新の統計を使用してコンパイルされるようになります。ただし、統計の更新によりクエリが再コンパイルされます。パフォーマンスの向上を目的とする場合、クエリプランの改善とクエリの再コンパイルに要する時間の間にはトレードオフの関係があるため、あまり頻繁に統計を更新しないようにすることをお勧めします。実際のトレードオフはアプリケーションによって異なります。

UPDATE STATISTICS または sp_updatestats を使用して統計を更新する場合は、クエリオプティマイザーによって統計の定期的な更新が引き続き実行されるように、AUTO_UPDATE_STATISTICS オプションを ON に設定したままにしておくことをお勧めします。列、インデックス、テーブル、またはインデックス付きビューの統計を更新する方法の詳細については、「UPDATE STATISTICS (Transact-SQL)」を参照してください。データベース内にあるすべてのユーザー定義テーブルと内部テーブルの統計を更新する方法については、ストアドプロシージャ sp_updatestats (Transact-SQL) の説明を参照してください。たとえば次のコマンドは、sp_updatestats を呼び出してデータベースのすべての統計を更新します。

EXEC sp_updatestats

統計の最終更新日を調べるには、STATS_DATE 関数を使用します。

次のような場合は、統計を更新することを検討してください。

クエリの実行に時間がかかる。
昇順または降順のキー列に対して挿入操作を実行する。
メンテナンス操作の実行後。

クエリの実行に時間がかかる

クエリの応答時間が遅い場合や予測できない場合は、他のトラブルシューティング手順を実行する前に、クエリの統計が最新のものであることを確認してください。実行速度の遅いクエリに対するトラブルシューティングの詳細については、「実行速度の遅いクエリを分析するためのチェックリスト」を参照してください。

昇順または降順のキー列に対して挿入操作を実行する

昇順または降順のキー列 (IDENTITY 列や実時間のタイムスタンプ列など) の統計では、クエリオプティマイザーで実行されるよりも頻繁に統計の更新が必要になる場合があります。挿入操作によって昇順または降順の列に新しい値が追加された場合に、追加された行数が少なすぎると、統計の更新が実行されないことがあります。統計が最新ではない場合に、追加された最新の行から選択するクエリを実行すると、現在の統計にそれらの新しい値の基数の推定が含まれません。その結果、基数の推定が不正確になり、クエリのパフォーマンスが低下することがあります。

たとえば、最新の販売注文日から選択するクエリで、統計が最新の販売注文日の基数の推定を含むように更新されていないと、基数の推定が不正確になります。

メンテナンス操作の実行後

テーブルの切り捨てや大部分の行の一括挿入を行うなど、データの分布が変わるメンテナンス操作を実行した後は、統計を更新することを検討してください。これにより、統計の自動更新を待つことによってクエリ処理で発生する以降の遅延を回避することができます。

インデックスの再構築、デフラグ、再構成などの操作では、データの分布は変わりません。そのため、ALTER INDEX REBUILD、DBCC REINDEX、DBCC INDEXDEFRAG、または ALTER INDEX REORGANIZE の各操作を実行した後に統計を更新する必要はありません。ALTER INDEX REBUILD または DBCC DBREINDEX を使用してテーブルまたはビューのインデックスを再構築した場合、クエリオプティマイザーによって統計が更新されますが、この統計の更新はインデックスを再作成する過程で実行されるものです。DBCC INDEXDEFRAG 操作または ALTER INDEX REORGANIZE 操作の後は、クエリオプティマイザーで統計は更新されません。

統計を効果的に使用したクエリのデザイン

クエリ述語にローカル変数や複雑な式が含まれている場合など、特定のクエリ実装では、最適なクエリプランにならないことがあります。クエリのデザインガイドラインに従って統計を効果的に使用することで、この問題を回避できる場合があります。クエリ述語の詳細については、「検索条件 (Transact-SQL)」を参照してください。

クエリのデザインガイドラインを適用して統計を効果的に使用することで、クエリ述語で使用される式、変数、および関数に対する基数の推定を向上させると、クエリプランを向上させることができます。クエリオプティマイザーでは、式、変数、または関数の値が不明な場合、ヒストグラムで参照する値を特定できないため、ヒストグラムから最適な基数の推定を得ることができません。その場合、クエリオプティマイザーでは、ヒストグラム内のサンプリングされたすべての行の値ごとの平均行数に基づいて基数の推定を行います。その結果、基数が適切に推定されず、クエリのパフォーマンスが低下することがあります。

以下のガイドラインでは、基数の推定を向上させることによってクエリプランを改善するためのクエリの作成方法について説明します。

式に対する基数の推定を向上させる

式に対する基数の推定を向上させるには、次のガイドラインに従います。

定数を含む式は可能な限り単純にします。クエリオプティマイザーでは、基数の推定を判断する前に、定数を含むすべての関数および式の評価は行われません。たとえば、式 ABS(-100) は単純に 100 にします。
式で複数の変数を使用している場合は、式の計算列を作成し、その計算列に対する統計またはインデックスを作成することを検討します。たとえば、クエリ述語 WHERE PRICE + Tax > 100 の基数の推定は、式 Price + Tax に対する計算列を作成すると向上する可能性があります。

変数および関数に対する基数の推定を向上させる

変数および関数に対する基数の推定を向上させるには、次のガイドラインに従います。

クエリ述語でローカル変数を使用している場合は、ローカル変数の代わりにパラメーターを使用してクエリを書き換えることを検討します。ローカル変数の値は、クエリオプティマイザーでのクエリ実行プランの作成時には認識されません。クエリでパラメーターを使用すると、クエリオプティマイザーで、ストアドプロシージャに渡される最初の実際のパラメーター値に対する基数の推定が使用されます。
複数ステートメントのテーブル値関数の結果を格納する場合は、標準のテーブルか一時テーブルを使用することを検討します。クエリオプティマイザーでは、複数ステートメントのテーブル値関数の統計は作成されません。この方法を使用すると、クエリオプティマイザーでテーブル列の統計を作成できるため、それを使用することでクエリプランを向上させることができます。複数ステートメントのテーブル値関数の詳細については、「関数の種類」を参照してください。
テーブル変数の代わりに標準のテーブルか一時テーブルを使用することを検討します。クエリオプティマイザーでは、テーブル変数の統計は作成されません。この方法を使用すると、クエリオプティマイザーでテーブル列の統計を作成できるため、それを使用することでクエリプランを向上させることができます。一時テーブルとテーブル変数のどちらを使用するかの判断には、トレードオフの関係があります。ストアドプロシージャでテーブル変数を使用すると、ストアドプロシージャの再コンパイルの回数が、一時テーブルを使用した場合よりも少なくなります。アプリケーションによっては、テーブル変数の代わりに一時テーブルを使用しても、パフォーマンスが向上しない場合もあります。
渡されたパラメーターを使用するクエリがストアドプロシージャに含まれている場合は、パラメーター値がクエリで使用される前にストアドプロシージャ内で変更されないようにします。クエリに対する基数の推定は、更新された値ではなく渡されたパラメーターの値に基づいて行われます。パラメーター値が変更されないようにするには、2 つのストアドプロシージャを使用するようにクエリを書き換えます。

たとえば、次のストアドプロシージャ Sales.GetRecentSales では、パラメーター @date is NULL の場合に @date の値を変更します。
```
USE AdventureWorks;
GO
IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetRecentSales;
GO
CREATE PROCEDURE Sales.GetRecentSales (@date datetime)
AS BEGIN
    IF @date is NULL
        SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
    SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
    WHERE h.SalesOrderID = d.SalesOrderID
    AND h.OrderDate > @date
END
GO
```
ストアドプロシージャ Sales.GetRecentSales の最初の呼び出しで @date パラメーターに NULL が渡された場合、クエリオプティマイザーでは、クエリ述語が @date = NULL で呼び出されていなくても、@date = NULL に対する基数の推定を使用してストアドプロシージャをコンパイルします。この基数の推定は、実際のクエリ結果の行数と大きく異なる場合があります。そのため、クエリオプティマイザーにより、最適なクエリプランが選択されないことがあります。この問題を回避するには、ストアドプロシージャを次のように 2 つのプロシージャに書き換えます。
```
USE AdventureWorks;
GO
IF OBJECT_ID ( 'Sales.GetNullRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetNullRecentSales;
GO
CREATE PROCEDURE Sales.GetNullRecentSales (@date datetime)
AS BEGIN
    IF @date is NULL
        SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
    EXEC Sales.GetNonNullRecentSales @date;
END
GO
IF OBJECT_ID ( 'Sales.GetNonNullRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetNonNullRecentSales;
GO
CREATE PROCEDURE Sales.GetNonNullRecentSales (@date datetime)
AS BEGIN
    SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
    WHERE h.SalesOrderID = d.SalesOrderID
    AND h.OrderDate > @date
END
GO
```

クエリヒントを使用して基数の推定を向上させる

ローカル変数に対する基数の推定を向上させるために、RECOMPILE を指定して OPTIMIZE FOR または OPTIMIZE FOR UNKNOWN クエリヒントを使用することができます。詳細については、「クエリヒント (Transact-SQL)」を参照してください。

アプリケーションによっては、クエリを実行するたびに再コンパイルすると時間がかかりすぎる場合がありますが、OPTIMIZER FOR クエリヒントは RECOMPILE オプションを使用しなくても役立つことがあります。たとえば、Sales.GetRecentSales ストアドプロシージャに OPTIMIZER FOR オプションを追加して、特定の日付を指定することができます。次の例では、Sales.GetRecentSales プロシージャに OPTIMIZE FOR オプションを追加しています。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetRecentSales;
GO
CREATE PROCEDURE Sales.GetRecentSales (@date datetime)
AS BEGIN
    IF @date is NULL
        SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
    SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
    WHERE h.SalesOrderID = d.SalesOrderID
    AND h.OrderDate > @date
    OPTION ( OPTIMIZE FOR ( @date = '2004-05-01 00:00:00.000'))
END;
GO

プランガイドを使用して基数の推定を向上させる

アプリケーションによっては、クエリを変更できない場合や、RECOMPILE クエリヒントを使用すると再コンパイルが多くなりすぎる場合など、クエリのデザインガイドラインを適用できないことがあります。プランガイドを使用すると、アプリケーションベンダーによるアプリケーションの違いを確認しながら、その他のヒント (USE PLAN など) を指定してクエリの動作を制御することができます。プランガイドの詳細については、「プランガイドを使用した配置済みアプリケーションのクエリの最適化」を参照してください。

次の方法で共有

クエリのパフォーマンスを向上させるための統計の使用

クエリの最適化に関する統計とは

フィルター選択された統計情報

データベース全体の統計オプションの使用

AUTO_CREATE_STATISTICS オプションを使用する

AUTO_UPDATE_STATISTICS オプションを使用する

AUTO_CREATE_STATISTICS オプションと AUTO_UPDATE_STATISTICS オプションをオンに設定する

一部の統計の AUTO_UPDATE_STATISTICS を無効化および再有効化する

統計の同期更新を使用する場合と非同期更新を使用する場合

統計を作成する場合の判断

データベースエンジンチューニングアドバイザーで統計を作成するように提示される

相関関係にある複数の列がクエリ述語に含まれている

データのサブセットから選択するクエリを使用する

クエリに統計がない

統計を更新する場合の判断

クエリの実行に時間がかかる

昇順または降順のキー列に対して挿入操作を実行する

メンテナンス操作の実行後

統計を効果的に使用したクエリのデザイン

式に対する基数の推定を向上させる

変数および関数に対する基数の推定を向上させる

クエリヒントを使用して基数の推定を向上させる

プランガイドを使用して基数の推定を向上させる

関連項目

参照

概念

その他の技術情報

その他のリソース

次の方法で共有

クエリのパフォーマンスを向上させるための統計の使用

クエリの最適化に関する統計とは

フィルター選択された統計情報

データベース全体の統計オプションの使用

AUTO_CREATE_STATISTICS オプションを使用する

AUTO_UPDATE_STATISTICS オプションを使用する

AUTO_CREATE_STATISTICS オプションと AUTO_UPDATE_STATISTICS オプションをオンに設定する

一部の統計の AUTO_UPDATE_STATISTICS を無効化および再有効化する

統計の同期更新を使用する場合と非同期更新を使用する場合

統計を作成する場合の判断

データベース エンジン チューニング アドバイザーで統計を作成するように提示される

相関関係にある複数の列がクエリ述語に含まれている

データのサブセットから選択するクエリを使用する

クエリに統計がない

統計を更新する場合の判断

クエリの実行に時間がかかる

昇順または降順のキー列に対して挿入操作を実行する

メンテナンス操作の実行後

統計を効果的に使用したクエリのデザイン

式に対する基数の推定を向上させる

変数および関数に対する基数の推定を向上させる

クエリ ヒントを使用して基数の推定を向上させる

プラン ガイドを使用して基数の推定を向上させる

関連項目

参照

概念

その他の技術情報

その他のリソース

データベースエンジンチューニングアドバイザーで統計を作成するように提示される

クエリヒントを使用して基数の推定を向上させる

プランガイドを使用して基数の推定を向上させる