Production SQL Server has a day job. The queries that test its data compete with the application for CPU, memory, IO, and locks, and the first full data profiling run is the heaviest thing you’ll send it.
DataKitchen has watched this go wrong. In a case study published in August 2026, a team pointed TestGen at a live transactional database and profiled five tables of under a million rows each. The application slowed down for 20 minutes. Data profiling moved to a replicated copy, on an overnight schedule.
Five ways to keep testing off the critical path
| Practice | What it buys you | How to do it |
|---|---|---|
| Test a replica, not the primary | The application never sees the load | An Always On readable secondary, a log-shipped or restored copy, or a reporting warehouse |
| Run off-peak | Heavy queries land when users are gone | Schedule data profiling and tests in the overnight window |
| Sample large tables | Less to read on the biggest tables | Sample-based data profiling for pattern and character analysis |
| Start with few threads | Fewer queries at once | One or two threads, raised once runs are stable |
| Read-only login | Nothing can write, by construction | A SQL login with SELECT on the tables you test |
Test a readable secondary or a copy
On an Always On availability group, add ApplicationIntent=ReadOnly to the connection string. A DataKitchen case study on a 1.2 TB SQL Server database found the two prerequisites that trip people up: the connection has to target the availability group listener, not an instance name, and read-only routing has to be configured on the group. Miss either and you’re on the primary, without an error.
No availability group? A nightly restored copy or the reporting warehouse works too. You test yesterday’s data, which is usually what the reports read anyway.
Schedule into the quiet window
TestGen schedules data profiling, test runs, and monitors. You pick a time zone and a frequency, or a cron expression for anything unusual, and the dialog shows the next run times before you save. If a long data profiling run is still going when users arrive, pause it to stop its queries and resume later. Stagger the long-running test suites rather than starting them together.
Sample the big tables
TestGen’s sample-based data profiling uses database-native sampling on SQL Server, Azure SQL, and Synapse, per its 2025 release notes. You set a sample percent and a minimum sample size per table group. The sample applies to pattern and character analysis. Record counts, distinct values, minimums, and maximums still cover the whole table.
For very large tables, the case study’s answer was views over a recent date range or one category at a time. If the table is partitioned, write the view so the partition filter still applies, or you’ve rebuilt the full scan you were avoiding.
Throttle concurrency
TestGen’s connection settings include Max Threads, the number of queries it runs at once, under Advanced Tuning. The case study started at one or two and raised it once runs were stable. That’s TestGen’s setting, not SQL Server’s max degree of parallelism. A DBA can also put the TestGen login in a Resource Governor workload group to cap its CPU and IO, as the same team did.
Keep the login read-only
TestGen needs a SQL login with SELECT on the tables you test. It reads information_schema.columns, sys.objects, and sys.partitions, which database users can read by default. It never runs INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP, and it stores only computed results in its own database on a server you host.
Data profiling once, tests and monitors often
The first data profiling run reads every column, so it’s the one to put on a copy at night. Generated tests check the columns and rules you keep, so they can follow each load. Monitors ask less of each table: freshness compares a fingerprint of key columns, and volume records a row count. TestGen’s monitor docs suggest running them at roughly twice the rate your data changes.
So a common split: data profiling weekly or after big schema changes, tests after each load, and monitors every few hours.
Open-source TestGen is free for one user, one connection, and one project. Start at the SQL Server install page.