この記事は、この機能（およびその他のDBMS設定）を適用するための前提条件と結果を1か所にまとめる試みです。
問題と前提
このトピックについて、システムに関するいくつかの言葉を詳しく説明します。システムは、2000年代に開発が開始された製品です。 現在、システムは積極的に開発中です。 この製品には、複数のアプリケーションサーバーを備えたクライアントノースアーキテクチャがあります。
サーバー側はMicrosoft SQL Serverです。
システムがもはや「初心者」ではないという事実を考えると、データベース構造には対応するメカニズム/オプション/オブジェクトがあり、その使用は現時点では不合理で時代遅れに思えます。 これらのオブジェクトは徐々に拒否されますが、まだ使用されている場合があります。
前提1
次のパフォーマンス監査では、顧客と一緒に、テーブルの1つの急速な成長に注目しました（これをテーブルXと呼びましょう）。 テーブルXのサイズはほぼ350 GBでした（ところで、データベース全体のボリュームは約2 TBです）。 さらに、テーブルとインデックスの実際のデータによる分布は次のとおりでした。
- 約115 GBのデータがありました
- 残りの〜235 GBボリュームにはインデックスが作成されました。
すなわち テーブルのインデックスがデータ自体よりも約2倍大きい場合、状況は非常に異常です。 すなわち 非常に高い間接費が発生し、次のようなマイナスの影響があります。
- このテーブルでのデータの挿入/更新操作の期間（インデックスが多いほど、操作は「より高価」になります）。
- これらのインデックスのメンテナンス（再構築）のためのサービス操作の期間。
- 障害が発生した場合のデータベースのバックアップとリカバリの期間。
- ディスク容量の要件は、ボリュームの観点から増加しています。
前提2
大規模なDBMS操作スキームは、次のように説明できます。すべてのデータは、処理前にディスクからバッファプール（キャッシュ）にロードされます。 これにより、ディスク操作の数が減り、最も頻繁に使用されるデータの処理が高速化されます。 メカニズムの詳細については、たとえば記事をご覧ください 。 バッファープールの使用効率は、ページ寿命予測カウンター（バッファープール内のページの有効期間）を使用して間接的に追跡できます。
数か月の間に、バッファープールのページの有効期間を短縮するマイナスのダイナミクスが明らかになりました。
何もしないと、次の結果になる可能性があります。
- ディスクサブシステムの負荷の大幅な増加。
- ユーザー操作の期間を増やします。
その結果、原因を分析することが決定されました。
これらの月のシステムは静的ではなく、絶えず変更されていたため、バッファプールの内容で分析を開始することにしました。 このために、動的表現のデータsys.dm_os_buffer_descriptorsを使用しました 。
リクエストの例：
Into_BufferPool
SELECT indexes.name AS index_name, objects.name AS object_name, objects.type_desc AS object_type_description, COUNT(*) AS buffer_cache_pages, COUNT(*) * 8 / 1024 AS buffer_cache_used_MB FROM sys.dm_os_buffer_descriptors INNER JOIN sys.allocation_units ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id INNER JOIN sys.partitions ON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3)) OR (allocation_units.container_id = partitions.partition_id AND type IN (2))) INNER JOIN sys.objects ON partitions.object_id = objects.object_id INNER JOIN sys.indexes ON objects.object_id = indexes.object_id AND partitions.index_id = indexes.index_id WHERE allocation_units.type IN (1,2,3) AND objects.is_ms_shipped = 0 AND dm_os_buffer_descriptors.database_id = DB_ID() GROUP BY indexes.name, objects.name, objects.type_desc ORDER BY COUNT(*) DESC;
〜185 GBのバッファプールサイズでは、約80〜90 GBが問題テーブルXのクラスタインデックスデータでした。残りのバッファプールはインデックス間で均等に分散されていました。 その結果、テーブルXのデータを何らかの方法で最適化することで最大の効果が得られることがわかりました（この場合、クラスターインデックスについて説明しています）。
前提3
1つのテーブルに大量のデータを蓄積すると、遅かれ早かれ、このテーブルに関連付けられた操作の少なくとも一部がすべてではないにしても、パフォーマンスに影響を与えることが実践的に示されています。 この表の列の数が多いと、状況は非線形に悪化します。
さらに、データの充足度についてテーブルXを分析したとき、次の図を見ました：ほとんどすべての行について、特定の列のセットのみが満たされました（これにより、システムの柔軟性と特定のビジネス要件への適応が達成されました）。 これは本質的に、データの保存と処理の効率を低下させます。 一部のセルは情報を保存しませんが、それでもこれらのセルのスペースは予約されています（たとえば、データ型intで空の列を追加すると、テーブルを保存するコストが少なくとも[4バイト*テーブルの行数]増加します）。
ソリューション/修正オプション
上記のすべての初期データを考慮に入れて、さらなる研究のために4つの方向が特定されました。
- フィルター選択されたインデックス
- DBMSを使用したデータ圧縮（データ圧縮）;
- スパース列
- テーブルXをいくつかの小さなテーブルに分割します。
フィルターされたインデックス
公式のドキュメントに記載されている内容は次のとおりです。
「フィルター選択されたインデックスは、最適化された非クラスター化インデックスであり、特に明確に定義されたデータのサブセットからフェッチするクエリに適しています...適切に設計されたフィルター選択されたインデックスは、フルテーブルインデックスと比較して、インデックスのメンテナンスパフォーマンスとストレージコストを削減できます 。 」
少し簡単にするために、テーブル内のデータの一部のみにインデックスを作成する可能性について話します。たとえば、特定のビジネスケース用にテーブルXにインデックスを作成できます。
しかし、インデックスを適用するには、データベースの構造も変更されたソフトウェアの新しいバージョンを使用する必要がありました。 特に、新しいバージョンでは、クライアントソフトウェアのDBMSとの接続パラメーターがONモードに変更されました。
- SET ANSI_NULLS ON;
- SET QUOTED_IDENTIFIER ON;
- SET CONCAT_NULL_YIELDS_NULL ON。
しかし、私たちのケースでは、更新は6か月後に計画されていたため、それほど多くの時間を待つことができませんでした。 さらに、フィルター選択されたインデックスを使用することも意図していませんでした。 これにより、たとえば、強制パラメーター化オプションを使用するなど、非効率的になりました。
データ圧縮
クライアントにはDBMSバージョン2012がインストールされているため、このバージョンには2種類のデータ圧縮があります。
- ページレベルの圧縮（ページ圧縮）;
- 行レベルの圧縮
SQL 2016のバージョンを検討する場合、いくつかの変更がありますが、私たちの場合、それらは無関係でした（強力なハードウェアでのSQLの次のバージョンへの切り替えは、財政的な観点から非常に高価です）。 そのため、最初の2つについて詳しく説明しました。
ドキュメントによると、ページレベルの圧縮は、行レベルの圧縮よりもCPUのリソースを集中的に使用する操作です。 これに基づいて、ページレベルの圧縮オプションはすぐに削除されました。
その後、行圧縮を使用する試みがありましたが、ドキュメントでも追加のリソースを消費するという記述がありました。 また、プロセッサは非常に限られたリソースであるため、このオプションも放棄する必要がありました。
スパース列
スパース列は、NULL値用にストレージを最適化した通常の列です。 スパース列は、NULL値を格納するために必要なスペースを削減しますが、非NULL値を取得するコストは増加します。
プラスの効果を得るには、特定の各列に特定の割合のNULL値が必要です。 この割合は、列内のデータのタイプによって異なります。例：
| データ型 | 切れ目のないバイト | スパースバイト | ヌルの割合 |
|---|---|---|---|
| 浮く | 4 | 8 | 64％ |
| 日時 | 8 | 12 | 52％ |
| varchar | 8 | 12 | 52％ |
| int | 2 | 4 | 60％ |
さらに、すべての列が スパースに転送されました。 制限および非互換性のリストは、 公式ドキュメントに記載されています 。
すなわち 大規模にスパースに翻訳する可能性を評価するには、以下を分析する必要がありました。
- 特定の表/列に関するドキュメントからの制限の存在;
- これらの列のNULL値の実際の割合。
要求はこれを行うのに役立ちました。そのテキストは以下のリンクで利用可能です。 大量のクエリ自体には非常に長い時間がかかるため、分析する必要がある特定のテーブルを指定することをお勧めします。
スパース候補
CREATE TABLE #temp ( ColumnName varchar(50), ColumnID int, TableName varchar(50), TableId int, TypeName varchar(50), IsParse bit, IsNullable bit, NumberOfRow bigint, NumberOfRowNULL bigint, Ratio int) SET NOCOUNT ON INSERT into #temp SELECT DISTINCT sys.columns.name ColumnName, sys.columns.column_id ColumnID, OBJECT_NAME(sys.columns.object_id) AS TableName, sys.columns.object_id TableID, CASE systypes.name WHEN 'sysname' THEN 'nvarchar' ELSE systypes.name END AS TypeName, sys.columns.is_sparse IsParse, sys.columns.is_nullable IsNullable, 0,0,0 FROM sys.columns (NoLock) INNER JOIN systypes (NoLock) ON systypes.xtype = sys.columns.system_type_id WHERE sys.columns.object_id = OBJECT_ID('my_table') -- change table name and systypes.name NOT IN ('geography', 'geometry', 'image', 'ntext', 'text', 'timestamp') and sys.columns.is_sparse = 0 and sys.columns.is_nullable = 1 and sys.columns.is_rowguidcol = 0 and sys.columns.is_identity = 0 and sys.columns.is_computed = 0 and sys.columns.is_filestream = 0 and sys.columns.default_object_id = 0 and sys.columns.rule_object_id = 0 and sys.columns.system_type_id=sys.columns.user_type_id delete tps from #temp tps where exists ( select DISTINCT 'Exists' from sys.columns inner join sys.indexes i on i.object_id = tps.TableId inner join sys.index_columns ic on ic.column_id = tps.ColumnID inner join sys.columns c on c.object_id = tps.TableId and ic.column_id = c.column_id where i.type =1 or i.is_primary_key = 1) select count(*) from #temp delete tps from #temp tps inner join sys.partitions p on p.object_id = tps.TableId where p.data_compression<>0; DECLARE @TableName nvarchar(1000) DECLARE @ColumnName nvarchar(1000) DECLARE @vQuery nvarchar(1000) DECLARE @result1 INT DECLARE @result2 INT DECLARE tables_cursor CURSOR FAST_FORWARD FOR SELECT TableName,ColumnName FROM #temp OPEN tables_cursor FETCH NEXT FROM tables_cursor INTO @TableName,@ColumnName WHILE @@FETCH_STATUS = 0 BEGIN -- Search the number of row in a table SET @vQuery = 'SELECT @result1= COUNT(*) FROM [' + @TableName + ']' EXEC SP_EXECUTESQL @Query = @vQuery , @Params = N'@result1 INT OUTPUT' , @result1 = @result1 OUTPUT -- Search the number of row in a table SET @vQuery = 'SELECT @result2= COUNT(*) FROM [' + @TableName + '] where [' + @ColumnName + '] is null' EXEC SP_EXECUTESQL @Query = @vQuery , @Params = N'@result2 INT OUTPUT' , @result2 = @result2 OUTPUT update #temp set NumberOfRow = @result1,NumberOfRowNULL = @result2,Ratio = (@result2*100/@result1) where ColumnName=@ColumnName and TableName=@TableName FETCH NEXT FROM tables_cursor INTO @TableName,@ColumnName END CLOSE tables_cursor DEALLOCATE tables_cursor --delete from #temp where Ratio>10 select * from #temp --drop table #temp
さらに、取得したリストから、条件を最も満たす列（最大NULL値）を決定し、それらをスパースに変更する必要があります。 変更自体は、長いロックが発生しないようにsingle_userデータベースモードで行うのが最適です。 列をスパースに移動した後、インデックスを再構築する必要があります。その後のみ、テーブルのサイズ変更を確認できます。
このメカニズムを使用しても、DBMSサーバーのプロセッサの負荷が増加することはありません（実際と負荷テストの両方でテストされました）。
おそらくこれは、次の写真で簡単に説明できるまれな状況の1つです。
テーブルを小さなものに分割する
この場合、作業はブロックに分割することもできます。
- システムアーキテクチャを変更する。
- 新しいアーキテクチャでのアプリケーション開発全体の修正。
どちらの項目も非常に高価であり、可能な最適化のリストの最後にテーブルパーティションがありました。 これはすべて無期限に引きずられる可能性があります。
ところで、このアイテムの実装は現在必要ありません。 製品全体の開発という観点から見ると、良くも悪くも、時間が経てばわかると思います...
スパース効果
まあ、結論の代わりに、数値でスパース列を使用することの肯定的な側面に注意したいと思います。
- テーブルXのクラスターインデックスのボリュームを約2倍削減しました（スパースに変換されたキーフィールドで非クラスターインデックスを再作成するときのMBのボリュームの削減に対する同様の効果）。
- 請求項1に基づいて、バッファプールの使用効率が向上しました。 バッファープールのテーブルXデータが減少しました
- p.1-2に基づいて、バッファープールのページの有効期間が長くなり（青色の線）、その結果、ディスクの負荷が減少しました（黄色の線）。
- 大量のデータサンプリングに関連する操作の一部の期間を短縮しました。 バッファプールでデータを見つける可能性の増加。
データをテーブルに縮小すると、次のようになります。
| インジケータ | | ご注意 |
|---|---|---|
| テーブルX、GBのクラスター化インデックスのボリューム | | ディスク上（HDD） |
| バッファープール内のテーブルXのクラスター化インデックスのサイズ、GB | | メモリ内（RAM） |
| ページの平均寿命、秒 | | バッファプールのページ存続期間 |
| ディスク転送/秒、iops | | ディスク操作の数。 ストレージの負荷が軽減されました。 |
スパースの制限
それでも、利点に加えて、制限もありました。
- スパース列の定期的な更新が必要です。 しばらくすると、スパース列でのNULL値と非NULL値の分布が変化する可能性があり、スパースの使用は不当になります。
- スパースに変換できる列の数は制限されています。 行の更新中に過剰な場合、エラー576が発生することがあります。
制限のより詳細なリストはここで見つけることができます 。
記事を最後まで読んだ場合は、上記の設定を行う前に、この記事の作成者がデータの損失または損害について責任を負わないことを忘れないでください。 バックアップを作成することを忘れないでください。
おそらく、誰かが既に高負荷のシステムに関する記事で説明されている設定を適用し、どのような結果になったかを登録解除してください。