Consulting 104 – Why Do I Give All the Performance Tuning Scripts to My Customers?

I hand over every script because the work should stay with your team after the call. You keep the performance tuning scripts so your team can understand the change and repeat the checks. Every checklist and prompt stays with you too.

This is part 4 of my six-part consulting series. The materials are how your team keeps the work after the session ends.

Teaching Makes Performance Tuning Scripts More Useful

A script's value extends beyond executing its statements. Your team needs to know what question it answers. Explain the result columns before deciding which value deserves attention.

I learn while teaching because the customer has context I don't have. Your team knows the application's busy periods and unusual business operations. That context changes how a diagnostic result should be interpreted.

When you run the script, you learn the server as well as the syntax. You see where the number lives and how it changes. That knowledge makes a later check easier to perform with confidence.

I ask the operator to explain a finding back in plain language. That exposes unclear assumptions before a change. The script then becomes a usable tool rather than a saved collection of unfamiliar statements.

Performance tuning has many sides, and its checks become easier once their meaning is clear. I don't describe every problem as simple. A query still needs workload context and a reason for its next action.

The teaching continues through the undo path. Restoring a value should be as understandable as setting the proposed value. A customer shouldn't need another appointment to decipher the recovery instructions.

Keep Code You Can Read and Audit

Save the exact script used, its intended instance and database, and the reason for running it. Record whether it only reads diagnostics or changes something. Separate those scripts so a later operator can choose deliberately.

Keep controlled versions of those source files and a change log. Your team's approved system can manage that history. Dated local copies with documented revisions also preserve the previous script without introducing a new tool.

A screenshot of the previous settings helps orientation. A saved query result and matching undo script preserve more useful detail. Keep both beside the proposed change when they serve the review.

Record the decision on each finding: do it, study it, or skip it. The existence of a script doesn't prove permission to execute it. That distinction remains important when somebody reuses the folder months later.

Include the conditions that make the script appropriate. State required permissions and the supported SQL Server versions. A comment explaining the expected scope prevents accidental instance-wide collection during a database-specific check.

Store the results in your own approved location. Nothing is copied off your systems during my sessions. Sensitive query text and customer identifiers need the same care after the call as during it.

The Performance Tuning Scripts Worth Keeping

The useful collection answers several distinct questions. Group scripts by those questions rather than by the order they were written. Each group needs a short explanation of its output.

• Configuration snapshots use sys.configurations, sys.databases, and sys.master_files to describe settings, database options, and file layout.
• Wait snapshots and deltas use sys.dm_os_wait_stats to identify activity during a defined interval.
• File I/O checks use sys.dm_io_virtual_file_stats to compare stall and operation differences over matching captures.
• Resource-query checks use Query Store or sys.dm_exec_query_stats to locate statements consuming CPU and logical reads.
• Index checks combine sys.dm_db_index_usage_stats, missing-index views, and sys.dm_db_index_physical_stats for reviewed investigation.
• Blocking checks use sys.dm_exec_requests, while system_health Extended Events provides retained deadlock reports on SQL Server.
• Undo scripts restore the saved values for every approved change, with the same instance and database context.

Your performance tuning scripts should connect each result to a decision. For example, an I/O average needs its operation count and interval. A missing-index suggestion needs the existing index design beside it.

Physical index inspection can consume resources and acquire locks even when it only reads information. Restrict its database, object, and scan mode. Avoid treating a diagnostic label as permission for an unrestricted scan.

Read-only scripts still need approved permissions. Instance performance DMVs use VIEW SERVER STATE on older versions and VIEW SERVER PERFORMANCE STATE from SQL Server 2022. Database-specific diagnostics have their own documented requirements.

Capture Configuration Into Approved History

The following example stores instance configuration values with a capture time. Reading sys.configurations doesn't change the configuration. Inserting the snapshot does write the monitoring tables, so label the capture accordingly.

Create these tables once in an approved monitoring database. They retain an instance name so separate sources aren't compared accidentally. Review storage and retention before scheduling collection.

CREATE TABLE dbo.ConfigurationCapture
(
    CaptureId bigint IDENTITY PRIMARY KEY,
    CapturedUtc datetime2(3) NOT NULL,
    InstanceName sysname NOT NULL
);
CREATE TABLE dbo.ConfigurationValue
(
    CaptureId bigint NOT NULL REFERENCES dbo.ConfigurationCapture (CaptureId),
    SettingName nvarchar(128) NOT NULL,
    ConfiguredValue sql_variant NOT NULL,
    ActiveValue sql_variant NOT NULL,
    IsDynamic bit NOT NULL,
    PRIMARY KEY (CaptureId, SettingName)
);

The capture stores configured and active values separately. A pending change can make those values differ. Preserving both prevents a later report from silently choosing the wrong interpretation.

The next script adds one capture without changing settings. Run it again later to create a comparison point. Both the header and its values commit together or roll back together.

-- Writes monitoring history; does not change instance configuration.
SET XACT_ABORT ON;
DECLARE @InstanceName sysname = CONVERT(sysname, SERVERPROPERTY('ServerName'));
IF @InstanceName IS NULL
    THROW 51130, 'The source instance name was not available.', 1;
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT dbo.ConfigurationCapture VALUES (SYSUTCDATETIME(), @InstanceName);
    DECLARE @CaptureId bigint = CONVERT(bigint, SCOPE_IDENTITY());
    INSERT dbo.ConfigurationValue
        (CaptureId, SettingName, ConfiguredValue, ActiveValue, IsDynamic)
    SELECT @CaptureId, name, value, value_in_use, is_dynamic
    FROM sys.configurations;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Test the history capture in a nonproduction database first. Verify that it stores the intended instance's values and that failures remain visible. Run the same capture twice on the test instance before comparing its retained values.

