# 機能設計書 150-Get-DbaTopResourceUsage

## 概要

本ドキュメントは、dbatools PowerShellモジュールにおける`Get-DbaTopResourceUsage`コマンドレットの機能設計を定義する。本機能は、sys.dm_exec_query_statsからキャッシュされたクエリの中で最もリソースを消費しているクエリを特定し、パフォーマンストラブルシューティングを支援する。

### 本機能の処理概要

`Get-DbaTopResourceUsage`は、DMV sys.dm_exec_query_statsを分析して、4つの主要メトリクス（Duration、Frequency、IO、CPU）に基づいて最もリソース消費の大きいクエリを特定するコマンドレットである。query_hashによってクエリをグループ化し、各メトリクスについてトップN件（デフォルト20件）を返す。

**業務上の目的・背景**：SQL Serverのパフォーマンス問題の多くは、少数の非効率なクエリに起因する。本機能は、DMVへの複雑なクエリを手動で記述することなく、即座にパフォーマンス問題の原因となるクエリを特定できる。実行時間、実行頻度、I/O操作、CPU消費の4つの観点から分析することで、様々なボトルネックパターンに対応できる。

**機能の利用シーン**：
- サーバーパフォーマンス低下時の原因クエリ特定
- 定期的なパフォーマンスレビュー
- クエリチューニング対象の優先順位付け
- キャパシティプランニングのためのワークロード分析
- アプリケーションのパフォーマンス問題調査
- 特定データベースのクエリ分析

**主要な処理内容**：
1. Connect-DbaInstanceでSQL Serverに接続（最小バージョン10=SQL 2008以降）
2. Type指定に基づいて対応するT-SQLクエリを実行
3. Duration: 総経過時間によるトップクエリ
4. Frequency: 実行回数によるトップクエリ
5. IO: 論理読み取り+書き込みによるトップクエリ
6. CPU: ワーカー時間によるトップクエリ
7. 各クエリにはクエリテキストと実行プランを含む

**関連システム・外部連携**：
- SQL Server DMV（sys.dm_exec_query_stats等）
- Connect-DbaInstance（SQL Server接続）
- Michael J. Swart氏のクエリを基にした実装

**権限による制御**：VIEW SERVER STATE権限が必要。DMVへのアクセスにはサーバーレベルの権限が必要。

## 関連画面

| 画面No | 画面名 | 関連種別 | 関連する操作・処理 |
|--------|--------|----------|------------------|
| - | CLIコマンド | 主機能 | リソース消費トップクエリ取得 |

## 機能種別

情報取得処理

## 入力仕様

### 入力パラメータ

| パラメータ名 | 型 | 必須 | 説明 | バリデーション |
|-------------|-----|-----|------|---------------|
| SqlInstance | DbaInstanceParameter[] | Yes | 対象SQL Serverインスタンス | パイプライン入力対応 |
| SqlCredential | PSCredential | No | SQL Server認証用クレデンシャル | - |
| Database | object[] | No | 分析対象データベース | - |
| ExcludeDatabase | object[] | No | 除外するデータベース | - |
| ExcludeSystem | switch | No | システムオブジェクト（sp_MS%）を除外 | - |
| Type | string[] | No | 分析タイプ（All/Duration/Frequency/IO/CPU、デフォルトAll） | ValidateSet |
| Limit | int | No | 取得件数（デフォルト20） | - |
| EnableException | switch | No | 例外を有効化 | - |

### 入力データソース

- sys.dm_exec_query_stats（クエリ統計）
- sys.dm_exec_sql_text（SQLテキスト）
- sys.dm_exec_query_plan（実行プラン）
- sys.dm_exec_plan_attributes（プラン属性）

## 出力仕様

### 出力データ（Durationタイプ）

