# 画面設計書 158-トップリソース使用

## 概要

本ドキュメントは、dbatools PowerShellモジュールにおけるトップリソース使用量分析機能の設計を定義する。sys.dm_exec_query_statsからキャッシュされたクエリのパフォーマンス統計を分析し、最もリソースを消費するクエリを特定する。

### 本画面の処理概要

本機能は、sys.dm_exec_query_statsを照会し、Duration（実行時間）、Frequency（実行頻度）、IO（論理読み書き）、CPU（ワーカー時間）の4つの観点でトップクエリを特定する。

**業務上の目的・背景**：SQL Serverのパフォーマンス問題を調査する際、どのクエリが最もリソースを消費しているかを把握することが重要である。本機能は、Michael J. Swartの手法に基づき、複雑なDMVクエリを手動で作成することなく、4つの主要指標でワーストパフォーマンスのクエリを迅速に特定できる。クエリハッシュでグループ化されるため、同一クエリの異なるプランも統合して分析できる。

**画面へのアクセス方法**：PowerShellコンソールまたはスクリプトから以下のコマンドを実行してアクセスする：
- `Get-DbaTopResourceUsage -SqlInstance <インスタンス名>`
- `Get-DbaTopResourceUsage -SqlInstance <インスタンス名> -Type Duration,CPU`

**主要な操作・処理内容**：
1. Duration（総実行時間）でトップ20クエリを取得
2. Frequency（実行回数）でトップ20クエリを取得
3. IO（論理読み取り+論理書き込み）でトップ20クエリを取得
4. CPU（ワーカー時間）でトップ20クエリを取得
5. クエリテキストとクエリプランの取得
6. データベースとオブジェクト名の解決

**画面遷移**：
- 本機能はスタンドアロンで動作し、他の機能への直接的な画面遷移はない
- パイプラインでSelect-Object等と連携可能

**権限による表示制御**：
- SQL Server接続権限が必要
- sys.dm_exec_query_stats への VIEW SERVER STATE 権限が必要

## 関連機能

| 機能No | 機能名 | 関連種別 | 関連する操作・処理 |
|--------|--------|----------|------------------|
| 150 | Get-DbaTopResourceUsage | 主機能 | トップリソース消費クエリの取得 |

## 画面種別

一覧 / 診断情報表示

## URL/ルーティング

PowerShellコマンドレット:
- `Get-DbaTopResourceUsage`

## 入出力項目

### Get-DbaTopResourceUsage パラメータ

| パラメータ名 | 型 | 必須 | 説明 |
|-------------|------|------|------|
| 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） |
| Limit | int | No | 取得件数（デフォルト: 20） |
| EnableException | switch | No | 例外処理の有効化 |

## 表示項目

### Duration タイプ出力プロパティ

| プロパティ名 | 型 | 説明 | デフォルト表示 |
|-------------|------|------|---------------|
| ComputerName | string | コンピュータ名 | Yes |
| InstanceName | string | インスタンス名 | Yes |
| SqlInstance | string | 完全インスタンス名 | Yes |
| Database | string | データベース名 | Yes |
| ObjectName | string | オブジェクト名（ストアドプロシージャ等） | Yes |
| QueryHash | binary | クエリハッシュ | Yes |
| TotalElapsedTimeMs | long | 総経過時間（ミリ秒） | Yes |
| ExecutionCount | long | 実行回数 | Yes |
| AverageDurationMs | decimal | 平均実行時間（ミリ秒） | Yes |
| QueryTotalElapsedTimeMs | long | クエリハッシュ別総経過時間 | Yes |
| QueryText | string | SQLテキスト | Yes |
| QueryPlan | xml | クエリプラン | No |

### Frequency タイプ出力プロパティ

| プロパティ名 | 型 | 説明 | デフォルト表示 |
|-------------|------|------|---------------|
| ComputerName | string | コンピュータ名 | Yes |
| InstanceName | string | インスタンス名 | Yes |
| SqlInstance | string | 完全インスタンス名 | Yes |
| Database | string | データベース名 | Yes |
| ObjectName | string | オブジェクト名 | Yes |
| QueryHash | binary | クエリハッシュ | Yes |
| ExecutionCount | long | 実行回数 | Yes |
| QueryTotalExecutions | long | クエリハッシュ別総実行回数 | Yes |
| QueryText | string | SQLテキスト | Yes |
| QueryPlan | xml | クエリプラン | No |

