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? This is part of acceptance 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
  
   

Read More

SQL Server test data generation testing tools - SQL Server DBA

Performance Metrics used in acceptance tests - SQL Server DBA

SQL Server - Query baseline testing - SQL Server DBA