Challenge your Database – MS SQL (part. 1)

Recently I had to demonstrate that the gaps of response times between 2 platforms weren’t caused by the application itself; pretty obvious because it’s the same application, but sometimes you’re not believed as long as you don’t give measured data.
So I wrote this script, to check how many SQL queries can be runned during a defined time. To compare the results between the two platforms, and prove that because the queries (and databases of course) are the same, the gaps of response times comes from something else that what runs the queries…

NB: The SQL queries are integrated in the script, in an array ‘$Queries=@(“SELECT COUNT(*) AS STAT FROM Table;”,”SELECT * FROM Table;”)’, but it would be pretty easy to get them from a txt file…

<#
.SYNOPSIS
    MSSqlLoopBenchmark.ps1 - Runs a few SQL queries against an MS SQL database
.DESCRIPTION
    MSSqlLoopBenchmark.ps1 - Runs a few SQL queries against an MS SQL database during 10 seconds for each (default value, but the parameter 'QueriesDuration' allows to change the default duration) and generate an HTML report.
    The purpose of this script is to determine how many queries can be runned during one minute (nb queries * $QueriesDuration) in order to test response times.
.PARAMETER QueriesDuration
    Defines how long each SQL query will be run
    Default is 10 seconds.
.PARAMETER DbHost
    Defines the MS SQL servername or IP address
    Default is localhost.
.PARAMETER DbInstance
    Defines the MS SQL server instance name, if exists
    No default value.
.PARAMETER DbPort
    Defines the MS SQL server port
    Default is 1433.
.PARAMETER DbName
    Defines the database name to connect to
    Mandatory parameter
    No default value.
.PARAMETER DbUser
    Defines the user id to use for authentication
    Mandatory parameter
    No default value.
.NOTES
    File Name   : MSSqlLoopBenchmark.ps1
    Author      : Fabrice ZERROUKI - fabricezerrouki@hotmail.com
.EXAMPLE
    PS D:\> .\MSSqlLoopBenchmark.ps1 -DbHost SQLSERVER.domain.com -DbPort 12200 -DbName MYDATABASE -DbUser sa
    Run the test against database MYDATABASE with user id 'sa' on SQLSERVER.domain.com DB Server (listening on TCP12200 port).
.EXAMPLE
    PS D:\> .\MSSqlLoopBenchmark.ps1 -DbHost SQLSERVER.domain.com -DbInstance FirstInstance -DbPort 12200 -DbName MYDATABASE -DbUser sa
    Run the test against database MYDATABASE with user id 'sa' on SQLSERVER.domain.com\FirstInstance DB Server (listening on TCP12200 port).
.EXAMPLE
    PS D:\> .\MSSqlLoopBenchmark.ps1 -QueriesDuration 100 -DbName MYDATABASE -DbUser sa
    Alter the default value for the QueriesDuration parameter (10 seconds) to run each query during 100 seconds against database MYDATABASE with user id 'sa'.
#>
Param(
    $QueriesDuration=10,
    $DbHost="localhost",
    $DbInstance,
    $DbPort="1433",
    [Parameter(Mandatory=$true)]
    $DbName,
    [Parameter(Mandatory=$true)]
    $DbUser    
)
$ErrorActionPreference="SilentlyContinue"
$ScriptVersion="1.0"
$ScriptName=($MyInvocation.MyCommand).Name
$ScriptName=$ScriptName -replace ".ps1",""
$Logfile=".\${ScriptName}.log"
function LogWrite
{
   Param ([string]$logstring)
   Add-Content $Logfile -Value $logstring
}

function Check-SessionArch {
	if ([System.IntPtr]::Size -eq 8) { return "x64" }
	else { return "x86" }
}
$SessionArch=Check-SessionArch

