# 機能設計書 2-sp_BlitzCache

## 概要

本ドキュメントは、SQL Server First Responder Kitのsp_BlitzCacheストアドプロシージャの機能設計について記載する。sp_BlitzCacheはプランキャッシュから最もリソースを消費しているクエリを分析・特定する診断ツールである。

### 本機能の処理概要

sp_BlitzCacheは、SQL Serverのプランキャッシュを分析し、CPU、読み取り、書き込み、実行時間などの観点から最もリソースを消費しているクエリを特定し、パフォーマンスチューニングのための提案を出力するストアドプロシージャである。

**業務上の目的・背景**：パフォーマンス問題の原因となるクエリを迅速に特定することは、データベース管理者にとって重要な課題である。sp_BlitzCacheは、プランキャッシュに蓄積されたクエリ実行統計を分析し、チューニング優先度の高いクエリを自動的に抽出することで、効率的なパフォーマンス改善を支援する。

**機能の利用シーン**：
- パフォーマンス問題発生時の原因クエリ特定
- 定期的なクエリパフォーマンス監視
- インデックスチューニング候補の特定
- リソース消費パターンの分析
- クエリプランの品質評価

**主要な処理内容**：
1. プランキャッシュからクエリ統計情報の取得（dm_exec_query_stats等）
2. 指定ソート順序に基づくTop Nクエリの抽出
3. クエリプランの解析と問題パターンの検出
4. 警告・推奨事項の生成
5. AI連携によるアドバイス取得（SQL Server 2025以降、オプション）

**関連システム・外部連携**：
- sys.dm_exec_query_stats: クエリ実行統計
- sys.dm_exec_procedure_stats: ストアドプロシージャ統計
- sys.dm_exec_trigger_stats: トリガー統計
- 外部AI API（OpenAI等）との連携（@AI=1時）

**権限による制御**：
- VIEW SERVER STATE権限が必要
- 特定のDMVアクセスには追加権限が必要な場合あり

## 関連画面

| 画面No | 画面名 | 関連種別 | 関連する操作・処理 |
|--------|--------|----------|------------------|
| 2 | sp_BlitzCache 結果画面 | 主機能 | プランキャッシュを分析し、リソース消費の大きいクエリTop10を表示 |
| 3 | sp_BlitzCache 警告説明画面 | 主機能 | sp_BlitzCacheの第2結果セットとして警告内容のグロッサリーを表示 |
| 5 | sp_BlitzFirst 詳細統計画面（ExpertMode） | 補助機能 | @OutputTableNameBlitzCache指定時にsp_BlitzCacheを呼び出しキャッシュ分析を実行 |
| 12 | sp_BlitzAnalysis 結果画面 | API連携 | BlitzCacheテーブルから過去のクエリキャッシュ分析データを取得 |

## 機能種別

計算処理 / データ分析 / パフォーマンス診断

## 入力仕様

### 入力パラメータ

