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

[Jose_Manuel_Jurado]

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

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 Commun... 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!