質問
SQL Serverの最新の操作方法に関するさまざまなドキュメントを読みました。各テーブルにはクラスター化インデックスが必要であり、非クラスター化インデックスの束ではないことを明記しています。 ほとんどのソースは、クラスター化インデックスを使用することの管理上の利点に注目しています。 しかし、パフォーマンスやその他のプラスまたはマイナスの側面に影響はありますか？
解決策
各オプションのパフォーマンスをテストするために、1つだけ違いがある2つの同一のテーブルを作成します-1つではクラスター化インデックスに基づいてプライマリキーが作成され、もう1つでは非クラスター化インデックスに基づいてプライマリキーが作成され、データはヒープの形のままになります。 以下は、テストテーブルを作成してデータを入力するためのスクリプトです。
スクリプト1
-- Create table and indexes CREATE TABLE testtable ([col1] [int] NOT NULL PRIMARY KEY CLUSTERED, [col2] [int] NULL, [col3] [int] NULL, [col4] [varchar](50) NULL, [col5] uniqueidentifier); -- Load sample data into table DECLARE @val INT SELECT @val=1 WHILE @val < 5000000 BEGIN INSERT INTO testtable (col1, col2, col3, col4, col5) VALUES (@val,round(rand()*100000,0), round(rand()*100000,0),'TEST' + cast(@val AS VARCHAR), newid()) SELECT @val=@val+1 END GO -- Create sample table and indexes CREATE TABLE testtable2 ([col1] [int] NOT NULL PRIMARY KEY NONCLUSTERED, [col2] [int] NULL, [col3] [int] NULL, [col4] [varchar](50) NULL, [col5] uniqueidentifier); INSERT INTO testtable2 SELECT * FROM testtable;
私がすぐに気づいた最初のことは、ヒープがより多くのスペースを使い果たしたことです テーブルとインデックスは別々の構造です。 クラスタ化インデックスを使用すると、データはインデックスのリーフに格納されるため、使用するスペースが少なくなります。 以下は、データのダウンロードの終了時に各オブジェクトが使用するスペースの量を示す表です。
| テーブル | 索引 | 使用済み（KB） | 予約済み（KB） | 行数 |
|---|---|---|---|---|
| テストテーブル | PK__testtabl__357D0D3E3D086A66 | 257952 | 257992 | 4999999 |
| testtable2 | HEAP | 256992 | 257032 | 4999999 |
| testtable2 | PK__testtabl__357D0D3F2CBA35D8 | 89432 | 89608 | 4999999 |
プライマリデータが読み込まれた後、読み込みの第2段階に進み、SQLプロファイラーを使用してパフォーマンスを確認し、使用されている領域を確認します。 以下は、各テーブルに追加の100,000レコードをロードするスクリプトです。
スクリプト2
-- insert when all pages are full DECLARE @val INT SELECT @val=5000000 WHILE @val < 5100000 BEGIN INSERT INTO testtable (col1, col2, col3, col4, col5) VALUES (@val,round(rand()*100000,0), round(rand()*100000,0),'TEST' + cast(@val AS VARCHAR), newid()) SELECT @val=@val+1 END GO DECLARE @val INT SELECT @val=5000000 WHILE @val < 5100000 BEGIN INSERT INTO testtable2 (col1, col2, col3, col4, col5) VALUES (@val,round(rand()*100000,0), round(rand()*100000,0),'TEST' + cast(@val AS VARCHAR), newid()) SELECT @val=@val+1 END GO
まず、使用済みのスペースを見てみましょう。これは、予想どおり（ページが最後までいっぱいになり、何も削除されなかったため）、オブジェクトごとに比例して増加しました。 両方のテーブルのサイズがほぼ同じに増加しました。
| テーブル | 索引 | 使用済み（KB） | 予約済み（KB） | 行数 |
|---|---|---|---|---|
| テストテーブル | PK__testtabl__357D0D3E3D086A66 | 263128 | 263176 | 5099999 |
| testtable2 | HEAP | 262392 | 262472 | 5099999 |
| testtable2 | PK__testtabl__357D0D3F2CBA35D8 | 91216 | 91272 | 5099999 |
しかし、SQLプロファイラーはもっと興味深いことを示しています。 なぜなら ヒープにデータを挿入する場合、2つのオブジェクトの更新が必要です-非クラスター化インデックスとテーブル自体、これにはCPUからの追加リソースも必要です少し時間がかかります。
| インデックスの種類 | CPU（ミリ秒） | 読み取り操作 | 書き込み操作 | 継続時間（ミリ秒） |
|---|---|---|---|---|
| クラスター化 | 3500 | 304919 | 654 | 11288 |
| 束 | 3890 | 406083 | 904 | 11438 |
次に、一部のデータをランダムに削除し、別の100,000レコードを挿入して、一部のページに空のスペースが存在するとパフォーマンスにどのように影響するかを確認します。 削除と挿入を実行するスクリプトを以下に示します。
スクリプト3
今回、SQLプロファイラーは、クラスター化インデックスを優先して、2つのテーブル間のパフォーマンスギャップの増加を示しています。
-- remove 1000000 random records from each table DELETE FROM testtable WHERE col1 in (SELECT TOP 1000000 col1 FROM testtable ORDER BY newid()); DELETE FROM testtable2 WHERE col1 not in (SELECT col1 FROM testtable); GO -- insert when there is free space in pages DECLARE @val INT SELECT @val=5100000 WHILE @val < 5200000 BEGIN INSERT INTO testtable (col1, col2, col3, col4, col5) VALUES (@val,round(rand()*100000,0), round(rand()*100000,0),'TEST' + cast(@val AS VARCHAR), newid()) SELECT @val=@val+1 END GO DECLARE @val INT SELECT @val=5100000 WHILE @val < 5200000 BEGIN INSERT INTO testtable2 (col1, col2, col3, col4, col5) VALUES (@val,round(rand()*100000,0), round(rand()*100000,0),'TEST' + cast(@val AS VARCHAR), newid()) SELECT @val=@val+1 END GO
今回、SQLプロファイラーは、クラスター化インデックスを優先して、2つのテーブル間のパフォーマンスギャップの増加を示しています。
| インデックスの種類 | CPU（ミリ秒） | 読み取り操作 | 書き込み操作 | 継続時間（ミリ秒） |
|---|---|---|---|---|
| クラスター化 | 3562 | 304859 | 653 | 10334 |
| 束 | 4973 | 422142 | 7053 | 13042 |
ほとんどの場合、この違いは、ヒープにレコードを挿入するときに、DBMSが各ページの空のスペースを探してデータを配置するために発生しました。 これは、ヒープ上のデータが並べ替えられていないためです。つまり、挿入されたレコードはどこにでも配置できます。 クラスター化インデックスが設定されたテーブルの場合、主キーの値をインクリメントすると、挿入は常にテーブルの最後に実行されます。 また、テーブルで使用されるスペースに注意する価値があります。 ヒープの場合、スペースを再利用するとき、テーブルが占めるスペースの量は増加しないはずです。 削除した数だけ挿入しました。 以下は、最後のスクリプトが実行された後に記録されたテーブルに関する情報です。これは、ヒープによって占有されているスペースが変更されていないことを示し、これは以前の仮定を確認します。
| テーブル名 | インデックス名 | 使用済み（KB） | 予約済み（KB） | 行数 |
|---|---|---|---|---|
| テストテーブル | PK__testtabl__357D0D3E3D086A66 | 268304 | 268360 | 4199999 |
| testtable2 | HEAP | 262392 | 262472 | 4199999 |
| testtable2 | PK__testtabl__357D0D3F2CBA35D8 | 93008 | 93064 | 4199999 |
おわりに
この調査では、クラスターインデックスを使用することで、束と比較して、すべての測定カテゴリ（CPU、I / O、およびランタイム）で最良の結果が得られることを確認しました。 唯一の副作用は、クラスター化インデックスを使用するにはもう少しスペースが必要になることです。 このスペースは、インデックスを再構築することで削減できます。 いずれにせよ、稼働中のシステムのインデックスを変更する前に、ハードウェアのすべてをチェックして、パフォーマンスが向上することを確認する必要があります。