How to SQLTest Insert ~ Simple Example
In this example we will cover:
1. Inserting into the Orders table with OrderID as int identity with single and many threads.
2. Inserting into Orders table with OrderID as int and explicitly supplying OrderID with single and many threads.
3. Inserting into Orders table with OrderID as uniqueidentifier default newid().
4. Inserting into Orders table with OrderID as uniqueidentifier default newsequentialid().
For all of the above examples create a database SQLTestDemo on the target SQL Server, where we want to run the test. If possible, keep the data and the transaction log file on different volumes.
use master
create database SQLTestDemo
go
1. Inserting into the Orders table with OrderID as int identity with single and many threads:
Create Orders table on the target SQL Server:
use SQLTestDemo
go
if exists (select * from sys . objects where name = 'Orders')
drop table Orders
go
create table Orders (OrderID int identity primary key clustered, OrderDate datetime, CustomerID int, SourceID int, StatusID int, Amount decimal (18, 2), OrderDetails char(350))
go
In the SQLClient Connection String(s) RichTextBox enter the target SQL Server connection string:
In my case it is
“Data Source=SQLWorkshops\SQL2014;Database=SQLTestDemo;Integrated Security=true;Pooling=false”.
Change the Data Source name to your target SQL Server name. If you have a default instance installed on your local computer and if you want to use that as target server, then you can leave the Data Source as “(local)”. In addition, change the Database name from “master” to “SQLTestDemo”.
In your case it might be
“Data Source=(local);Database=SQLTestDemo;Integrated Security=true;Pooling=false”
In the SQL Command enter the following statement:
insert into Orders values (getdate (), 1, 1, 1, 1, replicate ('a', 350))
go
Set the number of thread to 1 initially and finally click “Start All” under Workload GroupBox to start the workload.
Below you can see the test ran for 10 seconds, executed 9629 iterations with average database response time of 0.340 milliseconds per iteration.
The test is a bit slow due to displaying record(s) affected messages in “Results Tab”.
Under Settinsg -> Workload Settings Menu, it is recommended to disable “Show Results” and execute the inserts using ExecuteNonQuery instead of ExecuteReader.
Now when we rerun the tests with “Start All”, we can see 21826 iterations has completed within the 10 seconds, the average database time has decreased slightly to 0.259 milliseconds.
Now we can rerun the test with number of threads set to 2. Notice the iterations completed has increased to 41107 with database response time of 0.275 milliseconds.
With 4 and 8 thread the number of iterations completed increase is not linear as we start encountering PAGEIOLATCH_SH and PAGELATCH_EX waits.
PAGEIOLATCH_SH and PAGELATCH_EX waits can be observed with sp_whopro.
This is due to insert contention at last page of the clustered index. This can be avoided be assigning different OrderID range for different threads.
2. Inserting into Orders table with OrderID as int an explicitly supplying OrderID with single and many threads.
To use different OrderID range for different threads use the below SQL Command and also recreate the table without identity attribute:
In the below example we use a range of 100000 rows per thread.
Create Orders table on the target SQL Server:
use SQLTestDemo
go
if exists (select * from sys . objects where name = 'Orders')
drop table Orders
go
create table Orders (OrderID int primary key clustered, OrderDate datetime, CustomerID int, SourceID int, StatusID int, Amount decimal (18, 2), OrderDetails char (350))
go
In the SQL Command enter the following statement:
insert into Orders values (({ SQLTest_Thread } * 100000) + { SQLTest_Iteration }, getdate (), 1, 1, 1, 1, replicate ('a', 350))
go
In SQL Command, {SQLTest_Thread} will be replaced by thread number and {SQLTest_Iteration} will be replaced by iteration number.
The number of iterations decreases as we are using dynamic statement with different OrderID for every iteration. It is recommended to use parameterization functionality of SQLTest to avoid parsing and compilation overhead of these dynamic statements.
3. Inserting into Orders table with OrderID as uniqueidentifier default newid().
The steps are the same as the first example, except the table in the target server needs to be created with OrderID as uniqueidentifier with default newid().
use SQLTestDemo
go
if exists (select * from sys . objects where name = 'Orders')
drop table Orders
go
create table Orders (OrderID uniqueidentifier default newid () primary key clustered, OrderDate datetime, CustomerID int, SourceID int, StatusID int, Amount decimal (18, 2), OrderDetails char (350))
go
4. Inserting into Orders table with OrderID as uniqueidentifier default newsequentialid().
The steps are the same as the first example, except the table in the target server needs to be created with OrderID as uniqueidentifier with default newsequentialid().
use SQLTestDemo
go
if exists (select * from sys . objects where name = 'Orders')
drop table Orders
go
create table Orders (OrderID uniqueidentifier default newsequentialid () primary key clustered, OrderDate datetime, CustomerID int, SourceID int, StatusID int, Amount decimal (18, 2), OrderDetails char (350))
go