Lesson Learned #403:Using Azure SQL Database Activity Monitor for Query Performance Insights

[Jose_Manuel_Jurado]

In this article, we will explore a PowerShell script that serves as an Activity Monitor for Azure SQL Database query performance. By using this script, you can keep a close eye on the execution statistics of various queries running on your Azure SQL Database. We'll delve into the script's inner workings, explain the key components, and discuss why this monitoring approach is essential for optimizing your database performance.

Understanding the Script:

The PowerShell script utilizes the sys.dm_exec_query_stats and sys.dm_exec_sql_text dynamic management views (DMVs) to gather vital information about query execution statistics and their associated SQL text. It then processes and organizes this data into five distinct categories: AvgWorkerTime, AvgDOP (Degree of Parallelism), AvgLogicalReads, AvgPhysicalReads, and AvgRows. Each category represents a crucial aspect of query performance, providing valuable insights into query efficiency, resource utilization, and execution frequency.

Exploring the Key Components:

1. Invoke-SqlCommandWithRetry Function: The script includes a robust function called Invoke-SqlCommandWithRetry, responsible for establishing and maintaining a connection to the Azure SQL Database. This function incorporates a retry mechanism, which ensures that the script can gracefully handle transient connection issues or query timeouts. By making use of this function, the script becomes resilient and can withstand intermittent network or database-related disruptions.

2. Querying the Dynamic Management Views (DMVs): Within the script, the queries are constructed to retrieve query performance statistics from the sys.dm_exec_query_stats DMV. These statistics include execution counts, total worker time, total elapsed time, logical reads, physical reads, and rows affected. Additionally, the sys.dm_exec_sql_text DMV is employed to extract the actual SQL text associated with each query for better visibility.

3. Categorization and Sorting: The script groups the query performance data based on the five key categories mentioned earlier. It calculates average values for each category, allowing database administrators to identify performance bottlenecks and areas of improvement. The script then sorts the data within each category in descending order, displaying the top 25 queries with the highest average values.

4. Displaying the Results: To present the data in a user-friendly format, the script employs PowerShell's Out-GridView cmdlet. This cmdlet opens a graphical window (GridView) that displays the categorized query performance statistics. The GridView allows users to sort and filter the data interactively, providing an easy-to-read summary of query performance metrics.

The Significance of Monitoring Query Performance:

Monitoring query performance is critical for optimizing database operations and ensuring efficient resource utilization. By tracking key performance indicators (KPIs) such as average worker time, logical reads, and parallelism, database administrators can pinpoint poorly performing queries and take corrective actions to enhance database efficiency.

Conclusion:

The PowerShell Activity Monitor script for Azure SQL Database provides a valuable tool for database administrators to keep a close watch on query performance. With its ability to categorize and sort query execution statistics, this script allows for informed decision-making, better resource management, and enhanced overall database performance. By regularly monitoring query performance, you can proactively address performance issues, optimize queries, and ensure that your Azure SQL Database operates at its peak efficiency.

PowerShell Script

$ServerName = 'YourServer.database.windows.net'
$DatabaseName = 'YourDatabase'
$Username = 'YourUsername'
$Password = 'YourPassword'

$IntervalInSeconds = 5
$RetryAttempts = 3

function Invoke-SqlCommandWithRetry {
    param(
        [string]$ConnectionString,
        [string]$Query,
        [hashtable]$Parameters
    )

    for ($attempt = 1; $attempt -le $RetryAttempts; $attempt++) {
        try {
            $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
            $connection.ConnectionString = $ConnectionString
            $connection.Open()

            $command = $connection.CreateCommand()
            $command.CommandText = $Query

            foreach ($key in $Parameters.Keys) {
                $parameter = New-Object -TypeName System.Data.SqlClient.SqlParameter
                $parameter.ParameterName = $key
                $parameter.Value = $Parameters[$key]
                $command.Parameters.Add($parameter)
            }

            $dataTable = New-Object -TypeName System.Data.DataTable
            $dataAdapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter $command
            [void]$dataAdapter.Fill($dataTable)

            return $dataTable
        }
        catch {
            if ($attempt -eq $RetryAttempts) {
                throw
            }

            Write-Host "Retrying... Attempt $attempt"
            Start-Sleep -Seconds 5
        }
        finally {
            if ($connection.State -ne [System.Data.ConnectionState]::Closed) {
                $connection.Close()
            }
        }
    }
}

