SQL Server 2000 Performance Guidelines - Testing

From Guidance Share

Jump to: navigation, search

- J.D. Meier, Srinath Vasireddy, Ashish Babbar, Sharon Bjeletich and Alex Mackman


Use tables that contain a realistic amount and distribution of data.

The performance of a query that is not tuned can vary dramatically depending on the size of the data. A query that takes less than a second in a small database can take minutes in a database that has millions of rows. If your production database is large, populate your test database with an equivalent amount of data during development and testing. This gives you an opportunity to test the performance of your application with realistic data and to find the queries that need to be optimized. Ensure that you check your query execution plans by using tables that contain a realistic amount and distribution of data.

When you populate tables with large amounts of test data, follow these guidelines:

Ensure that your transaction logs do not fill up.

Using a simple loop mechanism can fill your transaction log for every single insert.

Budget your database growth.

Use tools to populate data.

The SQL Server resource kit provides valuable tools for generating test data in your database such as Database Hammer and Database Generator. For more information, see Chapter 39, "Tools, Samples, eBooks, and More," on the SQL Server Resource CD. This content is also available online at http://www.microsoft.com/sql/techinfo/reskit/default.asp.

Use existing production data.

If your application is going to be used against an existing database, consider making copies of the production data in your development, testing, and staging environments. If your production database contains sensitive data such as salary information, student grades, or other sensitive data, make sure that you strip it out or randomize it.

Use common user scenarios with a balance between read and write operations.

Use testing tools to perform stress and load tests on the system.

Personal tools