SQL Server: What to Record Before Tuning a Slow Query

What to record before tuning a slow query starts with the execution that caused the complaint. I want the query, its inputs, and a measured baseline before changing anything. Without them, a faster test can answer a different question from the one the application asked.

Start with a useful description of slow

“The report is slow” needs a little more detail. Record when it started, how long it normally takes, and what changed for the user. Include the time zone, application operation, and whether the problem affects one request or many.

Keep application duration separate from database execution time. A page can spend time waiting for a connection, processing rows, or rendering results. A SQL timing alone does not measure that entire experience.

Keep these records together

Think of this as one evidence tray for one execution. A plan from yesterday and parameters from today do not describe the same test. Label each item with its capture time and keep missing details visible.

Save the exact request and parameter types

Copy the statement the application submitted, or the complete stored procedure call. Include parameter values and types. Record whether the application used a prepared statement, parameterized SQL, or a stored procedure.

Changing a parameter into a local variable creates a different test. So does choosing a convenient date range instead of the slow one. Keep the original request intact before creating a smaller reproduction.

Save the evidence in an approved private location. Query text, parameter values, and execution plans can contain sensitive business information. A public screenshot should not expose customer data.

Record the connection before changing it

Run this read-only query in the database where you are testing. It records the current connection, not another application session:

SELECT CONVERT(nvarchar(128), SERVERPROPERTY('ServerName')) AS ServerName,
       CONVERT(varchar(30), SERVERPROPERTY('ProductVersion')) AS ProductVersion,
       DB_NAME() AS DatabaseName,
       d.compatibility_level AS CompatibilityLevel,
       @@SPID AS SessionID
FROM sys.databases AS d
WHERE d.database_id = DB_ID();

The result helps prevent comparisons across different databases or compatibility levels. It does not explain the slowdown by itself. Keep the values with the test record rather than relying on the query-window title.

For the current session settings, run:

DBCC USEROPTIONS;

An SSMS session does not prove what the application session used. If their behavior differs, collect the application connection settings too. Record the difference before deciding to change either connection.

Capture measurements you can compare

For a query you are allowed to execute safely, enable these settings in the test connection:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

Run the exact query separately, then copy its Messages output. Keep elapsed time, CPU time, and logical reads together. Label which numbers describe compilation and which describe execution.

Turn the settings off when you finish:

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Logical reads count pages read from the data cache, not rows returned. CPU time and elapsed time measure different things. Parallel execution can use more total CPU time than elapsed time, so their difference is not a reliable wait calculation.

Record whether the run returned every expected row or ended with an error, cancellation, or timeout. Do not compare a completed run with a stopped run as though both performed the same work.

Keep the plan with the measurements

An actual execution plan records runtime details from an execution. An estimated plan does not run the statement. Label the plan type and capture time, and keep it with the matching request.

In SSMS, enable Include Actual Execution Plan before an approved test. Remember that the query still runs, including any data changes. My graphical execution plan guide explains the SSMS settings.

Record observed waits and blocking while the slow execution is happening. A later test after the blocker finishes cannot recreate that evidence. If Query Store already contains useful history, preserve the relevant plan and time interval before making changes.

A cached plan or Query Store plan provides useful history. It does not contain runtime counters from your new test. Do not label it that way. Also record when no plan or runtime capture was available.

Make the second test comparable

Use the same request, parameter types, data, and result requirements for the comparison. Note concurrent workload and whether earlier runs warmed the cache. Record every deliberate change between the baseline and the next run.

I would not clear the production plan cache or data cache to make a comparison look cleaner. Those actions change conditions for other queries too. Use an isolated test environment when the experiment requires that control.

One faster run is encouraging, but it is not enough to declare success. Repeat safe tests under comparable conditions and retain the results. Check that the query still returns the right data.

When waiting for more evidence is the wrong choice

During an incident, restoring service takes priority over completing a perfect worksheet. Capture what you can without delaying the response. Record the action, its time, and the evidence you could not obtain.

That exception does not make the missing baseline disappear. Label the conclusion honestly and arrange a controlled follow-up. Otherwise, a successful recovery can turn into an unsupported explanation.

Keep the original execution intact, then change one thing you can measure.

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