Benchmark Factory for Database 8.3 - User Guide

Capture and Replay SQL Server Workload

Benchmark Factory allows you to capture SQL Server workloads, then replay those workloads to view real-time transactions and response results.

In Benchmark Factory, use one of the following workflows to capture a SQL Server workload:

• New Job Wizard—Use the New Job Wizard (File | New) to guide you through the steps of setting up and capturing the workload. Then add the captured workload to your new job to run it immediately or at a later time.
• Capture Scenario Wizard—Select Tools | Capture Workload to open the Capture Scenario Wizard.

Note: This feature is not available in the freeware edition of Benchmark Factory.

To capture a SQL Server workload

1. Select File | New to open the New Job Wizard.
2. On the Database Under Test page, select the database to test.
3. Then click .
4. On the Workload page, select Capture/Replay Test from the drop-down list.
5. Then select the Capture and Replay SQL Server Workload option.
6. Click .

7. On the Replay Workload page, click Perform New Capture. The Capture Scenario Wizard opens and the New Job Wizard closes.

   Tip: You can also create a new capture using the same settings as an existing capture project. In the Captures tab of the Benchmark Factory console, right-click a capture and select Repeat Capture.

8. Capture Scenario Wizard. In the Capture Scenario Wizard, enter connection information for the database from which you want to capture a workload. See Create SQL Server Connection for more information. Click Next when finished.

9. Directory Settings. On the Directory Settings page, specify a SQL Server server-side directory in which to place the capture files. Also, specify the capture directory from which Benchmark Factory will replay the files. Review the following for additional information:

   Click Next when finished.

   Capture Name	Enter a name for the capture, or use the default. This name is used for the sub-directory where the capture files are stored in the Capture Directory you specify. Note: Only alpha and numeric characters and the underscore (_) are permitted. The name must begin with a letter.
   Database Server Directory	Specify a server-side directory where Benchmark Factory should place the capture files. Specify the path as the server sees it. Note: You can specify a network directory here. Enter the full network path to the network directory. The database service must be able to access the network directory.
   Capture Directory	Specify a directory where Benchmark Factory will look for the capture files to replay. Do one of the following: Manual transfer: Specify a client-side directory and then manually transfer the capture files to this location. Shared directory: Specify the same server-side (or network) directory you specified in the preceding field. However, enter the path as the client computer sees it. To use this method, you must first map a drive on the client computer to the Database Server Directory (server-side or network directory). Then, browse to and select that mapped drive. Selecting a shared directory: The shared capture directory can be on the database server as a local directory or on a network file server as a local directory. Either location must be accessible by the Benchmark Factory client computer (Windows network share, Samba, or NFS). A network file server is the preferred location for the following reasons: This minimizes total network I/O required for capture and replay. The shared directory can be placed on an I/O subsystem with sufficient I/O bandwidth to handle both the concurrent I/O writing and the cumulative size of the trace and export files.

   

10. Capture Scope. In the Capture Scope page, select one or more databases from which to capture activity. Review the options below. Click Next when finished.

    Capture activity for entire database	Select to capture all activity for the entire database.
    Capture only activity for selected databases	Select to capture activity only for the selected databases. Then select the databases from which to capture activity.

11. Backup Scope. Benchmark Factory can export/backup the database objects and data required to replay the workload on the same data snapshot. Review the description below. Click Next when finished.

    Perform backup as part of the Capture Process

    Select this option to instruct Benchmark Factory to export/backup the databases selected on the previous page (Capture Scope). The backup is performed during the capture procedure. Note: If you selected to capture activity for the entire database, exporting the entire database can require significant time and space.

12. Filter Settings. (Optional) The Filtered Settings page allows you to add filters to exclude activity from the capture.

    • To add a filter, click Add. Then specify parameters for the filter. Click Next when finished.

13. Capture Thresholds. Benchmark Factory allows you to specify limits for CPU usage and free space during a capture. If levels exceed the values you specify, the capture process is stopped.

    Benchmark Factory displays the current values to help you determine the best thresholds to specify. Review the options below. Click Next when finished.

    Stop capture if ...	Description
    host CPU percentage of database server exceeds	Enter a percentage. If CPU usage exceeds this value, Benchmark Factory stops the capture process.
    trace file disk free space falls below	Enter a value for free space in GB. If the amount of free space falls below this level, Benchmark Factory stops the capture process.

    Note: The Capture Thresholds feature is not available for SQL Server running in a Linux environment.

14. Schedule Job. You can schedule a capture or start it immediately. Review the options below. Click Next when finished.

    Start Capture/Export	Immediately—Select to start the capture process immediately. Later—Select to schedule the capture process. Then specify a date and time based on the server time.
    Capture Automatically	Stop After—Use the sliders to specify a duration for the capture process. The default is 30 minutes.

15. Start the capture. The Submit page provides capture specifications. To start the capture (or enable the schedule), click Submit.

    

16. Capture Status. The Capture Status window opens providing export and/or capture status and details of the process.

    Immediate capture: If you selected to start the capture immediately, the Capture Status window displays information about the capture process, such as the status of the export process and the number of sessions captured.

    • Click the Click for capture details link to view more-detailed information during the export or capture. You can view which objects are exporting or which session/user is currently being captured and the total number of sessions captured.
    • Click Properties to review the capture description.
17. Click the Click for capture details links to review the export and capture processes. Close the Capture Status window when finished. You can open the Capture Status window again from the Captures tab in the Benchmark Factory console. In the Captures tab, right-click a capture and select View Status.

To replay a SQL Server workload