# Basic customizing the PowerShell console
$Shell=$Host.UI.RawUI
$Shell.WindowTitle="Benchmark - MS SQL Loop ($SessionArch)"
$size=$Shell.BufferSize
$size.width=120
$size.height=3000
$Shell.BufferSize=$size
$size=$Shell.WindowSize
$size.width=120
$size.height=30
$Shell.WindowSize=$size

function Test-Port {
$tcpclient=New-Object System.Net.Sockets.TcpClient
$iar=$tcpclient.BeginConnect($DbHost, $DbPort, $null, $null)
$wait=$iar.AsyncWaitHandle.WaitOne(5000, $false)
if (!$wait)
{
$tcpclient.Close()
$failed=$true
}
else
{
$Error.Clear()
$tcpclient.EndConnect($iar) | Out-Null
if ($Error[0]) {
Write-Host $Error[0] -ForegroundColor DarkYellow
$failed=$true
}
$tcpclient.Close()
}
if($failed){$false} else {$true}
} #EndOf function Test-Port
# Checking if the specified (or default value is not specified) DbHost answers on the DbPort
if(!(Test-Port)) {Write-Host "`nUnable to connect to ${DbHost}:${DbPort}. Exiting now." ; Break}

Write-Host "`nConnection parameters definition" -ForegroundColor Yellow
# Getting the password to connect to the database
Write-Host "Please provide the " -NoNewline
Write-Host "Password" -ForegroundColor Yellow -NoNewline
Write-Host " you would like to use with the `"$DbUser`" account: " -NoNewline
$DbPassword=Read-Host -AsSecureString
$Ptr=[System.Runtime.InteropServices.Marshal]::SecureStringToCoTaskMemUnicode($DbPassword)
$DbSecurePassword=[System.Runtime.InteropServices.Marshal]::PtrToStringUni($Ptr)
[System.Runtime.InteropServices.Marshal]::ZeroFreeCoTaskMemUnicode($Ptr)
Write-Host "`n"

if ($DbInstance) {$DbServer="${DbHost}\${DbInstance}"} else {$DbServer="$DbHost"}
$DatabaseInfo=@"
Database Host: ${DbServer}:${DbPort}<br />
Database Name: $DbName<br />
"@

# Writing the used parameters in the logfile
$StartScript=Get-Date -format "MM-dd-yyyy %H:mm:ss"
LogWrite ("******************************")
LogWrite ("$StartScript - [START]")
LogWrite ("Architecture: $SessionArch")
LogWrite ("Queries duration: $QueriesDuration")
LogWrite (($DatabaseInfo -replace "<br />","`r"))
LogWrite ("Database UserId: $DbUser")
LogWrite ("`r")
LogWrite ("-------------------")

function SqlServerQuery ($Query) {
$SqlConnection=New-Object -TypeName System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString="Data Source=$DbServer,$DbPort;Network Library=DBMSSOCN;Initial Catalog=$DbName;User Id=$DbUser;Password=$DbSecurePassword;"
$SqlCmd=New-Object -TypeName System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText=$Query
$SqlCmd.Connection=$SqlConnection
$SqlAdapter=New-Object -TypeName System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand=$SqlCmd
$DataSet=New-Object -TypeName System.Data.DataSet
$SqlAdapter.Fill($DataSet) | Out-Null
$SqlConnection.Close()
$DataSet.Tables[0].Rows.Count
} # EndOf function SqlServerQuery ($Query)

# Defining the SQL queries
$Queries=@("SELECT COUNT(*) AS STAT FROM Table;","SELECT * FROM Table;")
$QueriesLength=$Queries.Length
$TestDuration="$QueriesLength queries, executed for at least $QueriesDuration seconds each"
$TotalQueries=0