| 項目名 | 型 | 説明 |
|--------|-----|------|
| ComputerName | string | コンピュータ名 |
| InstanceName | string | インスタンス名 |
| SqlInstance | string | 完全なSQL Serverインスタンス名 |
| Database | string | データベース名 |
| ObjectName | string | オブジェクト名（ストアドプロシージャ等、なければ'<none>'） |
| QueryHash | binary | クエリハッシュ |
| TotalElapsedTimeMs | bigint | 総経過時間（ミリ秒） |
| ExecutionCount | bigint | 実行回数 |
| AverageDurationMs | money | 平均実行時間（ミリ秒） |
| QueryTotalElapsedTimeMs | bigint | クエリハッシュごとの総経過時間 |
| QueryText | string | SQLクエリテキスト |
| QueryPlan | xml | 実行プラン（Select-Object *で表示） |

### 出力データ（Frequencyタイプ）

| 項目名 | 型 | 説明 |
|--------|-----|------|
| ComputerName | string | コンピュータ名 |
| InstanceName | string | インスタンス名 |
| SqlInstance | string | 完全なSQL Serverインスタンス名 |
| Database | string | データベース名 |
| ObjectName | string | オブジェクト名 |
| QueryHash | binary | クエリハッシュ |
| ExecutionCount | bigint | 実行回数 |
| QueryTotalExecutions | bigint | クエリハッシュごとの総実行回数 |
| QueryText | string | SQLクエリテキスト |
| QueryPlan | xml | 実行プラン |

### 出力データ（IOタイプ）

| 項目名 | 型 | 説明 |
|--------|-----|------|
| ComputerName | string | コンピュータ名 |
| InstanceName | string | インスタンス名 |
| SqlInstance | string | 完全なSQL Serverインスタンス名 |
| Database | string | データベース名 |
| ObjectName | string | オブジェクト名 |
| QueryHash | binary | クエリハッシュ |
| TotalIO | bigint | 総IO（論理読み取り+書き込み） |
| ExecutionCount | bigint | 実行回数 |
| AverageIO | money | 平均IO |
| QueryTotalIO | bigint | クエリハッシュごとの総IO |
| QueryText | string | SQLクエリテキスト |
| QueryPlan | xml | 実行プラン |

### 出力データ（CPUタイプ）

| 項目名 | 型 | 説明 |
|--------|-----|------|
| ComputerName | string | コンピュータ名 |
| InstanceName | string | インスタンス名 |
| SqlInstance | string | 完全なSQL Serverインスタンス名 |
| Database | string | データベース名 |
| ObjectName | string | オブジェクト名 |
| QueryHash | binary | クエリハッシュ |
| CpuTime | bigint | CPU時間（マイクロ秒） |
| ExecutionCount | bigint | 実行回数 |
| AverageCpuMs | money | 平均CPU時間（ミリ秒） |
| QueryTotalCpu | bigint | クエリハッシュごとの総CPU時間 |
| QueryText | string | SQLクエリテキスト |
| QueryPlan | xml | 実行プラン |

### 出力先

パイプライン（DataRow / PSCustomObject）

## 処理フロー

### 処理シーケンス

```
1. クエリ構築（begin）
   ├─ Databaseフィルタ条件の構築
   ├─ ExcludeDatabaseフィルタ条件の構築
   ├─ ExcludeSystemフィルタ条件の構築
   └─ 4種類のT-SQLクエリ定義
2. SqlInstanceループ（process）
   └─ Connect-DbaInstance -MinimumVersion 10
3. Typeに基づくクエリ実行
   ├─ Type含む"Duration": $durationクエリ実行
   ├─ Type含む"Frequency": $frequencyクエリ実行
   ├─ Type含む"IO": $ioクエリ実行
   └─ Type含む"CPU": $cpuクエリ実行
4. 結果出力
   └─ Select-DefaultView -ExcludeProperty QueryPlan
```

### フローチャート

