Question: How do I generate test data for SQL Server ? I don’t want to buy a benchmarking tool, but I require a representative test case with real SQL statements. How do the professional DBA’s capture SQL Server workloads for testing?
Answer: First, try to use real-world data, not artificial test data sets. You want a representative benchmark.
A one dimensional view of SQL Server can lead to vastly different results. Data that is not representative and workloads executed on the data can create a false sense of results.
Always remember that there is no substitute for a real-world test that simulates the actual behaviour of your production systems. However , this is not always possible.
SQL Server test data generation testing tools
There are several tools and methods for generating test data for SQL Server:
- SQL Server Profiler- using the T-SQL Replay template.
- DBGEN - The TPC-H tools, dbgen and qgen allow for the development of up to a terabyte databases.
- Quest Benchmark Factory for Databases – Track the effects of SQL Server changes on the environment