# Initializing the HTML report
$date=(Get-Date).ToString('dd/MM/yyyy')
$ReportHeader=@" 
<html> 
<head> 
<meta http-equiv='Content-Type' content='text/html;'> 
<meta name='description' content='MSSqlLoopBenchmark.ps1 - Runs a few SQL queries against an MS SQL database in order to test reponse times.'>
<meta name='author' content='Fabrice ZERROUKI, fabricezerrouki@hotmail.com'>
<title>Benchmark - MS SQL Loop</title> 
<style type="text/css"> 
body {font-family: Verdana, Arial, Helvetica, sans-serif; color:#666666; text-align:center; line-height:90%;}
p {font-size: 13px; line-height: 1em;}
#pagewidth{width:80%; text-align:left;  margin:0 auto;} 
#maincol{background-color: #FFFFFF; position: relative;}
.clearfix:after {content: "."; display: block; height: 0; clear: both; visibility: hidden;}
.clearfix{display: inline-block;}
/* Hides from IE-mac \*/
* html .clearfix{height: 1%;}
.clearfix{display: block;}
/* End hide from IE-mac */  
table.tableizer-table {border: 1px solid #53A9FF; font-family: Verdana, Verdana, Geneva, sans-serif; font-size: 11px; word-wrap:break-word;}
.tableizer-table td {padding: 2px; margin: 1px; border: 1px solid #53A9FF;}
.tableizer-table th {padding: 2px; margin: 1px; border: 1px solid #53A9FF; background-color: #53A9FF; color: #FFF; font-weight: bold;}
</style>
"@

$TestReport=@"
<div id='pagewidth' >
<div id='wrapper' class='clearfix'>
<div id='maincol'>
<span style=' float:left; font-weight:normal; color:#FF7444; font-size:24px; margin:0px; padding:5px 0 5px 0;'>Benchmark</span><br />&nbsp;<br />
<span style=' float:left; font-weight:normal; color:#FF7444; font-size:20px; margin:0px; padding:5px 0 5px 0;'>MS SQL Loop - $date</span>
<br />&nbsp;<br />
<p><span style='font-size:11px;'><strong>Parameters</strong><br />
Script Version: $ScriptVersion<br />
PowerShell Session: $SessionArch<br />
Test duration: $TestDuration
<br />&nbsp;<br />
$DatabaseInfo
Database UserId: $DbUser</span></p>
<br />
<table class='tableizer-table' width='100%' rules='cols'>
<tr class='tableizer-firstrow'> 
<th align='left' valign='top' width='30%'><strong>QUERY</strong></th> 
<th align='left' valign='top'><strong>NB PASSED</strong></th> 
<th align='left' valign='top'><strong>LINES RETURNED</strong></th>
<th align='left' valign='top'><strong>START TIME</strong></th> 
<th align='left' valign='top'><strong>END TIME</strong></th>
<th align='left' valign='top'><strong>DURATION</strong></th>
<th align='left' valign='top'><strong>AVERAGE</strong></th>
</tr>
"@

# Launching the test
$j=1
$TotalStartTime=Get-Date
ForEach($Query in $Queries)
{
Write-Progress -Activity "Running the SQL queries. Please wait..." -Status "Progress ->" -PercentComplete ($j/$Queries.Length*100) -CurrentOperation "Currently querying: $Query"
$j++

# Let's run the loop
$StartTime=Get-Date
$TimeOut=New-TimeSpan -Seconds $QueriesDuration
$Sw=[Diagnostics.Stopwatch]::StartNew()
$i=0
While ($Sw.Elapsed -lt $TimeOut)
{
    $i++
    Try
    {
        $linesCount=SqlServerQuery $Query
    }
    Catch [system.exception]
    {
        # Writing the exception in the console
        Write-Host $_.Exception
        # Writing the exception in the logfile
        $ErrorTime=Get-Date -format "MM-dd-yyyy %H:mm:ss"
        LogWrite ("$ErrorTime - [ERROR] $_")
        LogWrite ("******************************")
        LogWrite ("`r`n")
        Exit;
    }
} #EndOf While ($Sw.Elapsed -lt $TimeOut)
$EndTime=Get-Date
$Duration=$EndTime-$StartTime

# Counting the average rate queries/second
$stat=(New-TimeSpan -Start $StartTime -End $EndTime).TotalMilliseconds
$stats=[System.Math]::Round(($i/$stat)*1000,3)

# Writing the query and the loop results in the logfile
$InfoTime=Get-Date -format "MM-dd-yyyy %H:mm:ss"
LogWrite ("$InfoTime - [INFO] Query: $Query")
LogWrite ("$InfoTime - [INFO] $i queries executed returning $linesCount lines`t|`tAvg: $stats queries/second")
LogWrite ("$InfoTime - [INFO] Start: $StartTime`t`t`t|`tEnd: $EndTime`t|`tDuration=$Duration`n")
# Displaying the query and the loop results in the console
Write-Host "$Query"
Write-Host "Test finished! $i queries executed returning $linesCount lines. (Avg: $stats queries/second)" -ForegroundColor DarkGreen
Write-Host "Start: $StartTime`t|`tEnd: $EndTime`t|`tDuration=$Duration`n" -ForegroundColor Yellow

$TotalQueries+=$i
$TotalLines+=$linesCount
$TestReport+=@"
<tr>
<td align='left' valign='top' cellpadding='2'>$Query</td>
<td align='left' valign='top' cellpadding='2'>$i</td>
<td align='left' valign='top' cellpadding='2'>$linesCount</td>
<td align='left' valign='top' cellpadding='2'>$StartTime</td>
<td align='left' valign='top' cellpadding='2'>$EndTime</td>
<td align='left' valign='top' cellpadding='2'>$Duration</td>
<td align='left' valign='top' cellpadding='2'>$stats queries/s</td>
</tr>
"@
} # EndOf ForEach($Query in $Queries)
$TotalEndTime=Get-Date
$TotalDuration=$TotalEndTime-$TotalStartTime

# Counting the average rate queries/second
$Totalstat=(New-TimeSpan -Start $TotalStartTime -End $TotalEndTime).TotalMilliseconds
$Totalstats=[System.Math]::Round(($TotalQueries/$Totalstat)*1000,3)

# Let's do the sums
$TestReport+=@"
<tr height='1'>
<th align='left' valign='top'></th> 
<th align='left' valign='top'></th> 
<th align='left' valign='top'></th>
<th align='left' valign='top'></th> 
<th align='left' valign='top'></th>
<th align='left' valign='top'></th>
<th align='left' valign='top'></th>
</tr>
<tr>
<td align='left' valign='top' cellpadding='2'>TOTAL</td>
<td align='left' valign='top' cellpadding='2'>$TotalQueries</td>
<td align='left' valign='top' cellpadding='2'>$TotalLines</td>
<td align='left' valign='top' cellpadding='2'>$TotalStartTime</td>
<td align='left' valign='top' cellpadding='2'>$TotalEndTime</td>
<td align='left' valign='top' cellpadding='2'>$TotalDuration</td>
<td align='left' valign='top' cellpadding='2'>$Totalstats queries/s</td>
</tr>
"@
$TestReport+="</table><span style='font-size:10px; float:right;'>Copyright &copy; 2013 PS FAB:\>. All Rights Reserved</span></div></div></div>"

# Generating the HTML report
ConvertTo-HTML -Body $TestReport -Head $ReportHeader -Title "Benchmark - MS SQL Loop Report" | Out-File MSSqlLoopBenchmark.html -encoding UTF8
# Opening the HTML report in the default browser
Invoke-Item MSSqlLoopBenchmark.html

$CompletionTime=Get-Date -format "MM-dd-yyyy %H:mm:ss"
LogWrite ("-------------------`r")
LogWrite ("$CompletionTime - [INFO] Script execution completed successfully!")
LogWrite ("$CompletionTime - [END]")
LogWrite ("******************************")
LogWrite ("`r`n")