1. Select File | New to open the New Job Wizard.
2. On the Database Under Test page, select the database to test.
3. Then click .
4. On the Workload page, select Capture/Replay Test from the drop-down list.
5. Then select the Capture and Replay SQL Server Workload option.
6. Click .

7. Select a capture. On the Replay Workload page, select a capture from the Capture to Replay field using one of the following methods:

   • Click the browse button and navigate to the directory where the capture files are located. Select the project (.cpj) file for the capture you want to replay.
   • Select one of your previously-replayed captures from the drop-down list.

   Benchmark Factory loads the capture details into Capture Properties fields.

8. Import Test. Click . The test is added to the workload and the test Summary page displays.

9. On the Summary page, you can click each of the links to go directly to an option to modify it.

10. To jump to the Test Options tab, click Test Options at the bottom of the Summary page. Review the following for more information:

    Tab	Description
    Transactions tab	Modify the transactions and the transaction mix for the test. For a custom test, add transactions. See Transactions Tab for more information.
    Options tab	Enable scaling for the user scenario. See Options Tab (Capture/Replay) for more information.
    Timing tab	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 connect/disconnect options for the test. See Advanced Tab for more information.

11. 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.

Notes:

• Database objects created by Benchmark Factory for the capture process are automatically dropped after the capture is finished.
• You can manage your existing capture projects from the Captures tab in the Benchmark Factory console.

Run Capture Scenario Wizard

You can run the Capture Scenario Wizard as a standalone application to capture an Oracle or SQL Server workload. Then replay the workload in Benchmark Factory. This utility provides an easy way for you to capture your database workloads without requiring the installation of Benchmark Factory.

After a workload is captured, the capture files can be distributed to others to replay and test in Benchmark Factory.

This utility is the same Capture Scenario Wizard that opens from the New Job Wizard in Benchmark Factory and it produces the same type of capture and export files.

Prerequisites

Oracle or SQL Server Capture. The following is required to run the Capture Scenario Wizard to capture any workload:

• Microsoft MSXML must be installed to run the Capture Scenario Wizard.

Oracle Capture. The following is required to run the Capture Scenario Wizard to capture an Oracle workload:

• An Oracle Client (version 9 or later) must be installed to create an Oracle connection and capture the workload.
  • The 32-bit version of the Capture Wizard requires the 32-bit Oracle Client.
  • The 64-bit version of the Capture Wizard requires the 64-bit Oracle Client.

Run the Capture Scenario Wizard

To run the Capture Scenario Wizard standalone application

1. Navigate to the Benchmark Factory application directory where Benchmark Factory is installed and open the bin directory.

   For example, C:\Program Files (x86)\Quest Software\Benchmark Factory 7.5.0\bin

2. In the bin directory, double-click BFCapture.exe to start the application.
3. Select the capture type (Oracle or SQL Server).
   • To capture an Oracle workload, see Capture and Replay Oracle Workload and skip ahead to the Capture Scenario Wizard step.
   • To capture a SQL Server workload, see Capture and Replay SQL Server Workload and skip ahead to the Capture Scenario Wizard step.

To start the Capture Scenario Wizard from the command line, see Run the Capture Wizard from the Command Line.

Run Capture Wizard from Cmd Line

You can use the command line to start and run the Capture Scenario Wizard.

When running the Capture Scenario Wizard from the command line, you can specify the following parameters:

Note: The parameters -V, -D, and -C cannot be used at the same time and must have a capture project file location specified.

Replay Load from an Oracle Trace File

Benchmark Factory allows you to import the SQL contained within an Oracle trace file. The resulting workload contains a user scenario consisting of the timed event sequence of captured SQL transactions.

Importing an Oracle Trace file allows you to replay your database activity with the same timing as was originally captured on an existing in-house Oracle database application.

Note: To create an Oracle trace file which includes bind parameters, a level number of 4 must be used. For example, to start an Oracle trace for a user session, use the following command:

ALTER SESSION SET EVENTS '10046 trace name context forever, level 4'

To import an Oracle trace file

1. In the New Job or Edit Job Wizard, click Workload in the left pane.
2. On the workload page, select Capture / Replay Test from the drop-down list.
3. Select Capture load from an Oracle trace file.
4. Click the Select Test button. The Oracle Trace Input dialog opens.
5. Click Add Trace and browse to and select the trace file (or files).
   • To add additional files, click Add Trace.
   • To remove a file from the list, select the file and click Remove Trace.

6. When you finish inputting files, click Next. The Oracle Trace Activity dialog opens.

   

7. Select import criteria.
   • Import entire trace file—Imports all activity in the trace file or files.
   • Import transactions within specified date range—Select this option and enter a date range to import only the activity within the date range.

   • Include system activity—Select to include system activity (SYS USER).

     Note: It is not recommended to include system activity (SYS USER). This imports Oracle SQL statements routinely run by Oracle for maintenance reasons and is not required for the load testing process.

8. Click Finish.

9. If the trace import file exceeds the Benchmark Factory limit for displaying individual transaction, the following dialog displays:

   

   If you click Yes, the trace file import continues. Individual SQL is converted to .xml files. You can then edit the .xml files in the Benchmark Factory Session Editor.

10. The test is added to the workload and the test Summary page displays.
11. On the Summary page, you can click each of the links to go directly to an option to modify it.

12. To jump to the Test Options tab, click Test Options at the bottom of the Summary page. Review the following for more information:

    Tab	Description
    Transactions tab	Modify the transactions and the transaction mix for the test. For a custom test, add transactions. See Transactions Tab for more information.
    Options tab	Enable scaling for the user scenario. See Options Tab (Capture/Replay) for more information.
    Timing tab	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 connect/disconnect options for the test. See Advanced Tab for more information.

13. 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.