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 /> <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 /> <br /> <p><span style='font-size:11px;'><strong>Parameters</strong><br /> Script Version: $ScriptVersion<br /> PowerShell Session: $SessionArch<br /> Test duration: $TestDuration <br /> <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 © 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")