Sql Profiler For Unit Testing

SQL Server Profiler Guide Author: Sravan Tadakamalla

SQL SERVER PROFILER GUIDE..............................................................................................................1 INTRODUCTION..................................................................................................................................................1 INSTALLATION...................................................................................................................................................2 WORKING: COLLECTING A TRACE.......................................................................................................................2 THE TOOL........................................................................................................................................................8 DOWNLOAD THE THIRD PARTY TOOL...................................................................................................................8

SQL Server Profiler Guide Introduction SQL Profiler is a tool that captures SQL Server events from the server and saves those events in a trace file (.trc file). We can then analyze or use the trace file to troubleshoot logic or performance problems. We can use this utility to monitor several areas of server activity, such as:

• • • • • •

Analyzing and debugging SQL statements and stored procedures. Monitoring slow performance. Stress analysis. General debugging and troubleshooting. Fine-tuning indexes. Auditing and reviewing security activity.

Installation Make sure SQL Server full installation is there. SQL Server Profiler tools come with SQL Server SDK. However, SQL Server Express Edition Profiler 2005/2008 provides the most of functionality standard profiler does, such as choosing events to profile, setting filters, etc.

Working: Collecting a Trace We collect the data by running a trace on the events defined in our template. During the run, Profiler displays the event classes and data columns that describe the event data being collected. Trace: The trace does the actual data capture, based on the events you defined in the template. Template: Use a template to define the criteria for each event you want to monitor. You don't actually execute a template. Rather, a template is used by a trace. Event: An event is an action generated by the SQL Server engine, such as a login connection or the execution of a T-SQL statement. Events are grouped by event categories. All the data generated by an event is displayed in the trace, which contains columns of data that describe in detail the event. Event Classes: One of the data columns in a trace—this particular column describes the event. Data Columns: One of the data columns in a trace—this column describes the type of data collected. After opening profiler, go to File menu under that select New Trace or click New Trace icon. You will be asked for the credentials to connect to the SQL Server. Identify the appropriate SQL Server instance, and click OK. Use the resulting Trace Properties dialog box and its four tabs to initiate the process: •

Use the General tab, shown in Figure A, to identify specific trace properties, such as name and location.

•

The Events tab lets you identify the events you want to trace.

•

The Data Column tab lets you specify the data the trace returns about a specific event.

•

Use the Filters tab to narrow the trace to a specific file(s).

Figure A

Identify the trace file.

Refer to Figure A To continue with the actual example and following are the options on the General tab: •

Name the trace and identify the server on which we'll run the trace—the Trace SQL Server property defaults to the instance identified earlier.

•

Use the Template Name control's drop-down list to choose one of the available templates. If you create a template, be sure to specify the path to

that file (tdf extension). You can add a default template via the Options menu off the Tools menu. •

Save the trace to a file, reducing overhead on the server. Selecting this option enables the two check boxes immediately below: The Enable File Roller option permits you to open a new file for the trace once the original file is full, and Server Processes SQL Server Traces Data indicates whether the server or the client application should perform the trace. Performing lengthy complicated event tracing on the server can reduce performance.

•

Saving the trace to a table is an alternate to saving the trace to a file. This again can have performance implications on a busy server. Specifically, a table trace requires more overhead.

•

The final option, Enable Trace Stop Time, allows you to determine when the trace ends.

Next, click the Events tab to set the event classes and events you want to trace. In Figure B, we've selected T-SQL Event and all its classes. Doubleclick an event class in the Available Event Classes control to move it to the Selected Event Classes control to the right. (The User Configurable event selected in Figure B is simply the result of the cursor choosing the next item after moving TSQL.)

Figure B

Select an event class.

After selecting the event classes you want to trace, click the Data Columns tab and specify the data to be exposed in the trace. As before, simply move items from the Unselected Data control to the Select Data control. Figure C shows this tab running a trace against the PPMSaas Access Data Project with most of the default items removed (highlight an item and click Remove). In addition, we added the DatabaseName item and a few other simple items (select an item and click Add).

Figure C

Identify the data you want to see in the trace.