| パラメータ名 | 型 | 必須 | 説明 | バリデーション |
|-------------|-----|-----|------|---------------|
| @Help | BIT | No | ヘルプ情報の表示 | デフォルト0 |
| @Top | INT | No | 取得するクエリ数 | NULL時は自動設定 |
| @SortOrder | VARCHAR(50) | No | ソート順序（CPU/Reads/Duration等） | デフォルトCPU |
| @UseTriggersAnyway | BIT | No | トリガー情報を含めるか | NULL許可 |
| @ExportToExcel | BIT | No | Excel出力用フォーマット | デフォルト0 |
| @ExpertMode | TINYINT | No | 詳細モード（0/1/2） | デフォルト0 |
| @OutputType | VARCHAR(20) | No | 出力形式 | デフォルトTABLE |
| @OutputServerName | NVARCHAR(258) | No | 出力先サーバー名 | NULL許可 |
| @OutputDatabaseName | NVARCHAR(258) | No | 出力先データベース名 | NULL許可 |
| @OutputSchemaName | NVARCHAR(258) | No | 出力先スキーマ名 | NULL許可 |
| @OutputTableName | NVARCHAR(258) | No | 出力先テーブル名 | NULL許可 |
| @DurationFilter | DECIMAL(38,4) | No | 平均実行時間フィルタ（秒） | NULL許可 |
| @HideSummary | BIT | No | サマリー非表示 | デフォルト0 |
| @IgnoreSystemDBs | BIT | No | システムDB除外 | デフォルト1 |
| @OnlyQueryHashes | VARCHAR(MAX) | No | 特定クエリハッシュのみ | NULL許可 |
| @IgnoreQueryHashes | VARCHAR(MAX) | No | 除外クエリハッシュ | NULL許可 |
| @OnlySqlHandles | VARCHAR(MAX) | No | 特定SQLハンドルのみ | NULL許可 |
| @IgnoreSqlHandles | VARCHAR(MAX) | No | 除外SQLハンドル | NULL許可 |
| @QueryFilter | VARCHAR(10) | No | フィルタ（ALL/procedures/statements/functions） | デフォルトALL |
| @DatabaseName | NVARCHAR(128) | No | 対象データベース名 | NULL許可 |
| @StoredProcName | NVARCHAR(128) | No | 対象ストアドプロシージャ名 | NULL許可 |
| @SlowlySearchPlansFor | NVARCHAR(4000) | No | プラン内検索文字列 | NULL許可 |
| @Reanalyze | BIT | No | 再分析フラグ | デフォルト0 |
| @SkipAnalysis | BIT | No | 分析スキップ | デフォルト0 |
| @BringThePain | BIT | No | 大量データ許可 | デフォルト0 |
| @MinimumExecutionCount | INT | No | 最小実行回数 | デフォルト0 |
| @Debug | TINYINT | No | デバッグモード | デフォルト0 |
| @MinutesBack | INT | No | 何分前から分析 | NULL許可 |
| @AI | TINYINT | No | AI連携（1=有効） | デフォルト0 |
| @AIModel | VARCHAR(200) | No | AIモデル名 | NULL許可 |
| @AIURL | VARCHAR(200) | No | AI API URL | NULL許可 |
| @AICredential | VARCHAR(200) | No | AI認証情報 | NULL許可 |
| @Version | VARCHAR(30) | No | バージョン（OUTPUT） | 出力専用 |
| @VersionDate | DATETIME | No | バージョン日付（OUTPUT） | 出力専用 |

### 入力データソース

- sys.dm_exec_query_stats: クエリレベルの実行統計
- sys.dm_exec_procedure_stats: プロシージャレベルの統計
- sys.dm_exec_trigger_stats: トリガーレベルの統計
- sys.dm_exec_sql_text: SQLテキスト
- sys.dm_exec_query_plan: クエリプラン

## 出力仕様

### 出力データ

| 項目名 | 型 | 説明 |
|--------|-----|------|
| QueryType | NVARCHAR(258) | クエリの種類 |
| DatabaseName | sysname | データベース名 |
| AverageCPU | DECIMAL(38,4) | 平均CPU時間 |
| TotalCPU | DECIMAL(38,4) | 合計CPU時間 |
| PercentCPU | MONEY | CPU使用率 |
| AverageDuration | DECIMAL(38,4) | 平均実行時間 |
| TotalDuration | DECIMAL(38,4) | 合計実行時間 |
| AverageReads | BIGINT | 平均読み取り数 |
| TotalReads | BIGINT | 合計読み取り数 |
| ExecutionCount | BIGINT | 実行回数 |
| TotalWrites | BIGINT | 合計書き込み数 |
| PlanCreationTime | DATETIME | プラン作成日時 |
| LastExecutionTime | DATETIME | 最終実行日時 |
| QueryText | NVARCHAR(MAX) | クエリテキスト |
| QueryPlan | XML | クエリプラン |
| Warnings | VARCHAR(MAX) | 検出された警告 |
| Pattern | NVARCHAR(20) | 問題パターン |
| ai_advice | NVARCHAR(MAX) | AIアドバイス（@AI=1時） |

### 出力先

- 画面表示（デフォルト）
- 指定テーブルへの出力（@OutputTableName指定時）
- ##BlitzCacheProcs/##BlitzCacheResultsグローバル一時テーブル

## 処理フロー

### 処理シーケンス

