A Repeatable Workload: Testing a Tuning Change Fairly

A repeatable workload is the same data, the same inputs and the same recording, run before and after your change. Without it, a tuning win is just one lucky run that you happened to like.

Why one quick run proves nothing

A colleague tells you, “I added an index and the query got faster.” You ask how many times they ran it. “Twice.” Which input did they use? “The one that was slow.”

That is not a test. The first run paid for compiling and for reading pages from disk. The second run was warm. The index may have helped, or the cache did. Nobody can tell.

A repeatable workload fixes that. It is small, boring and the same every time. I will build one on a temp table, so you can run it anywhere without leaving anything behind.

Fix the data and the inputs

The table has 100,000 rows, and GroupKey takes values from 0 to 999. The input list has three values: two that find rows and one that finds none. Fixed inputs are the whole point. If the inputs change between runs, the comparison is dead.

DROP TABLE IF EXISTS #Orders, #Inputs, #Evidence;

CREATE TABLE #Orders (Id int NOT NULL, GroupKey int NOT NULL);
INSERT #Orders (Id, GroupKey)
SELECT value, value % 1000 FROM GENERATE_SERIES(1, 100000);

CREATE TABLE #Inputs (InputValue int PRIMARY KEY);
INSERT #Inputs (InputValue) VALUES (10), (100), (1000);

CREATE TABLE #Evidence (
    Variant varchar(10), RunNumber int, InputValue int,
    ResultCount bigint, DurationUs bigint);

Write the test once

The test runs every input three times and records the row count and the elapsed microseconds. I put it in a temp procedure, so the baseline and the candidate go through exactly the same code. I use OPTION (RECOMPILE) so each run builds its plan from the real input value.

DROP PROCEDURE IF EXISTS #RunWorkload;
GO
CREATE PROCEDURE #RunWorkload @Variant varchar(10)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Run int = 1, @Input int, @Count bigint, @Start datetime2(7);
    WHILE @Run <= 3
    BEGIN
        SET @Input = (SELECT MIN(InputValue) FROM #Inputs);
        WHILE @Input IS NOT NULL
        BEGIN
            SET @Start = SYSUTCDATETIME();
            SELECT @Count = COUNT_BIG(*) FROM #Orders WHERE GroupKey = @Input OPTION (RECOMPILE);
            INSERT #Evidence (Variant, RunNumber, InputValue, ResultCount, DurationUs)
            VALUES (@Variant, @Run, @Input, @Count, DATEDIFF_BIG(microsecond, @Start, SYSUTCDATETIME()));
            SET @Input = (SELECT MIN(InputValue) FROM #Inputs WHERE InputValue > @Input);
        END;
        SET @Run += 1;
    END;
END;

Run the baseline, change one thing, run again

Baseline first, with no index. Then add one index, and run the same test again. Only one thing changes between the two runs. That is what makes the comparison fair.

EXEC #RunWorkload @Variant = 'baseline';

CREATE INDEX IX_Orders_GroupKey ON #Orders (GroupKey);

EXEC #RunWorkload @Variant = 'indexed';

Compare answers first, then times

Speed means nothing if the answers changed. So check the counts before you look at a clock. Each input must show one distinct count across all six runs.

SELECT InputValue, COUNT(DISTINCT ResultCount) AS DistinctCounts, MIN(ResultCount) AS RowsFound
FROM #Evidence
GROUP BY InputValue
ORDER BY InputValue;

Inputs 10 and 100 find 100 rows each, and input 1000 finds none. One distinct count each, so both variants agree. Now the timings. Keep every run in the list, including the ugly first one.

SELECT Variant, RunNumber, InputValue, ResultCount, DurationUs
FROM #Evidence
ORDER BY InputValue, Variant, RunNumber;

Your durations will differ from mine, and they will change from one execution to the next. That is normal. Look at the shape instead. In my output the very first baseline run was several times slower than the next two. That is warm-up cost, and it is exactly why I keep the first run in the list. The indexed runs sit lower for every input.

Also notice that the indexed numbers are tiny, around a millisecond, and some can even show 0. That is the clock ticking in coarse steps. Differences that small are noise. If your numbers overlap, you do not have a win yet.

What this test cannot tell you

This is one connection with tiny data. It does not show contention, plan reuse in your application, or the cost the index adds to every insert. Before you call it a result, add realistic parameter values, check logical reads and the actual plan, and test writes. Test concurrency separately, away from production.

Then clean up the temp objects.

DROP PROCEDURE IF EXISTS #RunWorkload;
DROP TABLE IF EXISTS #Orders, #Inputs, #Evidence;

Next time someone says “it got faster”, ask for the inputs, the runs and the row counts.

A tuning win is not one lucky run, it is a repeatable comparison.

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