MS SQL Server Benchmark and Stress Test Tools? Bsohn417 asked on 2007-06-29. Microsoft SQL Server 2005; Microsoft SQL Server; 4 Comments. I'm looking to create a batch of automated tests in SQL on MS-SQL server, these will include tests like - insert some records, call stored procedure, check certain conditions in various tables. There will be other tests which will just be complicated queries - ie. Many look like unit tests but will include deep knowledge of the product.
Walkthrough
After installing SQLStress, start it from the program menu. To install SQLStress the .NET Framework 2.0 must be installed first. SQLStress runs on Itanium, on x86 and on x64 and can stress SQL Server 2005 and SQL Server 2000 except for the 'roundtrip' transaction that will not work on SQL Server 2000.
On the <Connect> tab, specify a server hosting an existing database and click on <Add Database>. A connection attempt to this database will be performed. If successful, the database will be added to the list of databases used to create workloads and shown on the <Databases> tab.
On the <Databases> tab, assuming this is a database you never used with SQLStess before, right click the database and select <Database → Prepare>. All necessary data to run SQLStress is created on that database (To get rid of these definitions and the test data, click <Database → Cleanup> afterwards).
Ms Sql Server Stress Test Tool Free
Go to the workload tab, click on <Create>, a workload is created that corresponds to the last used settings. Click on <Sign In> and the users are connected to the database or click <Run> and the workload is started immediately. Check the journal tab for errors and other messages. You can use the <Pause> button to disconnect from the database and the <Run> button to restart the workload repeatedly. The <Done> button will rundown the workload and dispose it.
The workload runs until the specified number of transactions have been performed. If the number of transaction to be performed are specified as zero, the workload runs until the <Done> or <Pause> button is clicked.
SQLStress is resilient to Cluster failover or other outages, it will just resume work if the database gets available again. Operations can be monitored with the 'Windows Performance Monitor'. A SQLStress performance object has been added at installation time except for 'Click Once' installations, see 'Performance' tab.
-->In this walkthrough, you create a SQL Server unit test that verifies the behavior of several stored procedures. You create SQL Server unit tests to help identify code defects that might cause incorrect application behavior. You can run SQL Server unit tests and application tests as part of an automated suite of tests.
In this walkthrough, you perform the following tasks:
After one of the unit tests detects an error in a stored procedure, you correct that error and re-run your test.
Prerequisites
To complete this walkthrough, you must be able to connect to a database server (or LocalDB database) on which you have permissions to create and deploy a database. For more information, see Required Permissions for Database Features of Visual Studio.
Create a Script that Contains a Database Schema
To create a script from which you can import a schema
On the File menu, point to New, and then click File.
The New File dialog box appears.
In the Categories list, click General if it is not already highlighted.
In the Templates list, click Sql File, and then click Open.
The Transact-SQL editor opens.
Copy the following Transact-SQL code, and paste it into the Transact-SQL editor.
Save the file. Make note of the location because you must use this script in the next procedure.
On the File menu, click Close Solution.
Next you create a database project and import the schema from the script that you have created.
Create a Database Project and Import a Schema
To create a database project
On the File menu, point to New, and click Project.
The New Project dialog box appears.
Under Installed Templates, select the SQL Server node, and then select SQL Server Database Project.
In Name, type SimpleUnitTestDB.
Select the Create directory for solution check box if it is not already selected.
Clear the Add to Source Control check box if it is not already cleared, and click OK.
The database project is created and appears in Solution Explorer. Next you import the database schema from a script.
To import a database schema from a script
On the Project menu, click Import and then Script (*.sql).
Click Next after you read the Welcome page.
Click Browse, and go to the directory where you saved the .sql file.
Double-click the .sql file, and click Finish.
The script is imported, and the objects that are defined in that script are added to your database project.
Review the summary, and then click Finish to complete the operation.
Note
The Sales.uspFillOrder procedure contains an intentional coding error that you will discover and correct later in this procedure.
To examine the resulting project
In Solution Explorer, examine the script files that were imported into the project.
In SQL Server Object Explorer, look at the database in the Projects node.
Deploying to LocalDB
By default, when you press F5, you deploy (or publish) the database to a LocalDB database. You can change the database location by going to the Debug tab of the project's property page and changing the connection string.
Create SQL Server Unit Tests
To create a SQL Server unit test for the stored procedures
In SQL Server Object Explorer, expand the projects node for SimpleUnitTestDB and then expand Progammability and then the Stored Procedures node.
Right-click one of the stored procedures, and click Create Unit Tests to display the Create Unit Tests dialog box.
Select the check boxes for all five stored procedures: Sales.uspCancelOrder, Sales.uspFillOrder, Sales.uspNewCustomer, Sales.uspPlaceNewOrder, and Sales.uspShowOrderDetails.
In Project drop-down list, select Create a new Visual C# test project.
Accept the default names for the project name and class name, and click OK.
In the test configuration dialog box, in the Execute unit tests using the following data connection, specify a connection to the database that you deployed earlier in this walkthrough. For example, if you used the default deployment location, which is LocalDB, you would click New Connection specify (LocalDB)Projects. Then, choose the name of the database. Then, click OK to close the Connection Properties dialog box.
Note
If you must test views or stored procedures that have restricted permissions, you would typically specify that connection in this step. You would then specify the secondary connection, with broader permissions, to validate the test. If you have a secondary connection, you should add that user to the database project, and create a login for that user in the pre-deployment script.
In the test configuration dialog box, in the Deployment section, select the Automatically deploy the database project before unit tests are run check box.
In Database project, click SimpleUnitTestDB.sqlproj.
In Deployment configuration, click Debug.
You might also generate test data as part of your SQL Server unit tests. For this walkthrough, you will skip that step because the tests will create their own data.
Click OK.
The test project builds and the SQL Server Unit Test Designer appears. Next, you will update test logic in the Transact-SQL script of the unit tests.
Define Test Logic
This very simple database has two tables, Customer and Order. You update the database by using the following stored procedures:
uspNewCustomer - This stored procedure adds a record to the Customer table, which sets the customer's YTDOrders and YTDSales columns to zero.
uspPlaceNewOrder - This stored procedure adds a record to the Orders table for the specified customer and updates the YTDOrders value on the corresponding record in the Customer table.
uspFillOrder - This stored procedure updates a record in the Orders table by changing the status from 'O' to 'F' and increments the YTDSales amount on the corresponding record in the Customer table.
uspCancelOrder - This stored procedure updates a record in the Orders table by changing the status from 'O' to 'X' and decrements the YTDOrders amount on the corresponding record in the Customer table.
uspShowOrderDetails - This stored procedure joins the Orders table with the Custom table and shows the records for a specific customer.
Note
This example illustrates how to create a simple SQL Server unit test. In a real-world database, you could sum the total amounts of all orders with a status of 'O' or 'F' for a particular customer. The procedures in this walkthrough also contain no error handling. For example, they do not prevent you from calling uspFillOrder for an order that has already been filled.
The tests assume that the database starts in a clean state. You will create tests that verify the following conditions:
uspNewCustomer - Verify that the Customer table contains one row after you run the stored procedure.
uspPlaceNewOrder - For the customer who has a CustomerID of 1, place an order for $100. Verify that the YTDOrders amount for that customer is 100 and that the YTDSales amount is zero.
uspFillOrder - For the customer who has a CustomerID of 1, place an order for $50. Fill that order. Verify that YTDOrders and YTDSales amounts are both 50.
uspShowOrderDetails - For the customer who has a CustomerID of 1, place orders for $100, $50, and $5. Verify that uspShowOrderDetails returns the right number of columns and that the result set has the expected checksum.
Note
For a complete set of SQL Server unit tests, you would typically verify that the other columns were set correctly. To keep this walkthrough at a manageable size, it does not describe how to verify the behavior of uspCancelOrder.
To write the SQL Server unit test for uspNewCustomer
In the navigation bar of the SQL Server Unit Test Designer, click Sales_uspNewCustomerTest, and make sure that Test is highlighted in the adjacent list.
After you perform the previous step, you can create the test script for the test action in the unit test.
Update the Transact-SQL statements in the Transact-SQL editor to match the following statements:
In the Test Conditions pane, click the Inconclusive test condition, and then click the Delete Test Condition icon (the red X).
In the Test Conditions pane, click Row Count in the list, and then click the Add Test Condition icon (the green +).
Open the Properties window (select the test condition and press F4), and set the Row Count property to 1.
On the File menu, click Save All.
Next you define the unit test logic for uspPlaceNewOrder.
To write the SQL Server unit test for uspPlaceNewOrder
In the navigation bar of the SQL Server Unit Test Designer, click Sales_uspPlaceNewOrderTest, and make sure that Test is highlighted in the adjacent list.
After you perform this step, you can create the test script for the test action in the unit test.
Update the Transact-SQL statements in the Transact-SQL editor to match the following statements:
In the Test Conditions pane, click the Inconclusive test condition, and click Delete Test Condition.
In the Test Conditions pane, click Scalar Value in the list, and then click Add Test Condition.
In the Properties window, set the Expected Value property to 100.
In the navigation bar of the SQL Server Unit Test Designer, click Sales_uspPlaceNewOrderTest, and make sure that Pre-Test is highlighted in the adjacent list.
After you perform this step, you can specify statements that put your data into the state that is required to execute your test. For this example, you must create the Customer record before you can place an order.
Click Click here to create to create a pre-test script.
Update the Transact-SQL statements in the Transact-SQL editor to match the following statements:
On the File menu, click Save All.
Next you create the unit test for uspFillOrder.
To write the SQL Server unit test for uspFillOrder
In the navigation bar of the SQL Server Unit Test Designer, click Sales_uspFillOrderTest, and make sure that Test is highlighted in the adjacent list.
After you perform this step, you can create the test script for the test action in the unit test.
Update the Transact-SQL statements in the Transact-SQL editor to match the following statements:
In the Test Conditions pane, click the Inconclusive test condition, and click Delete Test Condition.
In the Test Conditions pane, click Scalar Value in the list, and then click Add Test Condition.
In the Properties window, set the Expected Value property to 100.
In the navigation bar of the SQL Server Unit Test Designer, click Sales_uspFillOrderTest, and make sure that Pre-Test is highlighted in the adjacent list. After you perform this step, you can specify statements that put your data into the state that is required to execute your test. For this example, you must create the Customer record before you can place an order.
Click Click here to create to create a pre-test script.
Update the Transact-SQL statements in the Transact-SQL editor to match the following statements:
On the File menu, click Save All.
To write the SQL Server unit test for uspShowOrderDetails
In the navigation bar of the SQL Server Unit Test Designer, click Sales_uspShowOrderDetailsTest, and make sure that Test is highlighted in the adjacent list.
After you perform this step, you can create the test script for the test action in the unit test.
Update the Transact-SQL statements in the Transact-SQL editor to match the following statements:
In the Test Conditions pane, click the Inconclusive test condition, and click Delete Test Condition.
In the Test Conditions pane, click Expected Schema in the list, and then click Add Test Condition.
In the Properties window, in the Configuration property, click the browse button ('...').
In the Configuration for expectedSchemaCondition1 dialog box, specify a connection to your database. For example, if you used the default deployment location, which is LocalDB, you would click New Connection specify (LocalDB)Projects. Then, choose the name of the database.
Click Retrieve. (If necessary, click Retrieve until you see data.)
The Transact-SQL body of your unit test is executed, and the resulting schema appears in the dialog box. Because the pre-test code was not executed, no data is returned. As you are only verifying the schema and not the data, this is fine.
Click OK.
The expected schema is stored with the test condition.
In the navigation bar of the SQL Server Unit Test Designer, click Sales_uspShowOrderDetailsTest, and make sure that Pre-Test is highlighted in the adjacent list. After you perform this step, you can specify statements that put your data into the state that is required to execute your test. For this example, you must create the Customer record before you can place an order.
Click Click here to create to create a pre-test script.
Update the Transact-SQL statements in the Transact-SQL editor to match the following statements:
In the navigation bar of the SQL Server Unit Test Designer, click Sales_uspShowOrderDetailsTest, and click Test in the adjacent list.
You must do this because you want to apply the checksum condition to the test, not to the pre-test.
In the Test Conditions pane, click Data Checksum in the list, and then click Add Test Condition.
In the Properties window, in the Configuration property, click the browse button ('...').
In the Configuration for checksumCondition1 dialog box, specify a connection to your database.
Replace the Transact-SQL in the dialog box (under the Edit Connection button) with the following code:
This code combines the Transact-SQL code from the pre-test with the Transact-SQL from the test itself. You need both to return the same results that the test will return when you run it.
Click Retrieve. (If necessary, click Retrieve until you see data.)
The Transact-SQL that you specified is executed, and a checksum is calculated for the returned data.
Click OK.
The calculated checksum is stored with the test condition. The expected checksum appears in the Value column of the Data Checksum test condition.
On the File menu, click Save All.
At this point, you are ready to run your tests.
Run SQL Server Unit Tests
To run the SQL Server unit tests
On the Test menu, point to Windows, and then click Test View in Visual Studio 2010 or Test Explorer in Visual Studio 2012.
In the Test View window (Visual Studio 2010), click Refresh on the toolbar to update the list of tests. To see the list of tests in Test Explorer (Visual Studio 2012), build the solution.
The Test View or Test Explorer window lists the tests that you created earlier in this walkthrough and to which you added Transact-SQL statements and test conditions. The test that is named TestMethod1 is empty and is not used in this walkthrough.
Right-click Sales_uspNewCustomerTest, and click Run Selection.
Visual Studio uses the privileged context that you specified to connect to the database and apply the data generation plan. Visual Studio then switches to the execution context before it runs the Transact-SQL script in the test. Finally, Visual Studio evaluates the results of the Transact-SQL script against those that you specified in the test condition, and a result of either pass or fail appears in the Test Results window.
View the result in the Test Results window.
The test passes, which means that SELECT statement returns one row when it is run.
Repeat step 3 for the Sales_uspPlaceNewOrderTest, Sales_uspFillOrderTest, and Sales_uspShowOrderDetailsTest tests. Results should be as follows:
Test Expected Result Sales_uspPlaceNewOrderTest Pass Sales_uspShowOrderDetailsTest Pass Sales_uspFillOrderTest Fails with the following error: 'ScalarValueCondition Condition (scalarValueCondition2) Failed: ResultSet 1 Row 1 Column 1: values do not match, actual '-100' expected '100'.' This error occurs because the definition of the stored procedure contains a minor error. Next, you will correct the error and re-run your test.
To correct the error in Sales.uspFillOrder
In the SQL Server Object Explorer Projects node for your database, double-click the uspFillOrder stored procedure to open its definition in the Transact-SQL editor.
In the definition, find the following Transact-SQL statement:
Change the SET clause in the statement to match the following statement:
On the File menu, click Save uspFillOrder.sql.
In the Test View, right-click Sales_uspFillOrderTest, and click Run Selection.
The test passes.
Add a Negative Unit Test
Ms Sql Server Stress Test Tool Download
You might create a negative test to verify that a test fails when it should fail. For example, if you try to cancel an order that was already filled, that test should fail. In this part of the walkthrough, you create a negative unit test for the Sales.uspCancelOrder stored procedure.
To create and verify a negative test, you must perform the following tasks:
Update the stored procedure to test for failure conditions
Define a new unit test
Modify the code for the unit test to indicate that is expected to fail
Run the unit test
To update the stored procedure
In the SQL Server Object Explorer Projects node for the SimpleUnitTestDB database, expand the Programmability node, expand the Stored Procedures node, and double-click uspCancelOrder.
In the Transact-SQL editor, update the procedure definition to match the following code:
On the File menu, click Save uspCancelOrder.sql.
Press F5 to deploy SimpleUnitTestDB.
You deploy the updates to the uspCancelOrder stored procedure. You changed no other objects, so only that stored procedure is updated.
Next you define the associated unit test for this procedure.
To write the SQL Server unit test for uspCancelOrder
In the navigation bar of the SQL Server Unit Test Designer, click Sales_uspCancelOrderTest, and make sure that Test is highlighted in the adjacent list.
After you perform this step, you can create the test script for the test action in the unit test.
Update the Transact-SQL statements in the Transact-SQL editor to match the following statements:
In the Test Conditions pane, click the Inconclusive test condition, and click the Delete Test Condition icon.
In the Test Conditions pane, click Scalar Value in the list, and then click the Add Test Condition icon.
In the Properties window, set the Expected Value property to 0.
In the navigation bar of the SQL Server Unit Test Designer, click Sales_uspCancelOrderTest, and make sure that Pre-Test is highlighted in the adjacent list. After you perform this step, you can specify statements that put your data into the state that is required to execute your test. For this example, you must create the Customer record before you can place an order.
Click Click here to create to create a pre-test script.
Update the Transact-SQL statements in the Transact-SQL editor to match the following statements:
On the File menu, click Save All.
At this point, you are ready to run your tests.
To run the SQL Server unit tests
In Test View, right-click Sales_uspCancelOrderTest, and click Run Selection.
View the result in the Test Results window.
The test fails and the following error appears:
Test method TestProject1.SqlServerUnitTests1.Sales_uspCancelOrderTest threw exception: System.Data.SqlClient.SqlException: You can only cancel open orders.
Next, you modify the code to indicate that the exception is expected.
To modify the code for the unit test
In Solution Explorer, expand TestProject1, right-click SqlServerUnitTests1.cs, and click View Code.
In the code editor, navigate to the Sales_uspCancelOrderTest method. Modify the attributes of the method to match the following code:
You specify that you expect to see a specific exception. You can optionally specify a specific error number. If you do not add this attribute, the unit test will fail, and a message appears in the Test Results window
Important
Currently, Visual Studio 2012 does not support the ExpectedSqlException attribute. For information to work around this, see Unable to Run 'Expected Failure' Database Unit Test.
On the File menu, click Save SqlServerUnitTests1.cs.
Next, you re-run your unit test to verify that it fails as expected.
To re-run the SQL Server unit tests
In Test View, right-click Sales_uspCancelOrderTest, and click Run Selection.
View the result in the Test Results window.
The test passes, which means that the procedure failed when it was supposed to fail.
Sql Server Administration Tools
Next Steps
In a typical project, you would define additional unit tests to verify that all critical database objects work correctly. When the set of tests is complete, you would check those tests into version control to share them with the team.
Ms Sql Server Stress Test Tool Harbor Freight
After you establish a baseline, you can create and modify database objects and then create associated tests to verify whether a change will break expected behavior.
Ms Sql Server Stress Test Tool Test
See Also
Ms Sql Server Stress Test Tool Set
Creating and Defining SQL Server Unit Tests
Verifying Database Code by Using SQL Server Unit Tests
How to: Create an Empty SQL Server Unit Test
How to: Configure SQL Server Unit Test Execution