クエリストア情報_取得.ps1

[CmdletBinding()]
param (
    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [psobject]
    # SQL Serverのインスタンス
    $ServerInstance,

    [Parameter(Mandatory = $true)]
    [string]
    # データベース名
    $Database,

    [Parameter()]
    [string]
    # SQL Serverのユーザー名
    $Username,

    [Parameter()]
    [string]
    # SQL Serverのパスワード
    $Password,

    [Parameter()]
    [int]
    # 取得するデータ数の上限
    $ResultsRowCount = 1000,

    [Parameter()]
    [ValidateNotNullOrEmpty()]
    [string]
    # 取得する期間の開始時刻（デフォルトは現在日時より24時間前）
    $IntervalStartTime = "{0:yyyy-MM-dd HH:mm:ss zzz}" -f (Get-Date).AddDays(-1),

    [Parameter()]
    [ValidateNotNullOrEmpty()]
    [string]
    # 取得する期間の終了時刻（デフォルトは現在日時）
    $IntervalEndTime = "{0:yyyy-MM-dd HH:mm:ss zzz}" -f (Get-Date),

    [Parameter()]
    [ValidateNotNullOrEmpty()]
    [string]
    # 取得結果の出力先フォルダ（デフォルトはカレントディレクトリ）
    $OutDirectory = $PWD,

    [Parameter()]
    [Alias("NoOverwrite")]
    [switch]
    # 出力ファイルを上書きしないようにするオプション
    $NoClobber = $false,

    [Parameter()]
    [ArgumentCompleter( { @("default", "oem", "utf8") } )]
    [string]
    # 出力ファイルのエンコーディング（デフォルトはdefault）
    $Encoding = "default"
)

begin {
    $ErrorActionPreference = 'Stop'
    $Verbose = $VerbosePreference -ne 'SilentlyContinue'

    $PSDefaultParameterValues = @{'*:Encoding' = $Encoding }

    $outputDir = mkdir $OutDirectory -Force -Verbose:$Verbose
}

