Benchmark Factory for Database 9.0 - User Guide

Create Scalability Test

The SQL Scalability test allows you to execute SQL statements, allowing users to spot potential issues not seen with a single execution. Users can run variations of a SQL statement in order to find the SQL that will perform the best under a load test.

Use this procedure to create a new SQL Scalability test or a Custom Scalability test.

To create a SQL Scalability Test

1. In the New Job or Edit Job Wizard, click Workload in the left pane.
2. On the workload page, do one of the following:
   • Select Scalability Test from the drop-down list. Then select the Test SQL for Scalability option.
   • Select Custom Test. Then select the Scalability test compares ... option.
3. Click the Add Test button. The test is added to the workload and the test Summary page displays.
4. On the Summary page, click each link to modify the specific test option.

5. Or you can select the Test Options tab to modify all test options. Review the following for additional information:

   Tab	Description
   Transactions tab	Add transactions for the test. See Transactions Tab for more information.
   User Load tab	Modify the number of users per test iteration. See Specify User Load for more information.
   Timing tab	Execute By—Select one of the following options: Number of executions per iteration—Each transaction is executed by each user for a specified number of times (recommended). Execution time per iteration—Executes each transaction for the specified length of time. User Startup—Select one of the following options: Start all users as quickly as possible—Starts all users immediately after a test begins. Start all users at even intervals for—Sets the amount of time in which to start all users at even intervals. The interval duration is equal to this value divided by the number of users. Start a new user every [n] seconds—Starts a new user, then waits the [n] number of seconds before starting the next user.
   Advanced tab	Specify Repository options, error handling, and database checkpoints for the test. You can also specify a file to execute at the beginning or end of each iteration. See Advanced Tab for more information.

6. After specifying options for this test, you can add another test to the job, configure job setup options, save and close the job, run the job, or schedule the job. For more information about each of these steps, see Quickstart: Create a New Job.

Create/Delete Benchmark Objects

The Create/Delete Benchmark Objects step creates or clears the database objects (tables/indexes, etc.) needed to run industry standard benchmarks. The step is included when you create an Industry Standard Benchmark Test. You can also add the step to an existing workload through the Edit Job Wizard.

Note: To create, delete, and modify database tables/indexes you must have the user login rights required.

To add a Create/Delete Benchmark Objects Test to a Workload

1. Use one of the following methods to open the Job Wizard:
   • Select File | New to open the New Job Wizard.
   • Select an existing job and click Edit Job.
2. Click Workload in the left pane. If this is an existing job, click Add Another Test/Step
3. On the Workload page, select Create/Delete Benchmark Objects Test from the drop-down list.
4. Then select the benchmark type from the Benchmark Type drop-down list.
5. Select one of the following options:
   • Create Benchmark Objects—Creates and initializes benchmark objects (tables, indexes, etc.).
   • Delete Benchmark Objects—Deletes existing benchmark objects (tables, indexes, etc.).
6. If you selected Create Benchmark Objects, select one of the following options:

   Create/Load objects if objects don't exist (no backup sets created)	If objects do not exist, Benchmark Factory creates the objects and loads data. If you select this option, Benchmark Factory does not create backup tables.
   Delete benchmark objects after completion of job	Benchmark Factory deletes objects after a job is finished.
   Refresh data using backup sets	When backup sets exist, Benchmark Factory reloads data and recreates indexes. If backup sets do not exist, Benchmark Factory creates objects and loads data. Then Benchmark Factory creates the backup tables.
   Recreate objects and reload data every execution	Benchmark Factory always deletes the existing objects and then recreates/reloads for each job execution. This allows you to ensure that the dataset is always in initialized status.
   Refresh data using inserts	If data does not exist, Benchmark Factory uses bulk insert to load data. If the following Benchmark Setting is selected, and the Benchmark Scale factor of the existing tables does not match the new requirement, the process of inserting additional data or deleting data my run long, depending on the size of the existing data and number of indexes. Note: If database does not support bulk insert, insert is used.

7. Click the Import Test button at the bottom of the page.
8. Summary. The test is added to the workload and the Summary page opens. Review the following editable options for this test/step.
   • Scale—Click the Scale link to modify the scale factor. The Scale page opens. In the Benchmark Scale field, select a new scale factor. See Benchmark Scale Factor for more information.

   Note: The test is added to the top of the workload list. To move the test down (or up) in the list, right-click the test and select Move Down (or Move Up).

9. Test Options. To modify test options for the Create Objects test/step, select the Test Options tab. Review the following for more information:
   • Scale—To modify the scale factor. See Benchmark Scale Factor for more information.
   • Options—To modify options for object creation and retention. See Options Tab (Create Objects Step) for more information.
   • Custom Creation SQL—To use custom SQL to create objects. See Customize SQL for Creating Objects for more information.

Tip: You can specify a default setting for Object/Data Retention in Edit | Settings | Benchmarks.

Execute External File

You might want to add an executable file (.bat or .exe) as part of a job. This gives you the flexibility to set up your database before running a workload. For example, you could run a .bat file to set up a prepared SQL statement before running a workload.  

To add an Execute File step to a workload

1. In the New Job or Edit Job Wizard, click Workload in the left pane.
2. On the workload page, select Execute External File from the drop-down list.
3. Enter the path or browse to and select a file to execute.

4. To add a timeout, select the Enforce Timeout option and enter the number of seconds for Benchmark Factory to wait for the execution to complete before continuing the job.

   You can specify a default setting for this option in Edit | Settings | Execute File. See Execute File Settings for more information.

Test Options for Create Objects