Azure SQL Database（Hyperscale）でテレメトリのような時系列データを溜め続けると、20 億行くらいはすぐに到達します。最初は速かった「日付範囲＋デバイス ID」での検索が、いつの間にか遅くなり、「パーティションで何とかならないか？」と悩みがちです。本記事では、パーティション分割に飛びつく前にやるべきインデックス設計や列ストア、さらに Azure SQL Database 特有の制約を踏まえたベストプラクティスを、実践的な SQL サンプルとともに整理します。
Azure SQL Database の 20 億行時系列テーブルで何が起きているのか
まず押さえておきたいのは、「行数が多い＝必ずパーティション」というわけではないことです。多くの場合、遅くなっている原因は次のような“基本設計”にあります。
- 日時列（ここでは
[timestamp]）のインデックスはあるが、他の条件列と相性が悪い - 典型クエリとインデックスキーの順序が合っていない
- 非 SARGable（インデックスが効きにくい）な WHERE 句を使っている
- 統計情報が古く、推定行数が大きくズレている
- 行が増え続けてフラグメンテーションが進んでいる
よくあるテレメトリテーブルの例をイメージしてみましょう。
CREATE TABLE dbo.Telemetry
(
id bigint IDENTITY(1,1) NOT NULL,
device_id int NOT NULL,
[timestamp] datetime2(3) NOT NULL,
metric_name nvarchar(100) NOT NULL,
metric_value float NOT NULL,
properties_json nvarchar(max) NULL,
CONSTRAINT PK_Telemetry PRIMARY KEY CLUSTERED (id)
);
-- 非クラスタ化インデックス（よくあるパターン）
CREATE INDEX IX_Telemetry_Timestamp
ON dbo.Telemetry ([timestamp]);
この設計だと、次のようなクエリはそこそこ速くても、行数が増えるほどじわじわ遅くなりがちです。
SELECT *
FROM dbo.Telemetry
WHERE [timestamp] BETWEEN @from AND @to
AND device_id = @deviceId
ORDER BY [timestamp] DESC;
理由はシンプルで、このクエリの実際の絞り込みキーは [timestamp] + device_id なのに、インデックスは [timestamp] だけだからです。さらに SELECT * で多くの列を読み出していれば、Bookmark Lookup（Key Lookup）が大量に発生し、20 億行規模では顕著に効いてきます。
典型クエリとインデックス設計のズレを洗い出す
まずは Query Store や実行プランを使って、「よく発行されるクエリ」と「どうやって実行されているか」を棚卸しします。典型的には、以下のようなパターンが多いはずです。
| クエリパターン | 内容 | 推奨インデックスの方向性 |
|---|---|---|
| 最新データ取得 | 直近 N 分/時間のデータをデバイス単位で取得 | (device_id, [timestamp] DESC) の複合インデックス＋INCLUDE 列 |
| 期間＋デバイス | 特定デバイスの一定期間の履歴を取得 | (device_id, [timestamp]) か ([timestamp], device_id) をクエリに合わせて設計 |
| 期間＋集計 | 期間内の平均・最大・最小などを集計 | 列ストア（NCCI）＋必要に応じて集約テーブル |
| 長期の広範囲分析 | 数か月〜数年単位の分析クエリ | 列ストア＋アーカイブテーブル、ADX/Synapse へのオフロード |
この「よく使うクエリ」に合わせてインデックスを作り直すことが、パーティション設計より先にやるべき最優先タスクです。
パーティションに飛びつく前に：実行プランと SARGability のチェック
パーティションの前に、まずは実行プランを確認します。特に次のポイントをチェックします。
IX_Telemetry_Timestampが Index Seek されているか（Scan になっていないか）- 推定行数（Estimated）と実測行数（Actual）が大きくズレていないか
- パラメータスニッフィング（特定パラメータで固まったプラン）で偏りが出ていないか
- WHERE 句が SARGable（インデックスを有効活用できる）になっているか
非 SARGable な書き方の典型例と書き換え
時系列でよくやってしまうアンチパターンがこちらです。
-- 悪い例：列に関数をかけてしまう
WHERE CONVERT(date, [timestamp]) = @date
この書き方だと、インデックス列に関数がかかっているため、インデックス Seek が効きにくく、多くの場合 Scan になります。正しくは「列をそのまま」にして、範囲条件に書き換えます。
-- 良い例：SARGable に書き換え
WHERE [timestamp] >= @date
AND [timestamp] < @date + 1;
同様に、DATEPART や FORMAT を WHERE 句に使っている場合も要注意です。「列に関数をかけない」「計算はパラメータ側でやる」が、時系列テーブルの鉄則です。
インデックス設計を最適化する：複合・カバリング・フィルタード
時系列テーブルで最初に効くのは、正しく設計された B ツリーインデックスです。特に意識したいのは次の 3 点です。
- 複合インデックス（複数列での検索に合わせる）
- カバリングインデックス（SELECT で使う列を INCLUDE する）
- フィルタードインデックス（「最近のデータだけ」など条件付き）
複合＋カバリングインデックスの具体例
先ほどの典型クエリに合わせるなら、例えば次のようなインデックスが候補になります。
CREATE INDEX IX_Telemetry_Device_Timestamp
ON dbo.Telemetry (device_id, [timestamp] DESC)
INCLUDE (metric_name, metric_value);
このインデックスがあれば、以下のようなクエリはインデックスだけで完結し、テーブルへの Key Lookup が不要になります。
SELECT
device_id,
[timestamp],
metric_name,
metric_value
FROM dbo.Telemetry
WHERE device_id = @deviceId
AND [timestamp] BETWEEN @from AND @to
ORDER BY [timestamp] DESC;
また、直近データへのアクセスが圧倒的に多い場合は、フィルタードインデックスを用意することで I/O を大きく削減できます。
-- 直近 90 日分だけのフィルタードインデックス
CREATE INDEX IX_Telemetry_Recent
ON dbo.Telemetry (device_id, [timestamp] DESC)
INCLUDE (metric_name, metric_value)
WHERE [timestamp] >= DATEADD(day, -90, SYSUTCDATETIME());
フィルタードインデックスは範囲を「動的」にできないという制約があるため、必要に応じて定期的な再作成やメンテナンスジョブでの入れ替えを検討してください（スライディングウィンドウ方式など）。
データ圧縮で I/O とストレージを削減
Azure SQL Database では ROW / PAGE 圧縮を利用できます。読み取り主体のテーブルであれば、圧縮によって I/O が減り、結果としてレスポンスが良くなるケースが多いです。
ALTER INDEX ALL ON dbo.Telemetry
REBUILD WITH (DATA_COMPRESSION = PAGE);
特に数十〜数百バイトの行を 20 億件抱えているようなテーブルでは、圧縮の有無でストレージコストとパフォーマンスの差がはっきり出ます。
| インデックス手法 | 主な目的 | 向いているケース |
|---|---|---|
| 複合インデックス | WHERE / ORDER BY に合わせて探索効率を上げる | 特定の列の組み合わせで頻繁に絞り込むクエリがある |
| カバリングインデックス | Key Lookup をなくし、インデックスだけでクエリを完結させる | 特定のレポートクエリをとにかく速くしたい |
| フィルタードインデックス | ホットデータだけを小さいインデックスで管理 | 「最近 N 日」のアクセスが圧倒的に多い |
| 圧縮（ROW/PAGE） | I/O とストレージの削減 | 読み取り主体、かつ行あたりのサイズが大きめ |
列ストア（Columnstore）で範囲スキャンと集計を高速化
20 億行規模で「期間＋集計」を高速化したい場合、列ストアインデックスは強力な選択肢です。Azure SQL Database では、次の 2 パターンを利用できます。
- 非クラスタ化列ストアインデックス（NCCI）
- クラスタ化列ストアインデックス（CCI）
非クラスタ化列ストア（NCCI）：B ツリーを残したまま分析を高速化
既存のクラスタ化インデックス（B ツリー）を維持したまま、分析クエリだけを速くしたい場合は NCCI が有効です。
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Telemetry
ON dbo.Telemetry
(
device_id,
[timestamp],
metric_name,
metric_value
);
NCCI を追加すると、例えば次のようなクエリが劇的に速くなることがあります。
SELECT
device_id,
DATEADD(minute, DATEDIFF(minute, 0, [timestamp]) / 5 * 5, 0) AS ts_5min,
AVG(metric_value) AS avg_value
FROM dbo.Telemetry
WHERE [timestamp] BETWEEN @from AND @to
GROUP BY
device_id,
DATEADD(minute, DATEDIFF(minute, 0, [timestamp]) / 5 * 5, 0);
注意点として、列ストアはバッチ挿入（INSERT の一括処理）に最適化されているため、「1 行ずつ頻繁に INSERT」がメインだと、デルタストアが増えやすくなります。可能であればバッファリングして 1 回あたり数千行単位で挿入するようにアプリ側を調整すると効果が高まります。
クラスタ化列ストア（CCI）：読み取り特化テーブルとして割り切る
履歴専用・分析専用のテーブルで、個別行の細かな更新がほとんどないなら、クラスタ化列ストア（CCI）を検討します。CCI を設定すると、従来の B ツリークラスタ化インデックスは置き換わる点に注意してください。
CREATE CLUSTERED COLUMNSTORE INDEX CCI_TelemetryArchive
ON dbo.TelemetryArchive;
よくあるパターンは、次のような「ホット／コールド分割」です。
- Telemetry_Hot：直近 30〜90 日分、クラスタ化 B ツリー＋必要な NCCI
- Telemetry_Archive：それより古いデータ、CCI で読み取りに特化
AP 系（分析・レポート系）は Archive を主に参照し、オンラインなダッシュボードは Hot テーブルに限定する、といった使い分けが現実的です。
| 方式 | 特徴 | 向いているケース | 注意点 |
|---|---|---|---|
| B ツリー（クラスタ化＋非クラスタ化） | 行指向。単一行・少数行の読み書きに強い | OLTP、最新データ中心のダッシュボード | 大規模集計はフルスキャンになりやすい |
| 非クラスタ化列ストア（NCCI） | 行ストアを残しつつ、集計・範囲スキャンを高速化 | 複雑な分析クエリと OLTP が混在 | INSERT パターン次第でメンテが必要 |
| クラスタ化列ストア（CCI） | 読み取り・集計に特化。圧縮率が高くコスト効率も良い | 履歴・アーカイブテーブル、分析専用テーブル | 頻繁な更新・削除には向かない |
パーティション分割は「性能」より「運用」のためと理解する
ここまでで触れた通り、パーティション分割は万能な性能改善策ではありません。Azure SQL Database におけるパーティションの主な役割は、次の 2 つと考えるのが現実的です。
- パーティションエリミネーションによる、限定的な I/O 削減
- 古いデータの丸ごと削除・アーカイブ、メンテナンス範囲の局所化
特に「古いデータをローリングで削除したい」「直近 1 年以外は別 DB や別サービスに逃がしたい」といった要件がある場合に、パーティションの威力が発揮されます。
日付でパーティション分割する基本パターン
典型的には、date または datetime2 の日付部分で月次パーティションを切るパターンが無難です。
-- 例：月単位のパーティション関数
CREATE PARTITION FUNCTION PF_TelemetryByMonth (date)
AS RANGE RIGHT FOR VALUES
(
'2023-01-01',
'2023-02-01',
'2023-03-01',
'2023-04-01'
-- 必要に応じて追加
);
-- Azure SQL Database では通常 PRIMARY ファイルグループを使用
CREATE PARTITION SCHEME PS_TelemetryByMonth
AS PARTITION PF_TelemetryByMonth
ALL TO ([PRIMARY]);
-- クラスタ化インデックスをパーティション整合で作成
CREATE CLUSTERED INDEX CIX_Telemetry_Timestamp
ON dbo.Telemetry ([timestamp])
ON PS_TelemetryByMonth ([timestamp]);
ここで重要なのは、クラスタ化インデックスのキーにパーティション列（ここでは [timestamp]）を含めることです。これにより、インデックスとパーティションの境界が一致し、古いパーティションの入れ替え（SWITCH）や TRUNCATE を安全に行えます。
Azure SQL Database とオンプレ SQL Server の違い
Azure SQL Database（特に PaaS 単体 DB）でのパーティションは、オンプレミスの SQL Server と比べていくつか制約があります。
- 基本的に PRIMARY ファイルグループのみを使用（ファイルグループを使った物理配置の最適化は限定的）
- パーティション SWITCH は同一データベース内でのみ有効（別データベース間 SWITCH は不可）
- SWITCH 先/元のテーブルは、インデックス定義を含めて完全一致している必要がある
- Hyperscale でも論理的なパーティション構成は同様だが、ストレージ分散はサービス側で抽象化されている
つまり、オンプレでよくある「古いパーティションだけ別ファイルグループに移して、ストレージを段階的に切り替える」といった物理チューニングはほとんどできません。その代わり、Azure のマネージドストレージに任せつつ、「論理的なスライディングウィンドウ運用」を整理する、という役割になります。
パーティション列とクエリパターンの相性
パーティションエリミネーションの恩恵を受けるには、クエリが必ずパーティション列で範囲指定する必要があります。
-- 良い例：パーティション列で範囲指定
SELECT ...
FROM dbo.Telemetry
WHERE [timestamp] >= @from
AND [timestamp] < @to
AND device_id = @deviceId;
一方で、次のようなクエリが多い場合は要注意です。
-- 例：パーティション列を使わないクエリ
SELECT ...
FROM dbo.Telemetry
WHERE device_id = @deviceId
AND metric_name = @metric;
こうしたクエリは全パーティションをまたいで検索する必要があるため、逆にオーバーヘッドが増えることもあります。「すべての主要クエリが必ず日付で絞り込む」のでなければ、パーティションは性能改善よりも運用のためと割り切るのが無難です。
パーティション数の粒度：日次か月次か
粒度を細かくしすぎると、管理するパーティション数が膨れ上がり、統計やインデックスメンテナンスのコストが増えます。目安として、年単位で数十〜数百パーティション程度に収めるのが扱いやすい範囲です。
| 粒度 | メリット | デメリット | 向いているケース |
|---|---|---|---|
| 日次パーティション | 細かい単位で TRUNCATE / SWITCH が可能 | 数年分で数千パーティションになりやすい | 保持期間が短い、日単位で厳密な削除が必要 |
| 月次パーティション | パーティション数が抑えられ、運用しやすい | 1 日だけ消したいといった細かい要求には不向き | 保持期間が長い、月単位のアーカイブや課金が中心 |
既存インデックスへの影響
パーティション分割を導入すると、既存のインデックスも「パーティション整合」に再作成する必要が出てくる場合があります。
- クラスタ化インデックスはパーティション列を含めることがほぼ必須
- 非クラスタ化インデックスも、必要に応じてパーティション整合（ON パーティションスキーム）で作り直す
- CCI に切り替える場合、従来のクラスタ化 B ツリーは置き換わるため、細かいルックアップが必要なクエリには別途非クラスタ化インデックスが必要
特に、パーティション導入時にはインデックス再構築が大規模になるため、メンテナンス時間帯やロック影響も考慮して計画的に実施することが重要です。
アーキテクチャレベルでの分離・オフロード戦略
20 億行クラスの時系列を Azure SQL Database だけで完結させようとすると、どうしても「全部入り」の設計になりがちです。しかし、役割ごとにアーキテクチャを分離することで、全体のパフォーマンスとコストが安定します。
ホット／コールドデータの分離
まず考えたいのは、「頻繁に読まれる最新データ」と「めったに読まれない履歴データ」を分離することです。
- Telemetry_Hot（直近 90 日など）
- クラスタ化 B ツリー：
(device_id, [timestamp] DESC) - 頻出クエリに合わせた非クラスタ化インデックス＋フィルタードインデックス
- 読み取りレプリカ（読み取りスケールアウト）でダッシュボードを捌く
- クラスタ化 B ツリー：
- Telemetry_Archive（それより古いデータ）
- CCI または NCCI メイン
- 日次・月次パーティション＋スライディングウィンドウで古いパーティションを順次アーカイブ/削除
- 日常的には参照せず、月次レポートや障害解析など限定用途
アプリケーション側では、参照期間に応じてテーブルを切り替えるか、ビューで UNION して吸収する設計が一般的です。
集約テーブルとインデックス付きビュー
秒単位・ミリ秒単位のデータをそのまま集計すると、どうしても巨大なスキャンになりがちです。よく使う粒度（5 分、15 分、1 時間など）で事前に集約テーブルを作っておくと、レポートクエリのレスポンスが劇的に改善します。
CREATE TABLE dbo.TelemetryAgg1h
(
device_id int NOT NULL,
ts_hour datetime2(0) NOT NULL,
metric_name nvarchar(100) NOT NULL,
avg_value float NOT NULL,
max_value float NOT NULL,
min_value float NOT NULL,
cnt bigint NOT NULL,
CONSTRAINT PK_TelemetryAgg1h
PRIMARY KEY CLUSTERED (device_id, ts_hour, metric_name)
);
集約テーブルへのロードは、Azure Functions や Azure Data Factory、Synapse Pipelines などを使ったバッチ処理で行います。「生データテーブルを直接集計するレポート」を「集約テーブルを見るレポート」に順次置き換えていくイメージです。
Azure Data Explorer（ADX）や Synapse へのオフロード
秒〜サブ秒単位のテレメトリを何年も残し、高度な分析・可視化を行いたい場合、Azure SQL Database だけで完結させるのは現実的ではありません。その場合は、次のような分担が有効です。
- Azure SQL Database：オンライン API / ダッシュボード、直近データ、集約テーブル
- Azure Data Explorer（ADX）：長期の生テレメトリ、自由度の高いクエリ、Kusto クエリ言語による分析
- Azure Synapse：大規模 ETL / BI、他システムとのデータ連携
必要に応じて、Azure SQL から ADX や Synapse のデータを外部テーブルで参照する構成も検討できます。オンライン処理とバッチ分析をきちんと分けることで、それぞれに最適なチューニングが可能になります。
Azure SQL Database における運用ベストプラクティス
設計だけでなく、日常運用も性能に大きな影響を与えます。特に Hyperscale 環境では、次のポイントを押さえておきましょう。
- 統計情報の更新：
[timestamp]、device_idなど重要列を優先して更新。必要に応じて FULLSCAN で再作成 - インデックスメンテナンス：フラグメンテーションがひどいパーティション/インデックスだけを再構築／再編成
- 自動チューニング：自動インデックス作成/削除、自動プラン修正を有効化しておく
- 読み取りスケールアウト：読み取りレプリカに ApplicationIntent=ReadOnly で接続し、レポート系を逃がす
- スキーマの見直し：巨大な NVARCHAR(MAX) や JSON 列を分離し、本当に必要な列だけをメインテーブルに残す
| 運用タスク | 頻度の目安 | ポイント |
|---|---|---|
| 統計情報の更新 | 週次〜月次、または大規模バッチ後 | 最新パーティション・ホットテーブルを優先 |
| インデックス再構築／再編成 | 月次、または断片化が閾値を超えたとき | パーティション単位で対象を絞るとダウンタイムを抑えられる |
| 古いパーティションの TRUNCATE / SWITCH | 保持期間に応じて（月次・四半期ごとなど） | アプリ側の保持ポリシーと連動させる |
| Query Store のレビュー | 月次〜四半期 | 重いクエリ TOP N を見直し、インデックスとプランを再評価 |
パーティションを使う／使わない判断の目安
ここまでの内容を踏まえて、時系列テーブルのパーティションについて「いつ使うべきか」を整理します。
- パーティションを使うべきケース
- 保持期間が長く、古いデータを定期的に「丸ごと削除」したい
- すべての主要クエリが必ず日付で範囲指定している
- メンテナンス（インデックス再構築、統計更新など）をパーティション単位で分割したい
- 慎重に検討すべきケース
- パーティション列を条件に含まないクエリが多い
- パーティション境界の追加・SWITCH 手順・運用ドキュメントの整備に割けるリソースが限られている
- 性能問題の主因がインデックス設計や統計のズレである可能性が高い（＝まずそこを直すべき）
パーティションは強力な機能ですが、「設計と運用の複雑さ」というコストも伴います。20 億行という数字に惑わされず、「本当にやりたいことは何か（高速化なのか、削除・アーカイブなのか）」を明確にしたうえで判断することが重要です。
短期で効くチェックリスト：まず何をするか
最後に、「今まさに 20 億行テーブルが遅くて困っている」という状況で、明日から着手できるアクションを整理します。
- Query Store で上位 5〜10 本の重いクエリを特定し、実行プランの Seek / Scan と推定行数 vs 実測行数を確認する
- 典型クエリの WHERE / JOIN / ORDER BY に合わせて、複合＋カバリングインデックス を設計し直す
- 非 SARGable な WHERE 句（列に関数をかける、LIKE 先頭ワイルドカードなど）を洗い出して修正する
- 集計・範囲スキャン主体のクエリに対して、非クラスタ化列ストア（NCCI） を追加し、効果を測定する
- ローリング削除や長期保管が必要なら、月次パーティション を前提に設計し、SWITCH / TRUNCATE を使った運用フローを作る
- 依然として長期間・広範囲の分析が重い場合は、Azure Data Explorer や Synapse へのオフロードを検討する
まとめ：パーティションは「最後の一手」、まずは設計と運用を整える
Azure SQL Database の Hyperscale で 20 億行もの時系列データを扱うとき、一番やってはいけないのは、「行数が多いからパーティションを切れば速くなるはず」と短絡的に考えることです。パーティションは主に運用（削除・アーカイブ・メンテナンス）の武器であり、クエリ性能そのものは、インデックス設計・列ストア・統計・実行プランの見直しで大きく変わります。
まずは Query Store で現状を可視化し、複合インデックスとカバリング、列ストアを組み合わせて「典型クエリにとことん寄せる」こと。そのうえで、保持期間やアーカイブ要件に応じてパーティションを導入し、ローリングウィンドウ運用を整える。さらに長期の自由度の高い分析は Azure Data Explorer や Synapse にオフロードする——この段階的なアプローチが、Azure SQL Database で超大規模な時系列テーブルを安定して高速に扱うための、もっとも現実的なベストプラクティスです。
コメント