Lesson Learned #171: Testing a connection from multiple PowerShell script instances

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, I worked on a very interesting situation. Our customer wants to check a connection but instead of from a single application, they are trying to run a bunch of instance of the same application to simulate a workload.

 

In order to test this situation, I developed this small PowerShell Script doing a simple connection and executing "SELECT 1" command around for 10000 times, but using the option of multithreading of Powershell to execute the same PowerShell script in multiple instances.

 

Basic points: 

 

  • 10 processes running in parallel from a single PC. 
  • Every process will run 1000 running 10 times SELECT 1, running the powershell script DoneThreadIndividual.ps1
  • In every execution we have the time spent in the connection (with/without connection pooling) and execution and save the information in a log file.
  • Everything is customizable, in terms of numbers, number of connections and query to execute.

 

Script to execute a number of Process defined

 

 

Function lGiveID([Parameter(Mandatory=$false)] [int] $lMax) { $Jobs = Get-Job [int]$lPending=0Foreach ($di in $Jobs) { if($di.State -eq "Running") {$lPending=$lPending+1} } if($lPending -lt $lMax) {return $true} return {$false}} try { for ($i=1; $i -lt 2000; $i++) { if((lGiveid(10)) -eq $true) { Start-Job -FilePath "C:\Test\DoneThreadIndividual.ps1" Write-output "Starting Up---" } else { Write-output "Limit reached..Waiting to have more resources.." Start-sleep -Seconds 20 } } } catch { logMsg( "You're WRONG") (2) logMsg($Error[0].Exception) (2) }

 

 

Script for testing - DoneThreadIndividual.ps1

 

 

$DatabaseServer = "servername.database.windows.net" $Database = "dbname" $Username = "username" $Password = "password" $Pooling = $true $NumberExecutions =1000 $FolderV = "C:\Test\"function GiveMeSeparator { Param([Parameter(Mandatory=$true)] [System.String]$Text, [Parameter(Mandatory=$true)] [System.String]$Separator) try { [hashtable]$return=@{} $Pos = $Text.IndexOf($Separator) $return.Text= $Text.substring(0, $Pos) $return.Remaining = $Text.substring( $Pos+1 ) return $Return } catch { $return.Text= $Text $return.Remaining = "" return $Return } }#-------------------------------------------------------------- #Create a folder #-------------------------------------------------------------- Function CreateFolder { Param( [Parameter(Mandatory)]$Folder ) try { $FileExists = Test-Path $Folder if($FileExists -eq $False) { $result = New-Item $Folder -type directory if($result -eq $null) { logMsg("Imposible to create the folder " + $Folder) (2) return $false } } return $true } catch { return $false } }#-------------------------------- #Validate Param #--------------------------------function TestEmpty($s) { if ([string]::IsNullOrWhitespace($s)) { return $true; } else { return $false; } }Function GiveMeConnectionSource() { for ($i=1; $i -lt 10; $i++) { try { logMsg( "Connecting to the database...Attempt #" + $i) (1)$SQLConnection = New-Object System.Data.SqlClient.SqlConnection $SQLConnection.ConnectionString = "Server="+$DatabaseServer+";Database="+$Database+";User ID="+$username+";Password="+$password+";Connection Timeout=15" if( $Pooling -eq $true ) { $SQLConnection.ConnectionString = $SQLConnection.ConnectionString + ";Pooling=True" } else { $SQLConnection.ConnectionString = $SQLConnection.ConnectionString + ";Pooling=False" }$SQLConnection.Open() logMsg("Connected to the database...") (1) return $SQLConnection break; } catch { logMsg("Not able to connect - Retrying the connection..." + $Error[0].Exception) (2) Start-Sleep -s 5} } }#-------------------------------- #Log the operations #-------------------------------- function logMsg { Param ( [Parameter(Mandatory=$true, Position=0)] [string] $msg, [Parameter(Mandatory=$false, Position=1)] [int] $Color ) try { $Fecha = Get-Date -format "yyyy-MM-dd HH:mm:ss" $msg = $Fecha + " " + $msg Write-Output $msg | Out-File -FilePath $LogFile -Append $Colores="White" If($Color -eq 1 ) { $Colores ="Cyan" } If($Color -eq 3 ) { $Colores ="Yellow" }if($Color -eq 2) { Write-Host -ForegroundColor White -BackgroundColor Red $msg } else { Write-Host -ForegroundColor $Colores $msg }} catch { Write-Host $msg } }cls$sw = [diagnostics.stopwatch]::StartNew()logMsg("Creating the folder " + $FolderV) (1) $result = CreateFolder($FolderV) If( $result -eq $false) { logMsg("Was not possible to create the folder") (2) exit; } logMsg("Created the folder " + $FolderV) (1)$LogFile = $FolderV + "Results.Log" #Logging the operations.$query = @("SELECT 1")for ($i=0; $i -lt $NumberExecutions; $i++) { try {$SQLConnectionSource = GiveMeConnectionSource #Connecting to the database. if($SQLConnectionSource -eq $null) { logMsg("It is not possible to connect to the database") (2) } else { $SQLConnectionSource.StatisticsEnabled = 1 $command = New-Object -TypeName System.Data.SqlClient.SqlCommand $command.CommandTimeout = 60 $command.Connection=$SQLConnectionSource for ($iQuery=0; $iQuery -lt $query.Count; $iQuery++) { $start = get-date $command.CommandText = $query[$iQuery] $command.ExecuteNonQuery() | Out-Null $end = get-date $data = $SQLConnectionSource.RetrieveStatistics() logMsg("-------------------------") logMsg("Query : " + $query[$iQuery]) logMsg("Iteration : " +$i) logMsg("Time required (ms) : " +(New-TimeSpan -Start $start -End $end).TotalMilliseconds) logMsg("NetworkServerTime (ms): " +$data.NetworkServerTime) logMsg("Execution Time (ms) : " +$data.ExecutionTime) logMsg("Connection Time : " +$data.ConnectionTime) logMsg("ServerRoundTrips : " +$data.ServerRoundtrips) logMsg("-------------------------") } $SQLConnectionSource.Close()} } catch { logMsg( "You're WRONG") (2) logMsg($Error[0].Exception) (2) } } logMsg("Time spent (ms) Procces : " +$sw.elapsed) (2) logMsg("Review: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/provider-statistics-for-sql-server") (2)

 

 

Enjoy!

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.