```mermaid
flowchart TD
    A[開始] --> B[begin: フィルタ条件構築]
    B --> C[4種類のT-SQLクエリ定義]
    C --> D[SqlInstanceループ]
    D --> E[Connect-DbaInstance<br/>-MinimumVersion 10]
    E --> F{接続成功?}
    F -->|No| G[Stop-Function]
    F -->|Yes| H{Type含む<br/>Duration?}
    H -->|Yes| I[Durationクエリ実行<br/>Select-DefaultView]
    H -->|No| J{Type含む<br/>Frequency?}
    I --> J
    J -->|Yes| K[Frequencyクエリ実行<br/>Select-DefaultView]
    J -->|No| L{Type含む<br/>IO?}
    K --> L
    L -->|Yes| M[IOクエリ実行<br/>Select-DefaultView]
    L -->|No| N{Type含む<br/>CPU?}
    M --> N
    N -->|Yes| O[CPUクエリ実行<br/>Select-DefaultView]
    N -->|No| P{次のInstance<br/>あり?}
    O --> P
    P -->|Yes| D
    P -->|No| Q[終了]
    G --> P
```

## ビジネスルール

### 業務ルール

| ルールNo | ルール名 | 内容 | 適用条件 |
|---------|---------|------|---------|
| BR-001 | 最小バージョン | SQL Server 2008（バージョン10）以降が必要 | 接続時 |
| BR-002 | デフォルトタイプ | Type未指定時は"All"（4種類すべて） | Type未指定 |
| BR-003 | デフォルト件数 | Limit未指定時は20件 | Limit未指定 |
| BR-004 | query_hashグループ化 | 同一query_hashのクエリは集計される | 常時 |
| BR-005 | 0x0 query_hash除外 | query_hashが0x0のクエリは除外 | 常時 |
| BR-006 | システムオブジェクト除外 | ExcludeSystem時はsp_MS%を除外 | ExcludeSystem指定 |
| BR-007 | QueryPlanデフォルト非表示 | Select-DefaultViewでQueryPlanは非表示 | 常時 |

### 計算ロジック

- AverageDurationMs = total_elapsed_time / 1000 / execution_count
- AverageIO = (total_logical_reads + total_logical_writes) / execution_count
- AverageCpuMs = total_worker_time / execution_count

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

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

| 操作 | 対象テーブル | 操作種別 | 概要 |
|-----|-------------|---------|------|
| クエリ統計取得 | sys.dm_exec_query_stats | SELECT | キャッシュされたクエリ統計 |
| SQLテキスト取得 | sys.dm_exec_sql_text | SELECT | クエリテキスト |
| 実行プラン取得 | sys.dm_exec_query_plan | SELECT | 実行プラン |
| プラン属性取得 | sys.dm_exec_plan_attributes | SELECT | データベースID等 |

### クエリ構造（Durationの例）

```sql
;WITH long_queries AS
(
    SELECT TOP {Limit}
        query_hash,
        SUM(total_elapsed_time) elapsed_time
    FROM sys.dm_exec_query_stats
    WHERE query_hash <> 0x0
    GROUP BY query_hash
    ORDER BY SUM(total_elapsed_time) DESC
)
SELECT
    SERVERPROPERTY('MachineName') AS ComputerName,
    ...
    qs.query_hash AS QueryHash,
    qs.total_elapsed_time / 1000 AS TotalElapsedTimeMs,
    ...
FROM sys.dm_exec_query_stats qs
JOIN long_queries lq ON lq.query_hash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE pa.attribute = 'dbid' {フィルタ条件}
ORDER BY lq.elapsed_time DESC
OPTION (RECOMPILE)
```

## エラー処理

### エラーケース一覧

| エラーコード | エラー種別 | 発生条件 | 対処方法 |
|------------|----------|---------|---------|
| - | 接続エラー | SQL Server接続失敗 | 接続情報・ネットワーク確認 |
| - | バージョンエラー | SQL Server 2008より前 | SQL Server 2008以降に更新 |
| - | 権限エラー | VIEW SERVER STATE権限なし | 権限付与 |
| - | クエリエラー | DMVアクセス失敗 | 権限・リソース確認 |

### リトライ仕様

リトライは実装されていない。各Typeのクエリでエラー時はStop-Function -Continueで次のクエリへ。

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

読み取り専用のためトランザクション制御なし。OPTION (RECOMPILE)でプラン再生成。

## パフォーマンス要件

- Type=Allの場合、4つのクエリが順次実行される
- 各クエリはCTE（共通テーブル式）を使用してトップNを効率的に取得
- OPTION (RECOMPILE)により毎回実行プラン生成
- 大量のキャッシュプランがある環境では時間がかかる可能性

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