### IO タイプ出力プロパティ

| プロパティ名 | 型 | 説明 | デフォルト表示 |
|-------------|------|------|---------------|
| ComputerName | string | コンピュータ名 | Yes |
| InstanceName | string | インスタンス名 | Yes |
| SqlInstance | string | 完全インスタンス名 | Yes |
| Database | string | データベース名 | Yes |
| ObjectName | string | オブジェクト名 | Yes |
| QueryHash | binary | クエリハッシュ | Yes |
| TotalIO | long | 総IO（論理読み取り+書き込み） | Yes |
| ExecutionCount | long | 実行回数 | Yes |
| AverageIO | decimal | 平均IO | Yes |
| QueryTotalIO | long | クエリハッシュ別総IO | Yes |
| QueryText | string | SQLテキスト | Yes |
| QueryPlan | xml | クエリプラン | No |

### CPU タイプ出力プロパティ

| プロパティ名 | 型 | 説明 | デフォルト表示 |
|-------------|------|------|---------------|
| ComputerName | string | コンピュータ名 | Yes |
| InstanceName | string | インスタンス名 | Yes |
| SqlInstance | string | 完全インスタンス名 | Yes |
| Database | string | データベース名 | Yes |
| ObjectName | string | オブジェクト名 | Yes |
| QueryHash | binary | クエリハッシュ | Yes |
| CpuTime | long | CPU時間（マイクロ秒） | Yes |
| ExecutionCount | long | 実行回数 | Yes |
| AverageCpuMs | decimal | 平均CPU時間（ミリ秒） | Yes |
| QueryTotalCpu | long | クエリハッシュ別総CPU時間 | Yes |
| QueryText | string | SQLテキスト | Yes |
| QueryPlan | xml | クエリプラン | No |

## イベント仕様

### 1-Get-DbaTopResourceUsage実行

1. Connect-DbaInstanceでSQL Serverインスタンスに接続（最小バージョン: SQL Server 2008）
2. Typeパラメータに基づき各分析クエリを実行：
   - Duration: total_elapsed_timeでソートしたCTEクエリ
   - Frequency: execution_countでソートしたCTEクエリ
   - IO: (total_logical_reads + total_logical_writes)でソートしたCTEクエリ
   - CPU: total_worker_timeでソートしたCTEクエリ
3. sys.dm_exec_sql_textでクエリテキスト取得
4. sys.dm_exec_query_planでクエリプラン取得
5. sys.dm_exec_plan_attributesでデータベースID解決
6. COALESCE(DB_NAME, OBJECT_NAME)でデータベース名・オブジェクト名解決
7. Select-DefaultViewでQueryPlanを非表示に設定

## データベース更新仕様

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

| 操作（イベント） | 対象テーブル | 操作種別 | 概要 |
|----------------|-------------|---------|------|
| Get-DbaTopResourceUsage | sys.dm_exec_query_stats | SELECT | クエリ統計取得 |
| Get-DbaTopResourceUsage | sys.dm_exec_sql_text | SELECT | SQLテキスト取得 |
| Get-DbaTopResourceUsage | sys.dm_exec_query_plan | SELECT | クエリプラン取得 |
| Get-DbaTopResourceUsage | sys.dm_exec_plan_attributes | SELECT | プラン属性取得 |

### テーブル別更新項目詳細

本機能は読み取り専用のため、データベースへの更新は発生しない。

## メッセージ仕様

| メッセージID | レベル | 条件 | メッセージ内容 |
|-------------|--------|------|---------------|
| - | Debug | SQL実行時 | SQLクエリテキスト |
| - | Error | 接続失敗 | "Failure" |
| - | Error | クエリ失敗 | "Failure executing query for {type}." |

## 例外処理

| 例外条件 | 処理内容 |
|---------|---------|
| SQL Server接続失敗 | ConnectionError カテゴリでStop-Function実行、処理継続 |
| SQL Server 2008未満 | MinimumVersion 10 により自動的にスキップ |
| 個別クエリ失敗 | Stop-Function実行、次のタイプへ継続 |

## 備考

- Type "All" は Duration, Frequency, IO, CPU すべてを実行（最大80件）
- クエリハッシュでグループ化するため、同一クエリの異なるプランを統合
- ExcludeSystemは sp_MS% オブジェクトを除外（レプリケーション関連）
- OPTION(RECOMPILE) でクエリプランのキャッシュを回避
- 参考資料:
  - http://michaeljswart.com/go/Top20