Compare the Last Two Captures

Choose captures from the same instance before comparing values. The query below selects its latest two retained captures. Run the capture script a second time first. With a single capture, the query stops on purpose with error 51131.

DECLARE @InstanceName sysname = CONVERT(sysname, SERVERPROPERTY('ServerName'));
DECLARE @Latest bigint = (SELECT MAX(CaptureId)
    FROM dbo.ConfigurationCapture WHERE InstanceName = @InstanceName);
DECLARE @Prior bigint = (SELECT MAX(CaptureId)
    FROM dbo.ConfigurationCapture
    WHERE InstanceName = @InstanceName AND CaptureId < @Latest);
IF @Prior IS NULL
    THROW 51131, 'Two captures from this instance are required.', 1;
;WITH PriorValues AS
(
    SELECT * FROM dbo.ConfigurationValue WHERE CaptureId = @Prior
), LatestValues AS
(
    SELECT * FROM dbo.ConfigurationValue WHERE CaptureId = @Latest
)
SELECT COALESCE(a.SettingName, b.SettingName) AS SettingName,
       b.ConfiguredValue AS PriorConfigured, a.ConfiguredValue AS LatestConfigured,
       b.ActiveValue AS PriorActive, a.ActiveValue AS LatestActive,
       CASE WHEN b.SettingName IS NULL THEN N'Added'
            WHEN a.SettingName IS NULL THEN N'Removed'
            ELSE N'Changed' END AS DifferenceKind
FROM LatestValues AS a
FULL JOIN PriorValues AS b ON b.SettingName = a.SettingName
WHERE a.SettingName IS NULL OR b.SettingName IS NULL
   OR a.ConfiguredValue <> b.ConfiguredValue OR a.ActiveValue <> b.ActiveValue
   OR a.IsDynamic <> b.IsDynamic
ORDER BY SettingName;

A changed value is a finding to explain, not a command to revert. Compare the change log and approval record. A deliberate improvement and an accidental drift need different decisions.

Show capture timestamps alongside the differences when reviewing them. The latest pair doesn't necessarily correspond to the deployment you're investigating. Select explicit capture identifiers when a particular event requires a before-and-after comparison.

No differences means the stored configuration values agree. It doesn't prove that workload or query plans stayed unchanged. Use the other diagnostic families for those questions.

Turn Performance Tuning Scripts Into a Baseline Habit

Run approved diagnostic captures on a suitable schedule. A SQL Server Agent job can write selected history into a small monitoring table. Check the job's execution identity and error reporting before enabling it.

A SELECT-only source query and a history-writing capture have different permissions and effects. Label that difference in the header. The operator should know whether the script writes retained data before running it.

Retain enough history for the comparisons your team uses. Define a cleanup process instead of letting every snapshot accumulate forever. A useful baseline has a maintained horizon and known coverage.

Repeat the checks after a deployment, cumulative update, version upgrade, or hardware move. Keep the change's timestamp with the captures. Matching evidence makes the next investigation easier to reconstruct.

Put the undo script next to the change script. Save its original input values rather than reconstructing them later. Yesterday's setting has a short memory when nobody wrote it down.

Respect Counter Lifetimes and Suggestion Limits

Wait and file counters reset across engine restarts. Index usage also has reset conditions, including engine startup. Keep the engine lifetime with those results before calling an index unused.

An index used for a monthly operation can look idle during a short observation window. Include that business cycle before considering removal. Review constraint support separately from recorded seek and scan counts.

Missing-index suggestions can overlap and don't complete the comparison with existing indexes. They don't price the full write and maintenance cost. Treat them as hints to consolidate and test, never as a bulk creation list.

Query Store is per database and needs active capture to help. SQL Server 2022 enables it by default for newly created databases. Existing upgraded or restored databases still require an actual-state check.

When history is absent, inspect capture settings and storage limits. Don't interpret missing records as proof that no expensive queries ran. An incomplete collection needs a visible limitation in the findings.

Deadlock graphs also contain statement and resource context that deserves careful handling. Inspect retained system_health events through SSMS before sharing their contents. Retention is bounded, so preserve approved evidence when investigating an incident.

Explain What the Next Operator Should Do

Which script would the next DBA choose when the symptom returns? Put that question in the folder's short guide. Explain the first read-only check and the condition that warrants deeper investigation.

A known symptom still needs a current cause check before reapplying a change. Workloads and versions evolve. Reuse the diagnostic process rather than treating an old setting as a universal answer.

The customer who paid once should retain the know-how to handle that cause again. My goal is to teach that capability, not withhold the script. Follow-ups remain useful when the evidence or question changes.

Keep performance tuning scripts as operational materials your team understands. See the Consulting page if you want help building that practice. The scripts, checklists, prompts, and resulting action plan stay with you.

Related reading on this blog: Wait Stats Collection Scripts : Updated March 2021 and Writing Rerunnable Deployment Scripts for Database Changes.

A script you keep is not a leftover from the session, it is a check your team can run again.

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