- VIEW SERVER STATE権限が必要
- クエリテキストと実行プランが表示される（機密情報含む可能性）
- SqlCredentialパラメータで安全に認証情報を渡せる

## 備考

- 参考: Michael J. Swart氏のクエリ（http://michaeljswart.com/go/Top20）
- dm_exec_query_statsはSQL Server再起動やプランキャッシュクリアでリセットされる
- query_hashは同じクエリパターンを識別するが、パラメータ値は異なる場合がある

---

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

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

### 推奨読解順序

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

出力オブジェクトの構造を理解する。

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 1-1 | Get-DbaTopResourceUsage.ps1 | `public/Get-DbaTopResourceUsage.ps1` | 4種類のクエリ結果構造 |

**読解のコツ**: 4つのT-SQLクエリ（$duration, $frequency, $io, $cpu）それぞれが異なる列構造を持つ。Select-DefaultView -ExcludeProperty QueryPlanでQueryPlanはデフォルト非表示。

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

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 2-1 | Get-DbaTopResourceUsage.ps1 | `public/Get-DbaTopResourceUsage.ps1` | 全体処理フロー |

**主要処理フロー**:
1. **163-179行目**: beginブロック、フィルタ条件構築
2. **165-167行目**: $instancecolumns定義
3. **169-175行目**: Database/ExcludeDatabase/ExcludeSystemフィルタ構築
4. **180-214行目**: $durationクエリ定義
5. **216-248行目**: $frequencyクエリ定義
6. **250-284行目**: $ioクエリ定義
7. **286-320行目**: $cpuクエリ定義
8. **323-366行目**: processブロック、Type判定とクエリ実行

#### Step 3: CTE構造を理解する

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 3-1 | Get-DbaTopResourceUsage.ps1 | `public/Get-DbaTopResourceUsage.ps1` | CTEによるトップN抽出 |

**主要処理フロー**:
- **180-189行目**: long_queries CTE（Duration用）
- **216-225行目**: frequent_queries CTE（Frequency用）
- **250-259行目**: high_io_queries CTE（IO用）
- **286-295行目**: high_cpu_queries CTE（CPU用）

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

```
Get-DbaTopResourceUsage
    │
    ├─ Connect-DbaInstance
    │      └─ -MinimumVersion 10（SQL 2008以降）
    │
    └─ Type判定
           │
           ├─ Duration: $server.Query($duration)
           │      └─ CTE: long_queries
           │
           ├─ Frequency: $server.Query($frequency)
           │      └─ CTE: frequent_queries
           │
           ├─ IO: $server.Query($io)
           │      └─ CTE: high_io_queries
           │
           └─ CPU: $server.Query($cpu)
                  └─ CTE: high_cpu_queries
```

### データフロー図

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

Type ──────────▶ Type判定
                      │
Database ───────▶ フィルタ条件構築
ExcludeDatabase       │
ExcludeSystem         │
Limit                 │
                      ▼
SqlInstance ────▶ Connect-DbaInstance
                      │
                      ▼
              ┌───────┴───────┐
              ▼               ▼
         Duration...      Frequency...
              │               │
              ▼               ▼
         CTE+JOIN        CTE+JOIN
      dm_exec_query_stats  ...
      dm_exec_sql_text
      dm_exec_query_plan
              │               │
              ▼               ▼
         [DataTable]     [DataTable]
              │               │
              ▼               ▼
    Select-DefaultView  Select-DefaultView
    -ExcludeProperty    -ExcludeProperty
    QueryPlan           QueryPlan
              │               │
              └───────┬───────┘
                      ▼
                パイプライン出力
```

### 関連ファイル一覧

| ファイル | パス | 種別 | 役割 |
|---------|------|------|------|
| Get-DbaTopResourceUsage.ps1 | `public/Get-DbaTopResourceUsage.ps1` | ソース | メイン機能 |
| Connect-DbaInstance.ps1 | `public/Connect-DbaInstance.ps1` | ソース | SQL Server接続 |
