Configuring the SQL Server instance for load testing

Before you start, you should double check that your Windowsfirewallrules are set up to allow traffic from the IP address of the new Windows instance youcreated. Then, create a new database for TPCC load testing and configure a useraccount using the following steps:

1. Right-click theDatabases folder in SQL Server Management Studio, andthen chooseNew Database.
2. Name the new database "TPCC".
3. Set the initial size of the data file to 190,000 MB and the log file to65,000 MB.

4. Set theAutogrowth limits to higher values by clicking the ellipsisbuttons, as shown in the following screenshot:

   

5. Set the data file to grow by 64 MB to unlimited size.

6. Set the log file to disable auto-growth.

7. ClickOK.

8. In theNew Database dialog, in the left pane, choose theOptionspage.

9. SetCompatibility level toSQL Server 2022 (160).

10. Set theRecovery model toSimple, so that the loading doesn't fill upthe transaction logs.

    

11. ClickOK to create the TPCC database, which can take a few minutes tocomplete.

12. The preconfigured SQL Server image comes with only Windows Authenticationenabled, so you will need to enable mixed mode authentication within SSMS, byfollowingthis guide.

13. Follow these steps to create a new SQL Server user account on your database server that has theDBOwner permission. Name the account "loaduser" and give it a securepassword.

14. Take note of your SQL Server internal IP address by using theGet-NetIPAddress commandlet, because it's important for performance and securityto use the internal IP.

Installing HammerDB

You can run HammerDB directly on your SQL Server instance. However, for a moreaccurate test, create a new Windows instance and test the SQL Server instanceremotely.

Creating an instance

Follow these steps to create a new Compute Engine instance:

1. In the Google Cloud console, go to theCreate an instance page.

   Go to Create an instance

2. ForName, enterhammerdb-instance.

3. In theMachine configuration section, select themachine type with at least half the number of CPUs as your databaseinstance.

4. In theBoot disk section, clickChange, and then do the following:

   1. On thePublic images tab, choose a Windows Server operating system.
   2. In theVersion list, clickWindows Server 2022 Datacenter.
   3. In theBoot disk type list, selectStandard persistent disk.
   4. To confirm your boot disk options, clickSelect.

5. To create and start the VM, clickCreate.

Installing the software

When it's ready, use an RDP client to connect to your new Windows Server instanceand install the following software:

• ODBC Driver 18 for SQL Server
• HammerDB for Windows 64-bit

Running HammerDB

After you install HammerDB, run thehammerdb.bat file. HammberDB doesnot show up in the Start menu's applications list. Use the following commandto run HammerDB:

C:\Program Files\HammerDB-VERSION\hammerdb.bat

1. Double-clickSQL Server in theBenchmark panel.
2. SelectTPROC-C.From theHammerDB site:

   TPROC-C is the OLTP workload implemented in HammerDB derived from the TPROC-C specification with modification to make running HammerDB straightforward and cost-effective on any of the supported database environments. The HammerDB TPROC-C workload is an open source workload derived from the TPROC-C Benchmark Standard and as such is not comparable to published TPROC-C results, as the results comply with a subset rather than the full TPROC-C Benchmark Standard. The name for the HammerDB workload TPROC-C means "Transaction Processing Benchmark derived from the TPC "C" specification".

3. ClickOK

   

4. ClickSchema and then double-clickOptions.

5. Fill out the form using your IP address, username, and password as shown in the following image:

   

6. Set theSQL Server ODBC Driver to OBDC Driver 18 for SQL Server

7. In this case, theNumber of Warehouses (the scale) is set to 460, butyou can choose a different value. Some guidelines suggest 10 to 100 warehousesper CPU. For this tutorial, set this value to 10 times the number of cores:160 for a 16-core instance.

8. ForVirtual Users to Build Schema, choose a number that is between 1- and2-times the number of client vCPUs. You can click the grey bar next to theslider to increment the number.

9. Clear theUse BPC Option

10. ClickOK

11. Double-click theBuild option below theSchema Build section tocreate the schema and load the tables. When that completes, click the redflash light icon in the top center of the screen to destroy the virtual userand move to the next step.

Creating the driver script

HammerDB uses the driver script to orchestrate the flow of SQLstatements to the database to generate the required load.

