Lesson Learned #415:PerfStat: Performance Stats Collection for Azure SQL Managed Instance

[Guest Jose_Manuel_Jurado]

Following the two articles published some days ago: Lesson Learned #412:PerfStat: Performance Stats Collection for SQL Server (OnPrem) Using DiagManager - Microsoft Community Hub and Lesson Learned #413:PerfStat: Performance Stats Collection for Azure SQL Database - Microsoft Community Hub

 

 

 

Today, I would like to share the script adapted for Azure SQL Managed Instance, the script is the same that we have located here: https://sqlperfstats.blob.core.windows.net/zip/sql-azure-perf-stats/sql-azure-perf-stats.zip but I changed the PowerShell to use Azure SQL Managed Instance.

 

 

 

Once your followed the instrucctions given in our article: Lesson Learned #413:PerfStat: Performance Stats Collection for Azure SQL Database - Microsoft Community Hub we are going to modify the PerfStats.ps1 for the following new one:

 

 

 

 

 

param(

[Parameter(Mandatory=$true, HelpMessage="Enter the Server Name without ")][string]$ServerName,

[Parameter(Mandatory=$true, HelpMessage="Enter the Virtual Name ")][string]$VirtualName,

[Parameter(Mandatory=$true)][string]$Database,

[Parameter(Mandatory=$true)][string]$Username,

[Parameter(Mandatory=$true)][string]$Password,

[int32]$DelayInSeconds=10,

[int32]$Port=1433,

[int32]$Public=0

)

 

# Convert seconds to hours:minutes:seconds for WAITFOR DELAY format

$seconds = $DelayInSeconds

$minutes = 0

$hours = 0

 

if ($seconds -ge 60) {

$minutes = $seconds/60

$seconds = $seconds%60

if ($minutes -ge 60) {

$hours = $minutes/60

$minutes = $minutes%60

}

}

$delayString = $hours.ToString("00") + ":" + $minutes.ToString("00") + ":" + $seconds.ToString("00")

 

# Add protocol, FQDN, and Port If US gov is selected use that FQDN

if($Public -eq 0)

{

$serverFQDN = "tcp:$($ServerName).$($VirtualName).database.windows.net,$($Port)"

}

else

{

$serverFQDN = "tcp:$($ServerName).public.$($VirtualName).database.windows.net,$($Port)"

}

 

$fullUsername = $Username

 

If (!(Test-Path "output")) {

New-Item -Name "output" -ItemType directory | Out-Null

}

 

$outputFile = ".\output\$($ServerName)_SQL_Azure_Perf_Stats_Snapshot_BeforeCapture.txt"

$perfStatsSnapshotScript = ".\SQL_Azure_Perf_Stats_Snapshot.sql"

Write-Host "Intial DB Snapshot running in background..."

Start-Process -NoNewWindow "sqlcmd" "-S $serverFQDN -d $Database -U $fullUsername -P $Password -i $perfStatsSnapshotScript -o $outputFile -w 65535"

 

$variableArray = 'delayvar="'+$delayString+'"'

$outputFile = ".\output\$($ServerName)_SQL_Azure_Perf_Stats.txt"

$perfStatsScript = ".\SQL_Azure_Perf_Stats.sql"

Write-Host "Capture starting, press Ctrl+C to end"

try {

sqlcmd -v $variableArray -S $serverFQDN -d $Database -U $fullUsername -P $Password -i $perfStatsScript -o $outputFile -w 65535

} finally {

Write-Host "Capture finished. You can view the output at $($outputFile)"

 

$outputFile = ".\output\$($ServerName)_SQL_Azure_Perf_Stats_Snapshot_Startup_AfterCapture.txt"

Write-Host "Ending DB Snapshot running in background..."

Start-Process -NoNewWindow "sqlcmd" "-S $serverFQDN -d $Database -U $fullUsername -P $Password -i $perfStatsSnapshotScript -o $outputFile -w 65535"

}

 

 

 

 

 

Explanation of the script modified

 

 

 

- ServerName:

The name of the SQL Azure database server.

- VirtualName:

The virtual name associated with the database server.

- Database:

The name of the database for which performance statistics will be captured.

- Username:

The username used for authenticating to the database.

- Password:

The corresponding password for the user.

- Port:

The port number for the database connection (default: 1433). If we are using public FQDN we need to use 3342.

- Public:

A numeric value indicating whether to use the public FQDN address of the server (1 to use, 0 to not use).

 

 

 

Examples:

 

 

 

• If we want to run this script using the public FQDN: MyServerName.Public.MyVirtualName.database.windows.net
  

 

- ServerName:

MyServerName

- VirtualName:

MyVirtualName

- Port:

3342

- Public:

1

 

 

• If we want to run this script using the private FQDN: MyServerName.MyVirtualName.database.windows.net
  

 

- ServerName:

MyServerName

- VirtualName:

MyVirtualName

- Port:

1433

- Public:

0

 

 

Enjoy!

 

Continue reading...