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=0 Foreach ($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!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

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