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 —

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

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

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.

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

Categories: Performance, Performance Tuning, SQL Server

Tags: Performance, SQL, SQL Server