function Show-CustomGridView {
    param (
        [Parameter(Mandatory=$true)]
        [System.Data.DataTable]$DataTable
    )

    $scriptBlock = {
        param ($DataTable)
        $gridView = $DataTable | Out-GridView -Title "Query Statistics" -OutputMode Multiple
        return $gridView
    }

    Start-Job -ScriptBlock $scriptBlock -ArgumentList $DataTable | Wait-Job | Receive-Job
}

$connectionString = "Server=$ServerName;Database=$DatabaseName;User Id=$Username;Password=$Password;"

$orderBy = @(
    "AvgWorkerTime DESC",
    "AvgDOP DESC",
    "AvgLogicalReads DESC",
    "AvgPhysicalReads DESC",
    "AvgRows DESC"
)

$categories = @(
    "AvgWorkerTime",
    "AvgDOP",
    "AvgLogicalReads",
    "AvgPhysicalReads",
    "AvgRows"
)

$gridWindow = $null

while ($true) {
    Write-Host "Starting a new cycle at $(Get-Date)"

    $tableData = @()

    foreach ($category in $categories) {
        $query = @"
SELECT TOP 25
    '${category}' AS [Category],
    CAST(SUBSTRING(t.text, (qs.statement_start_offset / 2) + 1,
                 ((CASE qs.statement_end_offset
                     WHEN -1 THEN DATALENGTH(t.text)
                     ELSE qs.statement_end_offset
                  END - qs.statement_start_offset) / 2) + 1) AS NVARCHAR(MAX)) AS [SQL Text],
    qs.execution_count AS ExecutionCount,
    AVG(qs.total_worker_time / qs.execution_count) AS AvgWorkerTime,
    AVG(qs.total_elapsed_time / qs.execution_count) AS AvgElapsedTime,
    AVG(qs.total_logical_reads / qs.execution_count) AS AvgLogicalReads,
    AVG(qs.total_physical_reads / qs.execution_count) AS AvgPhysicalReads,
    AVG(qs.total_rows / qs.execution_count) AS AvgRows,
    AVG(qs.total_dop / qs.execution_count) AS AvgDOP
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
GROUP BY CAST(SUBSTRING(t.text, (qs.statement_start_offset / 2) + 1,
                 ((CASE qs.statement_end_offset
                     WHEN -1 THEN DATALENGTH(t.text)
                     ELSE qs.statement_end_offset
                  END - qs.statement_start_offset) / 2) + 1) AS NVARCHAR(MAX)), qs.execution_count
ORDER BY ${category};
"@

        Write-Host "Running query for category '$category' at $(Get-Date)"
        $dataTable = Invoke-SqlCommandWithRetry -ConnectionString $connectionString -Query $query -Parameters @{}

        if ($dataTable -ne $null -and $dataTable.Rows.Count -gt 0) {
            $tableData += $dataTable
        }
    }

    if ($gridWindow -eq $null) {
        Write-Host "Opening the Grid for the first time at $(Get-Date)"
        $gridWindow = $tableData | Out-GridView -Title "Query Statistics" -OutputMode Multiple
    }
    else {
        # Clear the grid content before showing the new data
        Write-Host "Refreshing the Grid at $(Get-Date)"
        $gridWindow = $tableData | Out-GridView -Title "Query Statistics" -OutputMode Multiple
    }

    Write-Host "Cycle completed at $(Get-Date)"
    Write-Host "Waiting for the next cycle..."
    Start-Sleep -Seconds $IntervalInSeconds
}