Now you're ready to specify the file (or files) you want to trace. For instance, in this example, we'll narrow the trace to just the PPMSaasCS project file. Click the Filters tab, expand the DatabaseName item, and add PPMSaas to the Like option, as shown in Figure D.

Figure D

Limit the trace to the PPMSaasCS project file.

When you're ready to run the trace, click the Run button. Figure E shows the results of running the example trace when the PPMSaasCS Project opens and executes a stored procedure. This trace returns the user name, the application name, and all executed T-SQL statements. Figure E

Database Load Test Tool (Third Party Tool) The Tool This is a third party tool and it generates a Visual Studio 2005 Unit Test from a SQL Server Profiler trace. The tool extracts all the SQL statements and stored procedure calls from the trace and turns them into a single Visual Studio Unit Test, which can then be configured as a Visual Studio Load Test. The tool does not interact with the database itself when it generates the test code and so it can be used “offline”. The generated code needs to be customized to include a connection string to the database under test. The generated code also includes hooks to allow the user to customize the parameters that are passed to the SQL statements and stored procedures, so that variability can be introduced into the data to prevent caching from giving artificially high performance figures. The tool is intended to be used in conjunction with a client program that exercises the database. A trace of the client’s SQL Server activity is captured using the SQL Server Profiler. This represents a test scenario, which is then processed by the tool to produce a Unit Test that replays the scenario.

Download the Third Party Tool We can download the tool from this location

http://www.codeplex.com/SQLLoadTest Or http://sqlloadtest.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=151 52.

Prerequisites Make sure SQL Server 2005 SDK and the SQL Server 2005 Management Tools are installed. Copy the packaged assemblies to a convenient location. To use the generated code you need to have Visual Studio 2005 Team Edition for Software Testers, of Visual Studio Team Suite installed.

Creating Trace File The tool assumes that the trace only contains records pertaining to a single client session. So it is important to ensure that the trace is set up to only collect trace records generated by the activity of the client being used to generate the scenario. It is therefore advisable to run the client on a machine from which no other activity on the SQL Server database is being generated, and to set the SQL Server Profiler filter accordingly. The trace passed to the parser must contain the following columns, each required event class must include these columns: 1. Event Class 2. Text Data 3. Database Name - if this is omitted then the trace is assumed to be all for the same database 4. SPID The trace must contain the following event classes, each class must enable the columns listed above when they are defined for that event: 1. SQL:BatchStarting 2. RPC:Starting 3. Audit Login 4. Audit Logout Accordingly the tool implements the following rules: 1. All entries relating to the master, model and msdb databases are ignored.

2. All entries with no database name at all are ignored. 3. All calls to sp_reset_connection are ignored. A template is included in the package, but this will need to be further configured to ensure that only traffic from the test client is captured.

Running the Tool The executable prints the usage. As the tool runs it prints out the SQL statements that it is processing. Output will be two files in the same folder that can be added to a Visual Studio Test Project (C#). The stubs file will need to be renamed to a .cs file. The connection string will need to be put into the stubs file. The command line tool is called DbLoadTestGen, it prints the usage. Run it using a command with the following syntax: DbLoadTestGen.exe <Scenario Name> []

The tool’s three parameters are: 1. Scenario Name. A simple name for the scenario. This must be a valid C# identifier as it is used in the generated code. Avoid using the name of a type or operation in the services under test. 2. Trace file name. The path to the file containing the SQL trace to be processed. 3. Configuration file name. This is optional and it is the path to the file containing the configuration information. If the file is not present a default configuration is used.

Limitations of the Tool 1. Only supports code that operates against a single database with a single connection. 2. Prepared statements are not supported. 3. The tool does not support the XML data types. 4. Input/Output parameters that are initialised with a NULL cannot be distinguished from Output-only parameters, so they are treated as Output only. 5. When the trace passes a NULL to a binary parameter (e.g. image, binary) the generated code will generate a runtime error. The generated code needs to be modified to pass SqlBinary.Null. The custom code generation extensibility point can be used to work around this.

6. Positional parameters passed to SQL statements (as opposed to stored procedure calls) are not supported.

Integrating with VSTS and Running a Load Test Now the C# unit test scripts ready with us. We can add these files to unit tests/ project in Visual Studio to run the Database Units Tests.