process {
    function Invoke-Sql([string] $Query) {
        $params = @{
            ServerInstance = $ServerInstance
            Query          = $Query
        }

        if ($Database) {
            $params += @{ Database = $Database }
        }

        if ($Username) {
            $params += @{ Username = $Username }
        }

        if ($Password) {
            $params += @{ Password = $Password }
        }

        return Invoke-Sqlcmd @params
    }

    function Get-QueryWaitStats {
        return Invoke-Sql @"
exec sp_executesql N'SELECT TOP (@results_row_count)
    ws.wait_category wait_category,
    ws.wait_category_desc wait_category_desc,
    ROUND(CONVERT(float, SUM(ws.total_query_wait_time_ms)/SUM(ws.total_query_wait_time_ms/ws.avg_query_wait_time_ms))*1,2) avg_query_wait_time,
    ROUND(CONVERT(float, MIN(ws.min_query_wait_time_ms))*1,2) min_query_wait_time,
    ROUND(CONVERT(float, MAX(ws.max_query_wait_time_ms))*1,2) max_query_wait_time,
    ROUND(CONVERT(float, SQRT( SUM(ws.stdev_query_wait_time_ms*ws.stdev_query_wait_time_ms*(ws.total_query_wait_time_ms/ws.avg_query_wait_time_ms))/SUM(ws.total_query_wait_time_ms/ws.avg_query_wait_time_ms)))*1,2) stdev_query_wait_time,
    ROUND(CONVERT(float, SUM(ws.total_query_wait_time_ms))*1,2) total_query_wait_time,
    CAST(ROUND(SUM(ws.total_query_wait_time_ms/ws.avg_query_wait_time_ms),0) AS BIGINT) count_executions
FROM sys.query_store_wait_stats ws
    JOIN sys.query_store_runtime_stats_interval itvl ON itvl.runtime_stats_interval_id = ws.runtime_stats_interval_id
WHERE NOT (itvl.start_time > @interval_end_time OR itvl.end_time < @interval_start_time)
GROUP BY ws.wait_category, wait_category_desc
ORDER BY total_query_wait_time DESC',N'@interval_start_time datetimeoffset(7),@interval_end_time datetimeoffset(7),@results_row_count int',@interval_start_time='$IntervalStartTime',@interval_end_time='$IntervalEndTime',@results_row_count=$ResultsRowCount
"@
    }

    function Get-QueryWaitStatsByWaitCategory([int] $wait_category) {
        return Invoke-Sql @"
exec sp_executesql N'SELECT TOP (@results_row_count)
    p.query_id query_id,
    q.object_id object_id,
    ISNULL(OBJECT_NAME(q.object_id),'''') object_name,
    qt.query_sql_text query_sql_text,
    ROUND(CONVERT(float, SUM(ws.total_query_wait_time_ms)/SUM(ws.total_query_wait_time_ms/ws.avg_query_wait_time_ms))*1,2) avg_query_wait_time,
    ROUND(CONVERT(float, MIN(ws.min_query_wait_time_ms))*1,2) min_query_wait_time,
    ROUND(CONVERT(float, MAX(ws.max_query_wait_time_ms))*1,2) max_query_wait_time,
    ROUND(CONVERT(float, SQRT( SUM(ws.stdev_query_wait_time_ms*ws.stdev_query_wait_time_ms*(ws.total_query_wait_time_ms/ws.avg_query_wait_time_ms))/SUM(ws.total_query_wait_time_ms/ws.avg_query_wait_time_ms)))*1,2) stdev_query_wait_time,
    ROUND(CONVERT(float, SUM(ws.total_query_wait_time_ms))*1,2) total_query_wait_time,
    CAST(ROUND(SUM(ws.total_query_wait_time_ms/ws.avg_query_wait_time_ms),0) AS BIGINT) count_executions
FROM sys.query_store_wait_stats ws
    JOIN sys.query_store_plan p on p.plan_id = ws.plan_id
    JOIN sys.query_store_query q ON q.query_id = p.query_id
    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_runtime_stats_interval itvl ON itvl.runtime_stats_interval_id = ws.runtime_stats_interval_id
WHERE NOT (itvl.start_time > @interval_end_time OR itvl.end_time < @interval_start_time) AND ws.wait_category = @wait_category
GROUP BY p.query_id, qt.query_sql_text, q.object_id
ORDER BY total_query_wait_time DESC',N'@interval_start_time datetimeoffset(7),@interval_end_time datetimeoffset(7),@wait_category bigint,@results_row_count int',@interval_start_time='$IntervalStartTime',@interval_end_time='$IntervalEndTime',@wait_category=$wait_category,@results_row_count=$ResultsRowCount
"@
    }

    function Get-TopResourceConsumers {
        return Invoke-Sql @"
exec sp_executesql N'With wait_stats AS
(
SELECT
    ws.plan_id plan_id,
    ws.wait_category,
    ROUND(CONVERT(float, SUM(ws.total_query_wait_time_ms)/SUM(ws.total_query_wait_time_ms/ws.avg_query_wait_time_ms))*1,2) avg_query_wait_time,
    ROUND(CONVERT(float, SQRT( SUM(ws.stdev_query_wait_time_ms*ws.stdev_query_wait_time_ms*(ws.total_query_wait_time_ms/ws.avg_query_wait_time_ms))/SUM(ws.total_query_wait_time_ms/ws.avg_query_wait_time_ms)))*1,2) stdev_query_wait_time,
    CAST(ROUND(SUM(ws.total_query_wait_time_ms/ws.avg_query_wait_time_ms),0) AS BIGINT) count_executions,
    MAX(itvl.end_time) last_execution_time,
    MIN(itvl.start_time) first_execution_time
FROM sys.query_store_wait_stats ws
    JOIN sys.query_store_runtime_stats_interval itvl ON itvl.runtime_stats_interval_id = ws.runtime_stats_interval_id
WHERE NOT (itvl.start_time > @interval_end_time OR itvl.end_time < @interval_start_time)
GROUP BY ws.plan_id, ws.runtime_stats_interval_id, ws.wait_category
),
top_wait_stats AS
(
SELECT
    p.query_id query_id,
    q.object_id object_id,
    ISNULL(OBJECT_NAME(q.object_id),'''') object_name,
    qt.query_sql_text query_sql_text,
    ROUND(CONVERT(float, SUM(ws.avg_query_wait_time*ws.count_executions))*1,2) total_query_wait_time,
    MAX(ws.count_executions) count_executions,
    COUNT(distinct p.plan_id) num_plans
FROM wait_stats ws
    JOIN sys.query_store_plan p ON p.plan_id = ws.plan_id
    JOIN sys.query_store_query q ON q.query_id = p.query_id
    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE NOT (ws.first_execution_time > @interval_end_time OR ws.last_execution_time < @interval_start_time)
GROUP BY p.query_id, qt.query_sql_text, q.object_id
),
top_other_stats AS
(
SELECT
    p.query_id query_id,
    q.object_id object_id,
    ISNULL(OBJECT_NAME(q.object_id),'''') object_name,
    qt.query_sql_text query_sql_text,
    ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
    ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) total_cpu_time,
    ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))*8,2) total_logical_io_reads,
    ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))*8,2) total_logical_io_writes,
    ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))*8,2) total_physical_io_reads,
    ROUND(CONVERT(float, SUM(rs.avg_clr_time*rs.count_executions))*0.001,2) total_clr_time,
    ROUND(CONVERT(float, SUM(rs.avg_dop*rs.count_executions))*1,0) total_dop,
    ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))*8,2) total_query_max_used_memory,
    ROUND(CONVERT(float, SUM(rs.avg_rowcount*rs.count_executions))*1,0) total_rowcount,
    ROUND(CONVERT(float, SUM(rs.avg_log_bytes_used*rs.count_executions))*0.0009765625,2) total_log_bytes_used,
    ROUND(CONVERT(float, SUM(rs.avg_tempdb_space_used*rs.count_executions))*8,2) total_tempdb_space_used,
    SUM(rs.count_executions) count_executions,
    COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
    JOIN sys.query_store_query q ON q.query_id = p.query_id
    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY p.query_id, qt.query_sql_text, q.object_id
)
SELECT TOP (@results_row_count)
    A.query_id query_id,
    A.object_id object_id,
    A.object_name object_name,
    A.query_sql_text query_sql_text,
    A.total_duration total_duration,
    A.total_cpu_time total_cpu_time,
    A.total_logical_io_reads total_logical_io_reads,
    A.total_logical_io_writes total_logical_io_writes,
    A.total_physical_io_reads total_physical_io_reads,
    A.total_clr_time total_clr_time,
    A.total_dop total_dop,
    A.total_query_max_used_memory total_query_max_used_memory,
    A.total_rowcount total_rowcount,
    A.total_log_bytes_used total_log_bytes_used,
    A.total_tempdb_space_used total_tempdb_space_used,
    ISNULL(B.total_query_wait_time,0) total_query_wait_time,
    A.count_executions count_executions,
    A.num_plans num_plans
FROM top_other_stats A LEFT JOIN top_wait_stats B on A.query_id = B.query_id and A.query_sql_text = B.query_sql_text and A.object_id = B.object_id
WHERE A.num_plans >= 1
ORDER BY count_executions DESC',N'@results_row_count int,@interval_start_time datetimeoffset(7),@interval_end_time datetimeoffset(7)',@results_row_count=$ResultsRowCount,@interval_start_time='$IntervalStartTime',@interval_end_time='$IntervalEndTime'
"@
    }

    function Get-QueryExecutionTime {
        return Invoke-Sql @"
SELECT
    q.query_id,
    q.object_id object_id,
    ISNULL(OBJECT_NAME(q.object_id),'') object_name,
    qt.query_sql_text query_sql_text,
    ws.count_executions,
    ROUND(CONVERT(float, ws.avg_duration)*0.001,2) avg_duration,
    ROUND(CONVERT(float, ws.avg_duration*ws.count_executions)*0.001,2) total_duration,
    ws.first_execution_time,
    ws.last_execution_time
FROM
    sys.query_store_runtime_stats ws
    JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id
    JOIN sys.query_store_query q ON p.query_id = q.query_id
    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE NOT (ws.first_execution_time > '$IntervalEndTime' OR ws.last_execution_time < '$IntervalStartTime')
ORDER BY
    ws.last_execution_time
"@
    }

    function Out-CsvFile {
        [CmdletBinding()]
        param(
            [Parameter(
                Mandatory = $true,
                ValueFromPipeline = $true)]
            [object]
            $InputObject,

            [Parameter(
                Mandatory = $true,
                Position = 0)]
            [string]
            $FileName
        )

        $Input | Export-Csv -LiteralPath (Join-Path $outputDir $FileName) -NoTypeInformation -NoClobber:$NoClobber -Verbose:$Verbose
    }

    $queryWaitStats = Get-QueryWaitStats

    if ($Verbose) {
        Write-Verbose "クエリ待機統計"
        $queryWaitStats | Format-Table -AutoSize | Out-String -Width 1000 | Write-Verbose
    }

    $queryWaitStats | Out-CsvFile "クエリ待機統計.csv"

    $queryWaitStats | ForEach-Object {
        $wcId = $_.wait_category
        $wcName = $_.wait_category_desc
        Get-QueryWaitStatsByWaitCategory $wcId | Out-CsvFile "$wcName.csv"
    }

    Get-TopResourceConsumers | Out-CsvFile "リソースを消費するクエリの上位.csv"

    Get-QueryExecutionTime | Out-CsvFile "クエリの実行時間.csv"
}