Challenge your Database – MS SQL (part. 1)

sqlserver 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")

Leave a Reply

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

Scroll to Top