```
1. パラメータ検証と初期化
   └─ ソート順序の正規化、一時テーブル作成

2. プランキャッシュからデータ取得
   └─ dm_exec_query_stats等からクエリ統計取得

3. ソート順序に基づく上位クエリ抽出
   └─ CPU/Reads/Duration等でランキング

4. クエリプラン分析
   └─ XMLプランのパース、問題パターン検出

5. 警告・推奨事項の生成
   └─ パラメータスニッフィング、暗黙的変換等

6. AI連携（オプション）
   └─ 外部AI APIへのプロンプト送信

7. 結果出力
   └─ 結果セット返却またはテーブル出力
```

### フローチャート

```mermaid
flowchart TD
    A[開始] --> B{@Help = 1?}
    B -->|Yes| C[ヘルプ出力]
    C --> Z[終了]
    B -->|No| D{SQL Server 2008以上?}
    D -->|No| E[非対応エラー]
    E --> Z
    D -->|Yes| F[パラメータ初期化]
    F --> G[##BlitzCacheProcs作成]
    G --> H[プランキャッシュ取得]
    H --> I[ソート順序でランキング]
    I --> J[プラン分析]
    J --> K[警告生成]
    K --> L{@AI = 1?}
    L -->|Yes| M[AI API呼び出し]
    L -->|No| N[結果出力]
    M --> N
    N --> Z
```

## ビジネスルール

### 業務ルール

| ルールNo | ルール名 | 内容 | 適用条件 |
|---------|---------|------|---------|
| BR-001 | ソート順序 | CPU/Reads/Writes/Duration/Executions等でソート可能 | @SortOrderパラメータ |
| BR-002 | Top制限 | @Top > 10かつ@SortOrder='all'時は@BringThePain=1必要 | 大量データ取得時 |
| BR-003 | システムDB除外 | master/model/msdb/tempdb等はデフォルト除外 | @IgnoreSystemDBs=1時 |
| BR-004 | 実行回数フィルタ | 指定回数未満のクエリを除外 | @MinimumExecutionCount指定時 |
| BR-005 | AI制限 | AI機能は単一クエリプランでのみ動作 | @AI=1時 |

### 計算ロジック

**CPU使用率計算**:
```sql
PercentCPUByType = (TotalCPU * 100.0) / TotalWorkerTimeForType
```

**平均値計算**:
```sql
AverageCPU = TotalCPU / ExecutionCount
AverageReads = TotalReads / ExecutionCount
AverageDuration = TotalDuration / ExecutionCount
```

## データベース操作仕様

### 操作別データベース影響一覧

| 操作 | 対象テーブル | 操作種別 | 概要 |
|-----|-------------|---------|------|
| 参照 | sys.dm_exec_query_stats | SELECT | クエリ統計取得 |
| 参照 | sys.dm_exec_procedure_stats | SELECT | プロシージャ統計取得 |
| 参照 | sys.dm_exec_trigger_stats | SELECT | トリガー統計取得 |
| 参照 | sys.dm_exec_sql_text | SELECT | SQLテキスト取得 |
| 参照 | sys.dm_exec_query_plan | SELECT | クエリプラン取得 |
| 挿入 | ##BlitzCacheProcs | INSERT | 一時結果格納 |
| 挿入 | ##BlitzCacheResults | INSERT | 警告結果格納 |

### テーブル別操作詳細

#### ##BlitzCacheProcs（グローバル一時テーブル）

| 操作 | 項目（カラム名） | 更新値・取得条件 | 備考 |
|-----|-----------------|-----------------|------|
| INSERT | QueryType, DatabaseName, TotalCPU等 | プランキャッシュから抽出 | SPIDごとに管理 |

## エラー処理

### エラーケース一覧

| エラーコード | エラー種別 | 発生条件 | 対処方法 |
|------------|----------|---------|---------|
| - | バージョンエラー | SQL Server 2005/2000 | エラーメッセージ出力、RETURN |
| - | AI接続エラー | AI API接続失敗 | エラーメッセージ出力、処理継続 |
| - | 無効ソート順序 | 不正な@SortOrder値 | デフォルト（CPU）を使用 |

### リトライ仕様

AI連携時のAPI呼び出しにおいて、一時的なエラーに対するリトライは実装されていない。

## トランザクション仕様

READ UNCOMMITTED分離レベルで実行される。診断目的のため、ロックを最小化している。

## パフォーマンス要件

