Blog Post

Azure Database Support Blog
4 MIN READ

Lesson Learned #405: How many TSQL operations you said?

Jose_Manuel_Jurado's avatar
Jul 24, 2023

In today's data-centric landscape, Azure SQL Database has become a preferred choice for organizations seeking to store, manage, and access vast amounts of data efficiently and securely. As businesses rely heavily on this cloud-based database solution, questions naturally arise about its operational capacity and connection performance. To provide clarity on these crucial aspects, we would like share this PowerShell script as an example that quantifies the number of operations possible and measures the time spent while connecting to Azure SQL Database.

 

The Script's Goal:

 

The primary objective of this compelling PowerShell script is to empower users to gain a deeper understanding of Azure SQL Database's performance by determining the number of operations that can be executed while connecting to the database and measuring the time taken for these operations. By running a controlled series of queries and meticulously tracking connection time, the script allows users to grasp the database's operational boundaries and performance characteristics.

 

Quantifying Operations and Connection Time:

 

As the script runs the SQL query for the designated duration, it records essential metrics. The results encompass the total number of successful query executions, which is then translated into the number of operations achievable while connecting to Azure SQL Database.

 

Empowering Informed Decision-Making:

 

Upon completing the execution phase, the script presents a report. This insightful summary encompasses critical information, including the total number of query executions, the average number of operations per connection, maximum and minimum execution times, and the average execution time. Armed with these data-driven insights, users can make informed decisions, optimizing their Azure SQL Database configurations and enhancing overall application performance.

 

Script:

 

 

# Parameters for connecting to the database
$serverName = "servername.database.windows.net"
$databaseName = "dbName"
$username = "UserName"
$password = "Password"

# SQL query to be executed
$query = "SELECT 1"

# Execution time (in minutes)
$executionTimeInMinutes = 2

# Connection retry policy
$maxRetries = 5
$retryIntervalInSeconds = 10

# Function to establish a connection to Azure SQL Database with retry policy
function Connect-AzureSQLWithRetry {
    param(
        [string]$serverName,
        [string]$databaseName,
        [string]$username,
        [string]$password,
        [int]$maxRetries,
        [int]$retryIntervalInSeconds
    )

    $retryCount = 0
    $connectionStartTime = Get-Date

    while ($retryCount -lt $maxRetries) {
        try {
            $connectionString = "Server=tcp:$serverName,1433;Initial Catalog=$databaseName;User ID=$username;Password=$password;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
            $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
            $sqlConnection.ConnectionString = $connectionString
            $sqlConnection.Open()
            $connectionEndTime = Get-Date
            Write-Host "Connection to the database successful."
            $connectionTimeInSeconds = ($connectionEndTime - $connectionStartTime).TotalSeconds
            Write-Host "Time spent on establishing the connection: $connectionTimeInSeconds seconds."
            return $sqlConnection
        }
        catch {
            Write-Host "Error connecting to the database. Will retry in $retryIntervalInSeconds seconds..."
            $retryCount++
            Start-Sleep -Seconds $retryIntervalInSeconds
        }
    }

    Write-Host "Failed to establish the connection after $maxRetries attempts."
    return $null
}

# Function to execute a SQL query and get the result with error handling
function Execute-SqlQuery {
    param(
        [System.Data.SqlClient.SqlCommand]$sqlCommand
    )

    try {
        $result = $sqlCommand.ExecuteScalar()
        return $result
    }
    catch {
        Write-Host "Error executing the SQL query: $_.Exception.Message"
        return $null
    }
}

# Start of the script

# Record the start time of the test
$testStartTime = Get-Date
Write-Host "Test started at: $testStartTime"

# Establish the connection to the database with retry
$sqlConnection = Connect-AzureSQLWithRetry -serverName $serverName -databaseName $databaseName -username $username -password $password -maxRetries $maxRetries -retryIntervalInSeconds $retryIntervalInSeconds

if ($sqlConnection -eq $null) {
    Write-Host "Failed to connect to the database. The script will stop."
    return
}

# Initialize variables for metrics and time tracking
$totalExecutions = 0
$minExecutionTimeInSeconds = [double]::MaxValue
$maxExecutionTimeInSeconds = 0

$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = $query

# Execute the query for the specified time
while ((Get-Date) -lt ($testStartTime.AddMinutes($executionTimeInMinutes))) {
    $queryStartTime = Get-Date
    $result = Execute-SqlQuery -sqlCommand $sqlCommand
    $queryEndTime = Get-Date

    $executionTimeInSeconds = ($queryEndTime - $queryStartTime).TotalSeconds

    # Ensure the query executed successfully before processing the result
    if ($result -ne $null) {
        $totalExecutions += [int]$result

        # Update min and max execution times
        if ($executionTimeInSeconds -lt $minExecutionTimeInSeconds) {
            $minExecutionTimeInSeconds = $executionTimeInSeconds
        }
        if ($executionTimeInSeconds -gt $maxExecutionTimeInSeconds) {
            $maxExecutionTimeInSeconds = $executionTimeInSeconds
        }
    }
}

# Record the end time of the test
$testEndTime = Get-Date
Write-Host "Test finished at: $testEndTime"

# Calculate execution time in seconds
$executionTimeInSeconds = ($testEndTime - $testStartTime).TotalSeconds

# Calculate metrics
$averageExecutionTimeInSeconds = $executionTimeInSeconds / $totalExecutions
$metrics = @{
    "TotalExecutions" = $totalExecutions
    "ExecutionPerMinute" = $totalExecutions / $executionTimeInMinutes
    "MaxExecutionTimeInSeconds" = $maxExecutionTimeInSeconds
    "MinExecutionTimeInSeconds" = $minExecutionTimeInSeconds
    "AverageExecutionTimeInSeconds" = $averageExecutionTimeInSeconds
}

# Display the results
Write-Host "Results:"
Write-Host "Total executions: $($metrics['TotalExecutions'])"
Write-Host "Executions per minute (average): $($metrics['ExecutionPerMinute'])"
Write-Host "Max execution time (seconds): $($metrics['MaxExecutionTimeInSeconds'])"
Write-Host "Min execution time (seconds): $($metrics['MinExecutionTimeInSeconds'])"
Write-Host "Average execution time (seconds): $($metrics['AverageExecutionTimeInSeconds'])"

# Close the database connection
$sqlConnection.Close()

 

 

Variables Explanation

 

1. $serverName: Stores the name of the Azure SQL Database server to which the script will connect.

2. $databaseName: Holds the name of the specific database within the server that the script will query.

3. $username: Stores the username used for authenticating the connection to the database.

4. $password: Holds the password associated with the specified username for authentication.

5. $query: Contains the SQL query that will be executed repeatedly during the test.

6. $executionTimeInMinutes: Represents the duration (in minutes) for which the SQL query will be executed repeatedly. This variable determines the total time for the test.

7. $maxRetries: Defines the maximum number of connection retries allowed in the connection retry policy.

8. $retryIntervalInSeconds: Specifies the time interval (in seconds) between connection retry attempts in the connection retry policy.

9. $totalExecutions: Keeps track of the total number of successful query executions.

10. $minExecutionTimeInSeconds: Records the minimum execution time (in seconds) observed during the test.

11. $maxExecutionTimeInSeconds: Records the maximum execution time (in seconds) observed during the test.

12. $sqlCommand: Represents a System.Data.SqlClient.SqlCommand object that will be used to execute the SQL query.

 

Enjoy!

Updated Jul 24, 2023
Version 1.0
No CommentsBe the first to comment