CREATE STATISTICS (Transact-SQL)
適用対象: SQL ServerAzure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Microsoft Fabric のツールハウス内の SQL 分析エンドポイント
テーブル、インデックス付きビュー、または、外部テーブルの 1 つまたは複数の列に関するクエリ最適化の統計を作成します。 ほとんどのクエリでは、クエリ オプティマイザーによって、高品質のクエリ プランに必要な統計が既に生成されています。場合によっては、 CREATE STATISTICS
を使用して追加の統計を作成したり、クエリのパフォーマンスを向上させるためにクエリの設計を変更したりする必要があります。
詳しくは、「統計」をご覧ください。
Note
Microsoft Fabric の統計情報の詳細については、「 Fabric データ ウェアハウスの統計」を参照してください。
構文
SQL Server、Azure SQL Database、および Azure SQL Managed Instance の構文。
-- Create statistics on an external table
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ , ...n ] )
[ WITH FULLSCAN ] ;
-- Create statistics on a regular table or indexed view
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ , ...n ] )
[ WHERE <filter_predicate> ]
[ WITH
[ FULLSCAN
[ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
| SAMPLE number { PERCENT | ROWS }
[ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
| <update_stats_stream_option> [ , ...n ]
[ [ , ] NORECOMPUTE ]
[ [ , ] INCREMENTAL = { ON | OFF } ]
[ [ , ] MAXDOP = max_degree_of_parallelism ]
[ [ , ] AUTO_DROP = { ON | OFF } ]
]
];
<filter_predicate> ::=
<conjunct> [ AND <conjunct> ]
<conjunct> ::=
<disjunct> | <comparison>
<disjunct> ::=
column_name IN (constant , ...)
<comparison> ::=
column_name <comparison_op> constant
<comparison_op> ::=
IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<
<update_stats_stream_option> ::=
[ STATS_STREAM = stats_stream ]
[ ROWCOUNT = numeric_constant ]
[ PAGECOUNT = numeric_contant ]
Azure Synapse Analytics および Analytics Platform System (PDW) の構文。
CREATE STATISTICS statistics_name
ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
( column_name [ , ...n ] )
[ WHERE <filter_predicate> ]
[ WITH {
FULLSCAN
| SAMPLE number PERCENT
}
]
[ ; ]
<filter_predicate> ::=
<conjunct> [ AND <conjunct> ]
<conjunct> ::=
<disjunct> | <comparison>
<disjunct> ::=
column_name IN (constant , ...)
<comparison> ::=
column_name <comparison_op> constant
<comparison_op> ::=
IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<
Microsoft Fabric の構文。
CREATE STATISTICS statistics_name
ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
( column_name )
[ WITH {
FULLSCAN
| SAMPLE number PERCENT
}
]
[ ; ]
Note
SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。
引数
statistics_name
作成する統計の名前。
table_or_indexed_view_name
統計を作成するテーブル、インデックス付きビュー、または外部テーブルの名前。 別のデータベースの統計を作成するには、修飾テーブル名を指定します。
column [ ,...n]
統計に含める 1 つまたは複数の列。 列は、左から右への優先順位にする必要があります。 ヒストグラムの作成には最初の列のみが使用されます。 すべての列は、密度と呼ばれる列間の相関統計に使用されます。
インデックス キー列として指定できる任意の列を指定できますが、次の例外があります。
xml、フルテキスト、FILESTREAM の列は指定できません。
計算列は、
ARITHABORT
とQUOTED_IDENTIFIER
データベースの設定がON
されている場合にのみ指定できます。CLR ユーザー定義型の列は、データ型でバイナリ順がサポートされている場合に指定できます。 ユーザー定義型列のメソッド呼び出しとして定義されている計算列は、メソッドが決定的とマークされている場合に指定できます。
WHERE <filter_predicate>
統計オブジェクトを作成するときに含める行のサブセットを選択するための式を指定します。 フィルター述語を使用して作成された統計は、フィルター選択された統計情報と呼ばれます。 フィルター述語は単純な比較ロジックを使用し、計算列、UDT 列、空間データ型列、または hierarchyID データ型列を参照することはできません。 NULL
リテラルを使用した比較は、比較演算子では使用できません。 代わりに、IS NULL
および IS NOT NULL
演算子を使用します。
次に、Production.BillOfMaterials
テーブルのフィルター述語の例をいくつか示します。
WHERE StartDate > '20000101' AND EndDate <= '20000630'
WHERE ComponentID IN (533, 324, 753)
WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL
フィルター述語の詳細については、「 フィルター選択されたインデックスの作成」を参照してください。
FULLSCAN
適用対象: SQL Server 2016 (13.x) SP 1 CU 4、SQL Server 2017 (14.x) CU 1 以降のバージョン
すべての行をスキャンして統計を計算します。 FULLSCAN
と SAMPLE 100 PERCENT
は同じ結果になります。 FULLSCAN
SAMPLE
オプションでは使用できません。
省略すると、SQL Server ではサンプリングを使用して統計が作成され、高品質のクエリ プランを作成するために必要なサンプル サイズが決定されます。
Microsoft Fabric の Warehouse では、単一列の FULLSCAN
と単一列の SAMPLE
ベースの統計のみがサポートされています。 オプションが含まれていない場合は、 SAMPLE
統計が作成されます。
SAMPLE number { PERCENT | ROWS }
クエリ オプティマイザーが統計を作成するときに使用するテーブルまたはインデックス付きビューのおおよその割合 (行数) を指定します。 PERCENT
の場合、number は 0 ~ 100、ROWS
の場合はnumber 0 から行の合計数を指定できます。 クエリ オプティマイザーによってサンプリングされる行の実際の割合や行数が、指定した割合や行数と一致しない場合もあります。 たとえば、データ ページではすべての行がスキャンされます。
SAMPLE
は、既定のサンプリングに基づいてクエリ プランが最適でない特殊な場合に役立ちます。 ほとんどの場合、クエリ オプティマイザーでは既にサンプリングが使用され、高品質のクエリ プランを作成するために必要な統計的に有意なサンプル サイズが既定で決定されるため、 SAMPLE
を指定する必要はありません。
SAMPLE
は FULLSCAN オプションと共に使用できません。 SAMPLE
またはFULLSCAN
が指定されていない場合、クエリ オプティマイザーはサンプリングされたデータを使用し、既定でサンプル サイズを計算します。
0 PERCENT
や 0 ROWS
を指定することはお勧めしません。 0 PERCENT
または0 ROWS
を指定すると、統計オブジェクトが作成されますが、統計データは含まれません。
Microsoft Fabric の Warehouse では、単一列の FULLSCAN
と単一列の SAMPLE
ベースの統計のみがサポートされています。 オプションが含まれていない場合は、 FULLSCAN
統計が作成されます。
PERSIST_SAMPLE_PERCENT = { ON | OFF }
ON
すると、統計は、サンプリング率を明示的に指定しない後続の更新の作成サンプリング率を保持します。 OFF
すると、サンプリング率が明示的に指定されていない後続の更新では、統計サンプリング率が既定のサンプリングにリセットされます。 既定値は、OFF
です。
Note
テーブルが切り捨てられた場合、切り捨てられた HoBT に基づいて作成されたすべての統計は、既定のサンプリング率を使用するように戻されます。
STATS_STREAM = stats_stream
単に情報を示すためだけに特定されます。 サポートされていません。 将来の互換性は保証されません。
NORECOMPUTE
statistics_nameの統計の自動更新オプション (AUTO_STATISTICS_UPDATE
) を無効にします。 このオプションを指定すると、statistics_name の進行中の更新は最後まで実行され、その後の更新が無効になります。
統計の更新を再度有効にするには、DROP STATISTICS を使用して統計を削除し、NORECOMPUTE
オプションを指定せずにCREATE STATISTICS
を実行します。
警告
統計の自動更新を無効にすると、クエリ オプティマイザーがテーブルを含むクエリの最適な実行プランを選択できなくなる可能性があります。 このオプションは、資格のあるデータベース管理者のみが慎重に使用する必要があります。
AUTO_STATISTICS_UPDATE
オプションの詳細については、
INCREMENTAL = { ON | OFF }
適用対象: SQL Server 2014 (12.x) 以降のバージョン
ON
すると、作成される統計はパーティションごとの統計になります。 OFF
すると、すべてのパーティションの統計が結合されます。 既定値は、OFF
です。
パーティションごとの統計がサポートされていない場合は、エラーが生成されます。 次の種類の統計では、増分統計がサポートされていません。
- ベース テーブルにパーティションで固定されていないインデックスを使用して作成された統計。
- Always On の読み取り可能なセカンダリ データベースに対して作成された統計。
- 読み取り専用のデータベースに対して作成された統計。
- フィルター選択されたインデックスに対して作成された統計。
- ビューに対して作成された統計。
- 内部テーブルに対して作成された統計。
- 空間インデックスまたは XML インデックスを使用して作成された統計。
MAXDOP = max_degree_of_parallelism
適用対象: SQL Server 2016 (13.x) SP 2、SQL Server 2017 (14.x) CU 3 以降のバージョン
統計操作中に、 max 次数の並列処理 構成オプションをオーバーライドします。 詳細については、「 並列処理の最大限度の構成 (サーバー構成オプション)」を参照してください。 並列プランの実行で使用されるプロセッサの数を制限するには、 MAXDOP
を使用します。 最大数は 64 プロセッサです。
max_degree_of_parallelism は次のように指定できます。
1
: 並列プランの生成を抑制します。>1
: 並列インデックス操作で使用されるプロセッサの最大数を、指定した数に制限します。0
(既定値): 現在のシステム ワークロードに基づいて、プロセッサの実際の数以下を使用します。
update_stats_stream_option
単に情報を示すためだけに特定されます。 サポートされていません。 将来の互換性は保証されません。
AUTO_DROP = { ON | OFF }
適用対象: SQL Server 2022 (16.x) 以降のバージョン、および Azure SQL Database、Azure SQL Managed Instance
SQL Server 2022 (16.x) より前では、統計がユーザー データベース上のユーザーまたはサード パーティのツールによって手動で作成される場合、これらの統計オブジェクトは、顧客が望むスキーマ変更をブロックまたは妨害する可能性があります。
SQL Server 2022 (16.x) 以降では、 AUTO_DROP
オプションは、新しく移行されたすべてのデータベースで既定で有効になっています。 AUTO_DROP
プロパティを使用すると、以降のスキーマ変更が統計オブジェクトによってブロック必要に応じて統計が削除されるように、モードで統計オブジェクトを作成できます。 このように、 AUTO_DROP
が有効になっている手動で作成された統計は、自動作成された統計と同様に動作します。
Note
自動作成された統計で Auto_Drop プロパティを設定または設定解除しようとすると、エラーが発生する可能性があります。 自動作成された統計では、常に自動削除が使用されます。 復元時に、一部のバックアップでは、統計オブジェクトが次回更新されるまで (手動または自動で) このプロパティが正しく設定されていない可能性があります。 ただし、自動作成された統計は、自動削除の統計と同様に動作します。 以前のバージョンから SQL Server 2022 (16.x) にデータベースを復元する場合は、データベースで sp_updatestats
を実行し、統計 AUTO_DROP
機能の適切なメタデータを設定することをお勧めします。
詳細については、「AUTO_DROP オプション」を参照してください。
アクセス許可
これらのアクセス許可のいずれかが必要です。
ALTER TABLE
- ユーザーがテーブルの所有者です
- db_ddladmin 固定データベース ロールのメンバーシップ
解説
SQL Server で tempdb
を使用すると、統計を構築する前に、サンプリングされた行を並べ替えることができます。
外部テーブルの統計
外部テーブルの統計を作成する場合は、SQL Server を使用すると、外部テーブルが SQL Server の一時テーブルにインポートされ、統計が作成されます。 サンプルの統計の場合は、サンプリングされた行のみがインポートされます。 大きな外部テーブルがある場合は、フル スキャン オプションではなく、既定のサンプリングを使用する方が高速です。
外部テーブルがデータ型として DELIMITEDTEXT
、CSV
、PARQUET
、または DELTA
を使用している場合、外部テーブルは CREATE STATISTICS
コマンドごとに 1 列の統計のみをサポートします。
条件がフィルター選択された統計情報
適切に定義されたデータのサブセットから選択するクエリでは、フィルター選択された統計情報を使用するとクエリのパフォーマンスを向上させることができます。 フィルター選択された統計情報では、統計情報に含まれるデータのサブセットを選択するために WHERE 句でフィルター述語を使用します。
CREATE STATISTICS を使用する場合
CREATE STATISTICS
を使用する場合の詳細については、「統計」を参照してください。
フィルター選択された統計の依存関係を参照する
sys.sql_expression_dependencies カタログ ビューでは、フィルター選択された統計情報の述語の各列を、参照による依存関係として追跡します。 フィルター選択された統計を作成する前に、テーブル列に対して実行する操作を検討してください。 フィルター選択された統計述語で定義されているテーブル列の定義を削除、名前変更、または変更することはできません。
制限事項
- テーブルの外部では、統計を更新することはできません。 外部テーブルの統計を更新するには、統計を削除して再作成します。
- 統計オブジェクトごとに最大 64 列の一覧を取得できます。
MAXDOP
オプションは、STATS_STREAM
、ROWCOUNT
、およびPAGECOUNT
オプションと互換性がありません。MAXDOP
オプションは、Resource Governor ワークロード グループのMAX_DOP
の設定によって制限されます (使用されている場合)。CREATE
外部テーブルのDROP STATISTICS
は、Azure SQL Database ではサポートされていません。
例
この記事の Transact-SQL コード サンプルは AdventureWorks2022
または AdventureWorksDW2022
サンプル データベースを使用します。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクトのホーム ページからダウンロードできます。
A. CREATE STATISTICS を SAMPLE number PERCENT と共に使用する
次の例では、AdventureWorks2022 データベースの Person
テーブルの BusinessEntityID
列と EmailPromotion
列の 5% のランダムなサンプルを使って、ContactMail1
統計を作成します。
CREATE STATISTICS ContactMail1
ON Person.Person (BusinessEntityID, EmailPromotion)
WITH SAMPLE 5 PERCENT;
B. CREATE STATISTICS を FULLSCAN および NORECOMPUTE と共に使用する
次の例では、NamePurchase
テーブルの BusinessEntityID
列と EmailPromotion
列のすべての行を対象に、Person
統計情報を作成し、統計の自動再計算を無効にします。
CREATE STATISTICS NamePurchase
ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
WITH FULLSCAN, NORECOMPUTE;
C. CREATE STATISTICS を使用してフィルター選択された統計を作成する
次の例では、フィルター選択された統計情報 ContactPromotion1
を作成します。 データベース エンジンによって、データの 50% がサンプリングされた後、EmailPromotion
が 2 に等しい行が選択されます。
CREATE STATISTICS ContactPromotion1
ON Person.Person (BusinessEntityID, LastName, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO
D. 外部テーブルの統計を作成する
列の一覧を指定する以外に、外部テーブルの統計を作成するときに必要な決定事項は、統計を作成する際に行をサンプリングするか、すべての行をスキャンするかという点のみです。 CREATE
外部テーブルの DROP STATISTICS
は、Azure SQL Database ではサポートされていません。
SQL Server によってデータが外部テーブルから一時テーブルにインポートされて統計が作成されるため、フル スキャンのオプションを実行する場合は時間がかかります。 大きなテーブルの場合、通常は既定のサンプリング方法で十分です。
--Create statistics on an external table and use default sampling.
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);
--Create statistics on an external table and scan all the rows
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;
E. CREATE STATISTICS を FULLSCAN および PERSIST_SAMPLE_PERCENT と共に使用する
次の例では、Person
テーブルのBusinessEntityID
列とEmailPromotion
列のすべての行のNamePurchase
統計を作成し、サンプリング率を明示的に指定しない後続のすべての更新に対して 100% のサンプリング率を設定します。
CREATE STATISTICS NamePurchase
ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;
AdventureWorksDW データベースの使用例
F. 2 つの列の統計を作成する
次の例では、DimCustomer
テーブルの CustomerKey
列と EmailAddress
列に基づいて、CustomerStats1
統計を作成します。 統計は、Customer
テーブルの行の統計的に優位なサンプリングに基づいて作成されます。
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);
G. フル スキャンを使用して統計を作成する
次の例では、DimCustomer
テーブルのすべての行のスキャンに基づいて CustomerStatsFullScan
統計を作成します。
CREATE STATISTICS CustomerStatsFullScan
ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;
H. サンプル率を指定して統計を作成する
次の例では、DimCustomer
テーブルの行の 50 パーセントのスキャンに基づいて CustomerStatsSampleScan
統計を作成します。
CREATE STATISTICS CustomerStatsSampleScan
ON DimCustomer (CustomerKey, EmailAddress) WITH SAMPLE 50 PERCENT;
I. AUTO_DROP で CREATE STATISTICS を使用する
自動削除の統計を使用するには、統計の作成または更新の "WITH" 句に、単に次を追加します。
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH AUTO_DROP = ON
既存の統計の自動削除設定を評価するには、sys.stats の auto_drop
列を使用します。
SELECT object_id, [name], auto_drop
FROM sys.stats;