Skip to main content

A simple script to test server performance based on number of small SQL transactions


For testing the performance impact of enabling synchronous replica on AlwaysOn 3-node cluster (see http://smizrohi.blogspot.ca/2013/01/sql-2012-alwayson-synchronous-vs.html) I used s simple test script presented here.

The first part of the script is a CMD file called DB_Load.CMD

If you use this script then you need to change the parameters defined by the SET statements within the script:



  1. SRV is a name of the SQL server to connect to (SQLDEVCLUSTER1)
  2. DBNAME is a name of a test database (Test_DB3)
  3. TIMEINTERVAL - defines the initial delay in ShowStats and then each loop is also paused by that amount of seconds
  4. NumOfClients is the number of concurrent sessions
  5. MODE means:


  • MODE=1 means Creating the DB, the Tables, and the Procedures
  • MODE=2 means Creating the Tables, and Procedures (the DB has already been created)
  • MODE=3 means Creating the Procedures (the DB has already been created)


And check if SQLCMD path on you server is the same as defined by the SQLCMD parameter

SET SQLCMD="C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE"


The CMD file
===============
@echo OFF
SETLOCAL

 SET SRV=SQLDEVCLUSTER1

 SET DBNAME=Test_DB3
SET TIMEINTERVAL=5
SET MODE=3

 SET NumOfClients=4

 REM  The TIMEINTERVAL Parameter defines the initial delay in ShowStats and then each loop is also paused by %TIMEINTERVAL% seconds
REM  MODE=1 means Creating the DB, the Tables, and the Procedures
REM  MODE=2 means Creating the Tables, and Procedures (the DB has already been created)
REM  MODE=3 means Creating the Procedures (the DB has already been created)
SET SQLCMD="C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE"
IF NOT EXIST %SQLCMD% GOTO :NoSQLCMD

 REM CREATE %DBNAME% and SPs
%SQLCMD% -M -S %SRV% -E -i "%~dpn0.SQL" -v DBNAME=%DBNAME% TIMEINTERVAL=%TIMEINTERVAL% MODE=%MODE%

 REM Truncates the table with load results
%SQLCMD% -M -S %SRV% -E -Q "EXEC dbo.InitLoadRecords" -d %DBNAME% -o "%~dpn0.LOG" -r1 -v DBNAME=%DBNAME% TIMEINTERVAL=%TIMEINTERVAL%  MODE=%MODE%

 FOR /L %%i in (1,1,%NumOfClients%) DO START "SQLCMD"  /MIN %SQLCMD% -M -S %SRV% -E -Q "EXEC dbo.LoadRecords" -d %DBNAME% -v DBNAME=%DBNAME% TIMEINTERVAL=%TIMEINTERVAL%  MODE=%MODE%

 ECHO.
ECHO Waiting %TIMEINTERVAL% seconds for some records to be logged
TIMEOUT /T %TIMEINTERVAL%  /NOBREAK >nul
ECHO.
ECHO.

 :Mainloop
%SQLCMD% -M -S %SRV% -E -Q "EXEC dbo.ShowStats" -d %DBNAME% -r1 -v DBNAME=%DBNAME% TIMEINTERVAL=%TIMEINTERVAL%  MODE=%MODE%
ECHO.
CHOICE /C YN /M "Press Y to Continue; N to exit"

 IF ERRORLEVEL 2 Goto :EndOfLoop
Goto :MainLoop

 :EndOfLoop
TASKKILL /F /FI "WINDOWTITLE eq SQLCMD*"
GOTO :EOF

 :NoSQLCMD
ECHO SQLCMD.EXE was not found in [%SQLCMD%]
GOTO :EOF
======================


SQL script must have the same name as the CMD file but its extension has to be SQL (so, if the CMD file was named DB_load.cmd then the SQL file must be named DB_Load.SQL).
Place both files in the same folder and execute the CMD file.
Each concurrent user session executes the dbo.LoadRecords stored procedure. 
The stored procedure runs in the endless  loop (While 1=1) runs another inner loop where it generates a random number and if the dbo.T1 table already has a record with ID equals to the generated random number then it updates the existing record otherwise it adds a new one.
the inner loop ends when the number of seconds it has been running exceeds the TIMEINTERVAL parameter (5 seconds by defaults).
And then a new record added to the dbo.LoadStats table with StartTime (as the time when the inner loop started), EndTime (the time when the inner loop ended), and the total number of inserts/updates.

the dbo.ShowStats stored procedure finds the MIN(StartTime), MAX(EndTime), and SUM(TotalUPdates) that occurred in past 2 x TIMEINTERVAL seconds  and then prints out the average number of updates (and inserts) per seconds (it's not very accurate but feel free to improve the code)


===============
/*
:setvar DBNAME TEST_DB
:SETVAR TIMEINTERVAL  5
:SETVAR MODE 1
*/

-- $(MODE) = 1  -- CREATE DATABASE & CREATE TABLES & CREATE PROCEDURES
-- $(MODE) = 2  -- CREATE TABLES & CREATE PROCEDURES
-- $(MODE) = 3  -- CREATE PROCEDURES



USE MASTER;
GO

IF $(MODE) = 1 BEGIN
IF DB_ID (N'$(DBNAME)') IS NOT NULL
BEGIN
ALTER DATABASE [$(DBNAME)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE  
DROP DATABASE [$(DBNAME)];
END
END
GO

IF $(MODE) = 1 BEGIN
CREATE DATABASE [$(DBNAME)];
END
GO

IF $(MODE) = 1 BEGIN
--ALTER DATABASE [$(DBNAME)] SET COMPATIBILITY_LEVEL = 110
ALTER DATABASE [$(DBNAME)] SET RECOVERY SIMPLE
ALTER DATABASE [$(DBNAME)] SET ANSI_NULL_DEFAULT ON
ALTER DATABASE [$(DBNAME)] SET ANSI_NULLS ON
ALTER DATABASE [$(DBNAME)] SET ANSI_PADDING ON
ALTER DATABASE [$(DBNAME)] SET AUTO_CLOSE OFF
ALTER DATABASE [$(DBNAME)] SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE [$(DBNAME)] SET AUTO_SHRINK OFF
ALTER DATABASE [$(DBNAME)] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [$(DBNAME)] SET CURSOR_CLOSE_ON_COMMIT OFF
ALTER DATABASE [$(DBNAME)] SET CURSOR_DEFAULT  GLOBAL
ALTER DATABASE [$(DBNAME)] SET CONCAT_NULL_YIELDS_NULL OFF
ALTER DATABASE [$(DBNAME)] SET NUMERIC_ROUNDABORT OFF
ALTER DATABASE [$(DBNAME)] SET RECURSIVE_TRIGGERS OFF
ALTER DATABASE [$(DBNAME)] SET DISABLE_BROKER
ALTER DATABASE [$(DBNAME)] SET RECOVERY SIMPLE
ALTER DATABASE [$(DBNAME)] SET MULTI_USER
ALTER DATABASE [$(DBNAME)] SET PAGE_VERIFY CHECKSUM
ALTER DATABASE [$(DBNAME)] SET DB_CHAINING OFF
ALTER AUTHORIZATION ON DATABASE::$(DBNAME) To sa
END
GO
USE [$(DBNAME)];
GO
SET QUOTED_IDENTIFIER ON;
GO

IF ($(MODE) = 2) OR ($(MODE) = 1) BEGIN
IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL DROp TABLE dbo.T1;
IF OBJECT_ID (N'dbo.LoadStats', N'U') IS NOT NULL DROP TABLE dbo.LoadStats;
IF OBJECT_ID (N'dbo.StatsHistory', N'U') IS NOT NULL DROP TABLE dbo.StatsHistory;
END
GO

IF ($(MODE) = 2) OR ($(MODE) = 1) BEGIN
CREATE TABLE dbo.T1(
ID INT PRIMARY KEY CLUSTERED,
NumOfUpdates INT DEFAULT(0),
LastSPID INT,
UpdateTime datetime2,
CreateTime datetime2 DEFAULT(SYSDATETIME())
);

END
GO


IF ($(MODE) = 2) OR ($(MODE) = 1) BEGIN
CREATE NONCLUSTERED INDEX [NCI_CreateTime] ON dbo.T1 (CreateTime, ID);
CREATE NONCLUSTERED INDEX [NCI_UpdateTime] ON dbo.T1 (UpdateTime, ID, LastSPID) WHERE UpdateTime IS NOT NULL;
END
GO

IF ($(MODE) = 2) OR ($(MODE) = 1) BEGIN
CREATE TABLE dbo.LoadStats(
ID INT IDENTITY NOT NULL,
StartTime DateTime2 NOT NULL,
EndTime DateTime2 NOT NULL,
TotalUpdates INT NOT NULL,
);
END
GO

IF ($(MODE) = 2) OR ($(MODE) = 1) BEGIN
ALTER TABLE dbo.LoadStats ADD CONSTRAINT [PK_1] PRIMARY KEY CLUSTERED ( ID);
END
GO

IF ($(MODE) = 2) OR ($(MODE) = 1) BEGIN
CREATE TABLE dbo.StatsHistory(
Msg Varchar(256)
);
END
GO


-- ===================  dbo.InitLoadRecords  ============================
IF OBJECT_ID(N'dbo.InitLoadRecords',N'P') IS NOT NULL DROP PROCEDURE dbo.InitLoadRecords;
GO

SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.InitLoadRecords
AS
BEGIN
TRUNCATE TABLE dbo.LoadStats;
END
GO

-- ===================  dbo.LoadRecords  ============================
IF OBJECT_ID(N'dbo.LoadRecords',N'P') IS NOT NULL DROP PROCEDURE dbo.LoadRecords;
GO

SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.LoadRecords
AS
BEGIN

Declare
@wait char(12),
@StartTime Datetime2,
@EndTime DateTime2,
@TotalUpdates Int;

select @wait = '0:0:0.' + CONVERT(VARCHAR, (CONVERT(INT, RAND(datepart(ns, sysdatetime())) * 20000) + ( @@SPID + @@CONNECTIONS ) * 3791) % 1000)
WAITFOR DELAY @wait

SET NOCOUNT ON;

WHILE 1=1
BEGIN
SELECT @StartTime = sysdatetime(), @EndTime = sysdatetime(), @TotalUpdates=0
WHILE DATEDIFF(ss, @StartTime, @EndTime)< $(TIMEINTERVAL)
BEGIN
MERGE dbo.T1 WITH (HOLDLOCK) AS tgt
USING (SELECT CONVERT(INT, RAND(datepart(ns, sysdatetime())) * 2000000) + ( @@SPID + @@TOTAL_READ + @@CONNECTIONS ) * 7953 ) AS src (ID)
ON src.ID = tgt.ID
WHEN MATCHED THEN
UPDATE SET
tgt.LastSPID=@@SPID
, tgt.NumOfUpdates = tgt.NumOfUpdates + 1
, UpdateTime = SYSDATETIME()
WHEN NOT MATCHED THEN
INSERT (ID, LastSPID, CreateTime) VALUES (src.ID, @@SPID, SYSDATETIME() );
SET @TotalUpdates = @TotalUpdates + 1;
SELECT @EndTime = sysdatetime();
END
INSERT INTO dbo.LoadStats (StartTime, EndTime, TotalUpdates) VALUES (@StartTime, @EndTime, @TotalUpdates);
END
END
Go


-- ===================  dbo.ShowStats  ============================
IF OBJECT_ID(N'dbo.ShowStats',N'P') IS NOT NULL DROP PROCEDURE dbo.ShowStats;
GO

SET QUOTED_IDENTIFIER ON;
GO

CREATE PROCEDURE dbo.ShowStats
AS
BEGIN
Declare
@curTime datetime2,
@StartTime datetime2,
@EndTime datetime2,
@TotalUpdates int,
@Delay int,
@Lapse int, 
@PerSec varchar(256),
@str varchar(256);

SET NOCOUNT ON;
SET @Delay = -$(TIMEINTERVAL);
SET @CurTime = SYSDATETIME()
SELECT @StartTime=MIN([StartTime]), @EndTime=MAX([EndTime]), @TotalUpdates=SUM([TotalUpdates])
FROM dbo.LoadStats WITH (NOLOCK)
WHERE [StartTime] >= DATEADD(ss, 2* @Delay, @CurTime); 
IF @StartTime IS NOT NULL BEGIN
SET @Lapse = DATEDIFF(ss, @StartTime, @EndTime);
IF @Lapse = 0  SET @PerSec='N/A' 
ELSE  SET @PerSec = FORMAT(@TotalUpdates/@Lapse, 'N1');
SET @Str = 'The latest modification was at ' + FORMAT(@EndTime, 'HH:mm:ss') +'. In previous ' + FORMAT(@Lapse, 'D') +' secs: Updates=' + FORMAT(@TotalUpdates, 'N0') +' (Avg Per Sec='+@PerSec
PRINT @Str
INSERT INTO dbo.StatsHistory (Msg) VALUES (@Str);
END
ELSE PRINT 'No new records'
END
GO
===================================================




Comments

Post a Comment

Popular posts from this blog

Create 3-Node Windows 2012 Multi-subnet Cluster

Environment There are two Data centers connected via a WAN link. Two Windows 2012 Servers (called SQLDEV1 and SQLDEV2) are located in the Primary Data Center (on the IP subnet 192.168.79.0/24) and the third server is placed in the Secondary Data Center with the 192.168.69.0/24 subnet. We’ll be creating a three-node Windows cluster with no shared storage on the multi subnet network with a file share witness at the Primary Data Center. We’ll be using a file share witness to protect from the cluster failure in a situation when the network between the Data Centers is unavailable and one of the servers in the Primary Data Center is also down (or being rebooted). The final state will look like depicted above: -           Two Virtual IP’s will be assigned (192.168.76.218 and 192.168.69.134) to the cluster -           The servers at the Primary Data Center will have a vote (Vote=1) and the ...
2 comments
Read more

Joining Windows 10 to Azure AD Domain

As of October 2016 to join Windows 10 computers to Azure AD Domain service requires these steps: Create a VNET in the classic portal . The VNET must be placed to a region where Azure AD domain service is available (( https://azure.microsoft.com/en-us/regions/services/ )  In the classic portal  go to Directory -> Configure and enable the domain service. And wait for ~ 30 min When completed the IP address will be populated Go back to the VNET configuration and add a DNS server with the IP (10.0.0.4 in this case) Create the "AAD DC Administrator" administrators group (again in Directory -> Group). Members of this group are granted administrative privileges on machines that are domain-joined to the Azure AD Domain Services managed domain. Add to the group your users who are supposed to have the administrative access on a Windows 10 computer go to Settings -> Accounts (this is true for Windows 10 version  1607) then select 'Access...
1 comment
Read more

SQL 2012 AlwaysOn: Synchronous vs. Asynchronous commit. Performance impact

Recently I've had a chance to build a 3-server AlwaysOn environment distributed between the primary and secondary data centers. The configuration looks like this: Primary Data Center                         Secondary Data Center                        SQLDEV1                                        SQLDEV3          SQLDEV2 The availability group was crated with synchronous commit replicas on SQLDEV1 and SQLDEV2 and the replica on SQLDEV3 was configured for asynchronous commit. The link between the data centers was not great and when I pinged SQLDEV3 from SQLDEV1 I got these results Approximate round trip times in milli-seconds:     Minimum = 39ms, Maximum = 63ms, Average = 42ms I also created a very simp...
4 comments
Read more