1. In theBenchmark panel, expand theDriver Script section anddouble-clickOptions.
2. Verify the settings match what you used in theSchema Build dialog.
3. ChooseTimed Driver Script.
4. TheCheckpoint when complete option forces the database to writeeverything to disk at the end of the test, so check this only if you plan onrunning multiple tests in a row.
5. To ensure a thorough test, setMinutes of Rampup Time to 5 andMinutes for Test Duration to 20.
6. ClickOK to exit the dialog.
7. Double-clickLoad in theDriver Script section of theBenchmarkpanel to activate the driver script.

Creating virtual users

Creating a realistic load typically requires running scripts as multipledifferent users. Create some virtual users for the test.

1. Expand theVirtual Users section and double-clickOptions.
2. If you set your warehouse count (scale) to 160, then set theVirtual Users to 16, because the TPROC-C guidelines recommend a 10x ratioto prevent row locking. Select theShow Output checkbox to enable errormessages in the console.
3. ClickOK

Collecting runtime statistics

HammerDB and SQL Server don't easily collect detailed runtime statistics foryou. Although the statistics are available deep within SQL Server, they need tobe captured and calculated on a regular basis. If you do not already have aprocedure or tool to help capture this data, you can use the procedure in thissection to capture some useful metrics during your testing. The results will bewritten to a CSV file in the Windowstemp directory. You can copy the data toa Google Sheet using thePaste Special >Paste CSV option.

To use this procedure, you first must temporarily enableOLE Automation Procedures to write the file to disk.Remember to disable it after testing:

sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Ole Automation Procedures', 1;GORECONFIGURE;GO