---

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

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

### 推奨読解順序

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

トップリソース使用量情報のデータ構造を理解する。

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 1-1 | Get-DbaTopResourceUsage.ps1 | `public/Get-DbaTopResourceUsage.ps1` | 55-111行目: 各タイプの出力構造定義 |

**読解のコツ**: 4つのタイプで出力プロパティが異なることに注意。QueryPlanは常に非表示。

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

処理の起点となるコマンドレット関数を特定。

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 2-1 | Get-DbaTopResourceUsage.ps1 | `public/Get-DbaTopResourceUsage.ps1` | 149-161行目: param()ブロック |

**主要処理フロー**:
1. **163-320行目**: beginブロックで4つのSQLクエリ定義
2. **323-328行目**: Connect-DbaInstance
3. **331-365行目**: Typeごとのクエリ実行とSelect-DefaultView

#### Step 3: 各タイプのSQLクエリを理解する

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 3-1 | Get-DbaTopResourceUsage.ps1 | `public/Get-DbaTopResourceUsage.ps1` | 180-214行目: Durationクエリ |
| 3-2 | Get-DbaTopResourceUsage.ps1 | `public/Get-DbaTopResourceUsage.ps1` | 216-248行目: Frequencyクエリ |
| 3-3 | Get-DbaTopResourceUsage.ps1 | `public/Get-DbaTopResourceUsage.ps1` | 250-284行目: IOクエリ |
| 3-4 | Get-DbaTopResourceUsage.ps1 | `public/Get-DbaTopResourceUsage.ps1` | 286-320行目: CPUクエリ |

**主要処理フロー**:
- CTE (long_queries等) でTOP N集計
- dm_exec_query_statsとJOIN
- CROSS APPLY dm_exec_sql_text, dm_exec_query_plan
- OUTER APPLY dm_exec_plan_attributes (dbid取得用)

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

```
Get-DbaTopResourceUsage
    |
    +-- begin
    |       +-- WHERE句構築（Database, ExcludeDatabase, ExcludeSystem）
    |       +-- $duration SQLクエリ定義
    |       +-- $frequency SQLクエリ定義
    |       +-- $io SQLクエリ定義
    |       +-- $cpu SQLクエリ定義
    |
    +-- process
            |
            +-- Connect-DbaInstance (MinimumVersion 10)
            |
            +-- [Type contains "Duration"]
            |       +-- Server.Query($duration)
            |       +-- Select-DefaultView -ExcludeProperty QueryPlan
            |
            +-- [Type contains "Frequency"]
            |       +-- Server.Query($frequency)
            |       +-- Select-DefaultView -ExcludeProperty QueryPlan
            |
            +-- [Type contains "IO"]
            |       +-- Server.Query($io)
            |       +-- Select-DefaultView -ExcludeProperty QueryPlan
            |
            +-- [Type contains "CPU"]
                    +-- Server.Query($cpu)
                    +-- Select-DefaultView -ExcludeProperty QueryPlan
```

### データフロー図

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

SqlInstance    ------>  Connect-DbaInstance  ----+
                                                 |
Database       ------>  WHERE句構築              |
ExcludeDatabase -----+                           |
ExcludeSystem -------+                           |
                                                 v
Type           ------>  タイプ判定  ----+
  (All/Duration/       |  |  |  |       |
   Frequency/IO/CPU)   |  |  |  |       |
                       |  |  |  |       |
Limit          ------>  +--+--+--+       |
                       |                 |
                       v                 v
                   CTEクエリ実行    dm_exec_query_stats
                       |                 |
                       +-- CROSS APPLY --+
                       |   dm_exec_sql_text
                       |   dm_exec_query_plan
                       |
                       +-- OUTER APPLY --+
                           dm_exec_plan_attributes
                                |
                                v
                           PSCustomObject (タイプ別)
                                |
                                v
                           Select-DefaultView
                           (QueryPlan非表示)
```

### 関連ファイル一覧

| ファイル | パス | 種別 | 役割 |
|---------|------|------|------|
| Get-DbaTopResourceUsage.ps1 | `public/Get-DbaTopResourceUsage.ps1` | ソース | トップリソース使用量取得の主処理 |
| Connect-DbaInstance.ps1 | `public/Connect-DbaInstance.ps1` | ソース | SQL Server接続処理 |
