SQL Server - Automate Tests on Windows

[marcosfreccia]

Hello Everyone!

I am currently trying to create a script to test a bunch of SQL Servers in a new hardware and for that I want to use HammerDB. So far I was able to create the script to build up the schema, which I have named autorun.tcl

puts "SETTING CONFIGURATION"
global complete
proc wait_to_complete {} {
global complete
set complete [vucomplete]
if {!$complete} { after 5000 wait_to_complete } else { exit }
}
puts "SETTING CONFIGURATION"
dbset db mssqls
diset connection mssqls_server Instance001
diset tpcc mssqls_count_ware 1
diset tpcc mssqls_num_vu 1
buildschema
wait_to_complete
vwait forever

This is working perfectly when I invoke a build.bat that contains hammerdbcli.bat auto autorun.tcl

Now, I am trying to automate the second part, which is to load the script, create the virtual users and perform the workload test against the created schema/data. For this step in specific I have created the following script, which I took from the documentation.

#!/bin/tclsh
puts "SETTING CONFIGURATION"
global complete
proc wait_to_complete {} {
global complete
set complete [vucomplete]
if {!$complete} { after 5000 wait_to_complete } else { exit }
}
puts "SETTING CONFIGURATION"
dbset db mssqls
diset connection mssqls_server Instance001
diset tpcc mssqls_count_ware 1
diset tpcc mssqls_num_vu 1
print dict
vuset logtotemp 1
loadscript
puts "SEQUENCE STARTED"
foreach z { 1 2 4 } {
puts "$z VU TEST"
vuset vu $z
vucreate
vurun
runtimer 120
vudestroy
after 5000
}
wait_to_complete
vwait forever
puts "TEST SEQUENCE COMPLETE"

This second part somehow is not working when I try to run `hammerdbcli.bat auto loadtest.tcl'. I am certain that I am doing something wrong, I just don't know what.

I appreciate any responses to this.

[sm-shaw]

Hi,

You have a mix of the build and driver scripts in the driver one. in particular you call a function called runtimer but don't include that in the script. So there are a couple of examples below for build and driver tested here that should provide you with a template.

A few things to note. Firstly these scripts are multithreaded so once you do build schema and vurun it is running multiple threads. We need a different approach for both because in the schema build we are waiting for all of the virtual users to finish (vwait forever), however for the driver we are waiting for a set period of time before terminating them. For the build I have created more warehouses but with 5 virtual users to show the multithreaded nature of the build. For the driver we have set a rampup and duration of 3 minutes in total (180 secs) - this runs in the virtual users, then in the main monitor virtual user it runs a timer - this is set to 200 seconds more than the rampup and driver combined (if it is less it will terminate the test before ending).

With both I copied the scripts to C:\Program Files\HammerDB-3.3. I then did:

hammerdbcli.bat auto autorunbuild.tcl

and

hammerdbcli.bat auto autorundrive.tcl

When both are complete the shell will exit as intended.

Build Script - autorunbuild.tcl

puts "SETTING CONFIGURATION"
global complete
proc wait_to_complete {} {
global complete
set complete [vucomplete]
if {!$complete} { after 5000 wait_to_complete } else { exit }
}
puts "SETTING CONFIGURATION"
dbset db mssqls
diset connection mssqls_server {(local)\SQLDEVELOP}
diset tpcc mssqls_count_ware 10
diset tpcc mssqls_num_vu 5
vuset logtotemp 1
buildschema
wait_to_complete
vwait forever

Driver Script - autorundrive.tcl

#!/bin/tclsh
proc runtimer { seconds } {
set x 0
set timerstop 0
while {!$timerstop} {
incr x
after 1000
  if { ![ expr {$x % 60} ] } {
          set y [ expr $x / 60 ]
          puts "Timer: $y minutes elapsed"
  }
update
if {  [ vucomplete ] || $x eq $seconds } { set timerstop 1 }
    }
return
}
puts "SETTING CONFIGURATION"
dbset db mssqls
diset connection mssqls_server {(local)\SQLDEVELOP}
diset tpcc mssqls_driver timed
diset tpcc mssqls_rampup 1
diset tpcc mssqls_duration 2
vuset logtotemp 1
loadscript
puts "SEQUENCE STARTED"
foreach z { 1 2 4 } {
puts "$z VU TEST"
vuset vu $z
vucreate
vurun
#Runtimer in seconds must exceed rampup + duration
runtimer 200
vudestroy
after 5000
}
puts "TEST SEQUENCE COMPLETE"

[marcosfreccia]

Thanks a lot for that @sm-shaw. Interesting enough I had some problems when editing the .tcl files from different editors, but that is of course something on my side.

I will explore different parameters and configurations, but that gives me at least a good starting point.

[sm-shaw]

For an editor on Windows with TCL language syntax colorisation I would recommend vim that can be found here https://www.vim.org/. Using vim you should not have any issues editing files to run in HammerDB.

[sm-shaw]

A copy of these scripts has been posted here https://www.hammerdb.com/blog/uncategorized/automating-cli-tests-on-windows/