Sample Databases for Practicing Performance Tuning

A query can finish instantly in a tiny sample and struggle on a real workload. Sample databases for practicing performance tuning need enough volume and uneven data to make plan choices visible, not merely a familiar logo on the backup file.

Decide Which Problem to Target When Practicing Performance Tuning

Practicing performance tuning is not one exercise. A missing index, parameter sensitivity, sort spill, and blocking problem need different fixtures. Write the question before choosing a database. What plan should a good query produce, and what poor choice should the lesson reveal? A sample that cannot produce both outcomes is the wrong starting point.

I start with a query that returns a stable, understandable result. A lesson about faster code should not hide changed totals. Then I inspect the data distribution and indexes. If the query has no selective predicate or join choice, adding millions of rows can make it slower without making it educational.

Use AdventureWorks for Clear Plans

AdventureWorks has approachable sales and product relationships. It works well for explaining joins, predicates, and index access paths. Its manageable size makes a first plan easy to read. That same size limits some demonstrations: a scan can be the right choice on a small table, and an added index can show little benefit. Do not call the optimizer wrong because it ignores an index on tiny data.

I use AdventureWorks when the lesson is plan reading or query shape. For cardinality and grant exercises, I change only the relevant table or choose a larger fixture. Keep a clean backup so the modified sample can be reset. Document every index and data change made for the exercise.

Use WideWorldImporters for Operational Shapes

WideWorldImporters includes transactional and analytical variants. Its order and stock workflows support practical joins, while the warehouse variant supports aggregations and columnstore lessons. Microsoft also documents ways to generate more sample data. Check the chosen version and edition before using features such as In-Memory OLTP in a demonstration.

I test the query against the installed sample rather than assuming the downloaded version has enough history. A lesson about year-to-year grouping fails if the data covers only a narrow period. Choose the tables and date range deliberately. More rows help only if they preserve the relationships and distributions the optimizer uses.

Use Public Q and a Data Carefully

Stack Overflow data can show highly uneven activity, long comment threads, and popular tags. That shape helps demonstrate estimates and joins that behave differently for common and rare values. The schema and available extracts vary, so document the source and exact extract. Follow its license and privacy conditions. A public data set is still not a license to ignore data handling.

I keep a bounded local copy for repeatability. If a full extract is too large, sample by a rule that preserves the skew. Taking the first rows of a file can remove the interesting tail. Which values make the plan change? Keep those and enough ordinary rows for contrast.

Measure the Starting Shape Before Practicing Performance Tuning

Before changing data, record row counts, index definitions, statistics age, and the plan for the chosen query. This query shows approximate rows per user table in the current database. It helps locate candidates, but not the shape inside each table. Follow it with grouped counts for predicate columns used by the lesson.

I avoid presenting a measured result from another machine as a promise. Disk, cache, SQL Server build, and concurrent work all affect timings. The repeatable part is the script and the observation method.

SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS table_name,
       SUM(row_count) AS rows_present
FROM sys.dm_db_partition_stats
WHERE index_id IN (0,1)
GROUP BY object_id
ORDER BY rows_present DESC;

Enlarge the Right Tables

Increase volume in related tables together. Duplicating orders without valid customer keys damages the model and can change join selectivity in an unrealistic way. Use synthetic dates, status distributions, and customer skew that support the lesson. Generate in batches when a single transaction would overfill the log. After loading, update statistics as appropriate and inspect the resulting plan.

I record the generator and its seed so a second run has comparable data. Watch database and log space while enlarging a fixture. A performance exercise should not become an accidental capacity test. If a columnstore or partitioning lesson needs a documented generator in WideWorldImportersDW, use that supported path rather than hand-copying fact rows.

Compare Plans With the Same Output

Use SET STATISTICS IO and the actual execution plan for the old and changed queries. Compare reads, CPU, duration, estimates, actual rows, and memory grants on the same fixture. Keep the result set equivalent. The code below is a measurement wrapper for a query you choose, not a claim about the number it will return.

I change one factor at a time. Add an index, then rerun. Rewrite a predicate, then rerun. If you change both data volume and code between measurements, the lesson cannot explain the difference.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT CustomerID, COUNT_BIG(*) AS orders_placed
FROM Sales.Orders
GROUP BY CustomerID;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Keep the Setup Path for Practicing Performance Tuning Available

The existing install guide covers installation of AdventureWorks and WideWorldImporters. Save the baseline version, the enlargement script, the tested query, and a reset plan with the lesson. Students need the same starting point if they are to compare their own plans meaningfully.

Which limitation of the fixture should you disclose? State it plainly. A sample can teach one plan decision without reproducing every production workload. I keep the claim narrow and the experiment reproducible. That is more useful than a dramatic timing screenshot whose underlying data cannot be rebuilt.

For practicing performance tuning, add a baseline workload script with parameters and a stated question. An index exercise needs both the query and an expectation about what the index should improve. Capture the actual plan, logical reads and elapsed time on the same machine and data state before and after. Avoid turning a single timing run into a universal performance claim; cache state and concurrent activity can change that number.

I deliberately keep a reset script for each tuning exercise. If the first learner adds an index, the second learner should not wonder why the scan disappeared. A useful performance sample includes enough rows and distribution to make the optimizer’s choices visible, but it remains small enough to rebuild. The lesson is the reasoning: estimate, access path, grant, reads and tradeoff. A faster screenshot without the query context is a trophy with no label.

Related reading on this blog: Install AdventureWorks and WideWorldImporters: Updated 2026 and SQL SERVER Performance Tuning: Catching Key Lookup in Action.

A performance sample is not realistic because it is large, it is realistic because its shape tests the right decision.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.