USE [master]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/***LogFile path has to be in a directory that SQL Server can Write To.*/CREATE PROCEDURE [dbo].[sp_write_performance_counters] @LogFile varchar (2000) = 'C:\\WINDOWS\\TEMP\\sqlPerf.log', @SecondsToRun int =1600, @RunIntervalSeconds int = 2ASBEGIN--File writing variablesDECLARE @OACreate INT, @OAFile INT, @FileName VARCHAR(2000), @RowText VARCHAR(500), @Loops int, @LoopCounter int, @WaitForSeconds varchar (10)--Variables to save last counter valuesDECLARE @LastTPS BIGINT, @LastLRS BIGINT, @LastLTS BIGINT, @LastLWS BIGINT, @LastNDS BIGINT, @LastAWT BIGINT, @LastAWT_Base BIGINT, @LastALWT BIGINT, @LastALWT_Base BIGINT--Variables to save current counter valuesDECLARE @TPS BIGINT, @Active BIGINT, @SCM BIGINT, @LRS BIGINT, @LTS BIGINT, @LWS BIGINT, @NDS BIGINT, @AWT BIGINT, @AWT_Base BIGINT, @ALWT BIGINT, @ALWT_Base BIGINT, @ALWT_DIV BIGINT, @AWT_DIV BIGINTSELECT @Loops = case when (@SecondsToRun % @RunIntervalSeconds) > 5 then @SecondsToRun / @RunIntervalSeconds + 1 else @SecondsToRun / @RunIntervalSeconds endSET @LoopCounter = 0SELECT @WaitForSeconds = CONVERT(varchar, DATEADD(s, @RunIntervalSeconds , 0), 114)SELECT @FileName = @LogFile + FORMAT ( GETDATE(), '-MM-dd-yyyy_m', 'en-US' ) + '.txt'--Create the File Handler and Open the FileEXECUTE sp_OACreate 'Scripting.FileSystemObject', @OACreate OUTEXECUTE sp_OAMethod @OACreate, 'OpenTextFile', @OAFile OUT, @FileName, 2, True, -2--Write the HeaderEXECUTE sp_OAMethod @OAFile, 'WriteLine', NULL,'Transactions/sec, Active Transactions, SQL Cache Memory (KB), Lock Requests/sec, Lock Timeouts/sec, Lock Waits/sec, Number of Deadlocks/sec, Average Wait Time (ms), Average Latch Wait Time (ms)'--Collect Initial Sample ValuesSET ANSI_WARNINGS OFFSELECT  @LastTPS= max(case when counter_name = 'Transactions/sec' then cntr_value end),  @LastLRS = max(case when counter_name = 'Lock Requests/sec' then cntr_value end),  @LastLTS = max(case when counter_name = 'Lock Timeouts/sec' then cntr_value end),  @LastLWS = max(case when counter_name = 'Lock Waits/sec' then cntr_value end),  @LastNDS = max(case when counter_name = 'Number of Deadlocks/sec' then cntr_value end),  @LastAWT = max(case when counter_name = 'Average Wait Time (ms)' then cntr_value end),  @LastAWT_Base = max(case when counter_name = 'Average Wait Time base' then cntr_value end),  @LastALWT = max(case when counter_name = 'Average Latch Wait Time (ms)' then cntr_value end),  @LastALWT_Base = max(case when counter_name = 'Average Latch Wait Time base' then cntr_value end)FROM sys.dm_os_performance_countersWHERE counter_name IN ('Transactions/sec','Lock Requests/sec','Lock Timeouts/sec','Lock Waits/sec','Number of Deadlocks/sec','Average Wait Time (ms)','Average Wait Time base','Average Latch Wait Time (ms)','Average Latch Wait Time base') AND instance_name IN( '_Total' ,'')SET ANSI_WARNINGS ONWHILE @LoopCounter<= @LoopsBEGINWAITFOR DELAY @WaitForSecondsSET ANSI_WARNINGS OFFSELECT  @TPS= max(case when counter_name = 'Transactions/sec' then cntr_value end)   ,  @Active = max(case when counter_name = 'Active Transactions' then cntr_value end)   ,  @SCM = max(case when counter_name = 'SQL Cache Memory (KB)' then cntr_value end)   ,  @LRS = max(case when counter_name = 'Lock Requests/sec' then cntr_value end)   ,  @LTS = max(case when counter_name = 'Lock Timeouts/sec' then cntr_value end)   ,  @LWS = max(case when counter_name = 'Lock Waits/sec' then cntr_value end)   ,  @NDS = max(case when counter_name = 'Number of Deadlocks/sec' then cntr_value end)   ,  @AWT = max(case when counter_name = 'Average Wait Time (ms)' then cntr_value end)   ,  @AWT_Base = max(case when counter_name = 'Average Wait Time base' then cntr_value end)   ,  @ALWT = max(case when counter_name = 'Average Latch Wait Time (ms)' then cntr_value end)   ,  @ALWT_Base = max(case when counter_name = 'Average Latch Wait Time base' then cntr_value end)FROM sys.dm_os_performance_countersWHERE counter_name IN ('Transactions/sec','Active Transactions','SQL Cache Memory (KB)','Lock Requests/sec','Lock Timeouts/sec','Lock Waits/sec','Number of Deadlocks/sec','Average Wait Time (ms)','Average Wait Time base','Average Latch Wait Time (ms)','Average Latch Wait Time base') AND instance_name IN( '_Total' ,'')SET ANSI_WARNINGS ONSELECT  @AWT_DIV = case when (@AWT_Base - @LastAWT_Base) > 0 then (@AWT_Base - @LastAWT_Base) else 1 end ,    @ALWT_DIV = case when (@ALWT_Base - @LastALWT_Base) > 0 then (@ALWT_Base - @LastALWT_Base) else 1 endSELECT @RowText = '' + convert(varchar, (@TPS - @LastTPS)/@RunIntervalSeconds) + ', ' +          convert(varchar, @Active) + ', ' +          convert(varchar, @SCM) + ', ' +          convert(varchar, (@LRS - @LastLRS)/@RunIntervalSeconds) + ', ' +          convert(varchar, (@LTS - @LastLTS)/@RunIntervalSeconds) + ', ' +          convert(varchar, (@LWS - @LastLWS)/@RunIntervalSeconds) + ', ' +          convert(varchar, (@NDS - @LastNDS)/@RunIntervalSeconds) + ', ' +          convert(varchar, (@AWT - @LastAWT)/@AWT_DIV) + ', ' +          convert(varchar, (@ALWT - @LastALWT)/@ALWT_DIV)SELECT  @LastTPS = @TPS,    @LastLRS = @LRS,    @LastLTS = @LTS,    @LastLWS = @LWS,    @LastNDS = @NDS,    @LastAWT = @AWT,    @LastAWT_Base = @AWT_Base,    @LastALWT = @ALWT,    @LastALWT_Base = @ALWT_BaseEXECUTE sp_OAMethod @OAFile, 'WriteLine', Null, @RowTextSET @LoopCounter = @LoopCounter + 1END--CLEAN UPEXECUTE sp_OADestroy @OAFileEXECUTE sp_OADestroy @OACreateprint 'Completed Logging Performance Metrics to file: ' + @FileNameENDGO

Use masterGoexec dbo.sp_write_performance_counters