Generate millions of rows of test data for SQL Performance Analysis


Improving SQL Server query performance on large tables has its own challenges. While working on a performance issue, I figured out the culprit after reviewing the SQL Profiler trace in QA environment and analyzing the execution plans of expensive queries. I rewrote the query keeping in mind the best practices and the next step was testing out the improvements locally.

To recreate this performance issue locally, I required a huge workload of test data in my tables. The plan was to have a table locally with 10 million rows and then capture the execution time of the query before and after changes.

I have done this many times earlier by manually writing T-SQL script to generate test data in a database table. For a change this time, I thought of trying out one of the 3rd party tools to help me generate this.

Being the Co-Chapter Lead of Steel City User Group In Birmingham, I decided to try out tools from one of our favorite sponsors – ApexSQL. I download the ‘ApexSQL Generate‘ to help me generate random test data for specified tables.

I was quite impressed with this tool since it helped me to generate 10 million of rows in a matter of couple of minutes.

Select the table you want to insert test data on —Select Table

Just provide the number of rows of test data you want to generate —

Provide number of rows

I was able to generate 10 million rows of test data with couple of button clicks in under 3 min time —

Summary

Looking at the data generated, I found that the tool supports all the SQL data types and generates realistic test data based on that. This is awesome since it makes the testing more accurate by emulating real-world test data. Hence your load tests and performance tests are going to be more accurate.

Data Generated

Conclusion

Your application is bound to grow over a period of time. Sometimes it is critical to test your changes with a bigger workload than your current table size to identify any performance bottlenecks, well ahead of time. Leveraging such automated tools to generate a workload for you to test your application makes your lives much easier. There are lot of other tools available in market to generate test data and you can give them a shot and use the one which suits your requirements.

Reference

SQL Test Data Generator

One thought on “Generate millions of rows of test data for SQL Performance Analysis

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s