- 通常実行: 数秒から数十秒
- @SortOrder='all': 複数のソート条件で分析するため時間がかかる
- @SlowlySearchPlansFor: プラン内文字列検索は低速

## セキュリティ考慮事項

- VIEW SERVER STATE権限が必要
- AI連携時は外部APIに認証情報を送信
- クエリテキストにはセンシティブ情報が含まれる可能性

## 備考

- バージョン: 8.29（2026年2月3日時点）
- SQL Server 2008以降対応
- AI機能はSQL Server 2025以降で利用可能

---

## コードリーディングガイド

本機能を理解するために参照すべきファイルと、推奨する読み解き順序を以下に示す。

### 推奨読解順序

#### Step 1: データ構造を理解する

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 1-1 | sp_BlitzCache.sql | `sp_BlitzCache.sql` | **38-47行目**: ##BlitzCacheResults一時テーブル定義 |
| 1-2 | sp_BlitzCache.sql | `sp_BlitzCache.sql` | **49-242行目**: ##BlitzCacheProcs一時テーブル定義 |

**読解のコツ**: ##BlitzCacheProcsがメイン結果格納テーブルで、クエリ統計と分析結果を保持する。計算列（index_ops, [Remove Plan Handle From Cache]等）の定義に注目。

#### Step 2: エントリーポイントを理解する

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 2-1 | sp_BlitzCache.sql | `sp_BlitzCache.sql` | **245-288行目**: ALTER PROCEDURE定義とパラメータ |
| 2-2 | sp_BlitzCache.sql | `sp_BlitzCache.sql` | **290-301行目**: 初期設定とバージョンチェック |

**主要処理フロー**:
1. **290-293行目**: SET NOCOUNT ON、STATISTICS XML OFF、READ UNCOMMITTED設定
2. **295行目**: バージョン情報設定
3. **298-301行目**: @VersionCheckMode処理

#### Step 3: ヘルプ出力を理解する

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 3-1 | sp_BlitzCache.sql | `sp_BlitzCache.sql` | **305-500行目**: @Help=1時のパラメータ説明出力 |

**主要処理フロー**:
- **350-500行目**: 各パラメータの説明をSELECT文で出力

### プログラム呼び出し階層図

```
sp_BlitzCache
    │
    ├─ sys.dm_exec_query_stats (クエリ統計)
    │
    ├─ sys.dm_exec_procedure_stats (プロシージャ統計)
    │
    ├─ sys.dm_exec_trigger_stats (トリガー統計)
    │
    ├─ sys.dm_exec_sql_text (SQLテキスト)
    │
    ├─ sys.dm_exec_query_plan (クエリプラン)
    │
    ├─ ##BlitzCacheProcs (結果格納)
    │
    ├─ ##BlitzCacheResults (警告格納)
    │
    └─ 外部AI API (オプション)
           └─ sp_invoke_external_rest_endpoint
```

### データフロー図

```
[入力]                           [処理]                              [出力]

パラメータ ──────────────────▶ パラメータ検証 ─────────────────▶ ##BlitzCacheProcs
   │                              │                                   │
   │                              ▼                                   │
dm_exec_query_stats ─────────▶ クエリ統計取得 ───────────────────▶ │
   │                              │                                   │
dm_exec_procedure_stats ─────▶ プロシージャ統計取得 ─────────────▶ │
   │                              │                                   │
dm_exec_trigger_stats ───────▶ トリガー統計取得 ─────────────────▶ │
   │                              │                                   │
dm_exec_query_plan ──────────▶ プラン分析 ───────────────────────▶ │
                                  │                                   │
                                  ▼                                   │
                            警告生成 ◀────────────────────────────────┘
                                  │
                                  ▼
                         [結果セット/テーブル]
                                  │
                         [AI API(オプション)]
```

### 関連ファイル一覧

| ファイル | パス | 種別 | 役割 |
|---------|------|------|------|
| sp_BlitzCache.sql | `sp_BlitzCache.sql` | ソース | メインストアドプロシージャ定義 |
| sp_BlitzFirst.sql | `sp_BlitzFirst.sql` | 関連 | sp_BlitzCacheを呼び出し可能 |
| sp_BlitzAnalysis.sql | `sp_BlitzAnalysis.sql` | 関連 | BlitzCacheテーブルからデータ取得 |
