Consulting 105 – Why Don’t I Want My Customers to Return for the Same Problem?

I want you to handle the same problem yourself next time. You leave with reusable checks and a clear explanation of the cause. A recurring symptom still deserves a fresh evidence check.

This is part 5 of my six-part consulting series. The goal is capability your team retains, while follow-ups and new questions remain welcome.

Solve the Same Problem Once, Then Keep the Plan

Think of a doctor who provides a care plan instead of requiring a daily visit for the same explanation. The useful comparison is the retained plan. Its instructions remain available after the appointment ends.

Database work needs that retained understanding too. You should know which checks establish the cause and what the approved action changes. Keep the recovery steps beside the fix.

I prefer teaching that process once to withholding the next step. It gives your team a way to investigate independently. Calling again should add useful expertise rather than recover missing instructions.

A recurring symptom doesn't prove the original cause was never found. The cause can be reintroduced, or a different cause can produce similar symptoms. Start by confirming what the current evidence says.

A Restart Leaves the Cause Unexplained

Restarting SQL Server clears the plan cache and changes the engine's memory state. It also interrupts service and resets important diagnostic evidence. A brief improvement afterward doesn't identify which change relieved the symptom.

Look for the troublesome plan or memory condition while the problem exists. Query Store can preserve plan and runtime history when it's enabled and capturing. Keep instance configuration evidence beside that query-level history.

I check the engine's startup time before interpreting DMV totals. A low total after a restart doesn't demonstrate low activity across the earlier business period. The counter has a shorter memory than the complaint.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
SELECT name, value AS ConfiguredValue, value_in_use AS ActiveValue
FROM sys.configurations
WHERE name IN (N'max server memory (MB)', N'max degree of parallelism');

SELECT DB_NAME() AS DatabaseName, actual_state_desc, desired_state_desc,
       current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Use the affected database for the Query Store query. Inspect whether its history covers the incident before relying on it. A write-state preference doesn't establish that the useful plans were retained.

A restart can be necessary for another approved operational reason. That decision still needs its own plan. Don't make repeated restarts the diagnostic method for unexplained performance pressure.

An Index Rebuild Changes More Than Fragmentation

A query speeding up after a rebuild doesn't prove fragmentation caused the earlier delay. The operation also affects index statistics and dependent plan reuse. Investigate those effects before choosing a permanent maintenance response.

For a nonpartitioned rowstore index, rebuilding refreshes its index statistics with a full scan. Other table statistics aren't refreshed by that operation. Partitioned-index statistics follow different sampling behavior. Statistics changes can trigger recompilation.

Inspect statistics freshness and the plan's estimated versus available actual row counts. Check whether representative parameter values choose different work. Parameter sensitivity needs a controlled comparison rather than a blanket cache clear.

SELECT OBJECT_SCHEMA_NAME(s.object_id) AS TableSchema,
       o.name AS TableName, s.name AS StatisticsName,
       STATS_DATE(s.object_id, s.stats_id) AS StatisticsUpdatedAt,
       s.auto_created, s.user_created
FROM sys.stats AS s
JOIN sys.objects AS o ON o.object_id = s.object_id
WHERE o.type = 'U'
ORDER BY TableSchema, TableName, StatisticsName;

A NULL date means the statistic holds no data yet. I saw that on an index created over an empty table.

An update timestamp doesn't show whether a histogram describes the relevant skew accurately. Review the statistic's distribution when the plan points that way. Keep the before and after plans for the tested parameter values.

Choose index maintenance based on the object and workload, including resource cost. Don't rebuild repeatedly to obtain a temporary new plan. The repeatable fix needs an explanation of what changed and why.

A Growing Log Needs Its Reuse Blocker Found

Shrinking a log file reduces physical space only when reusable space permits it. It doesn't remove the condition preventing log reuse. The file grows again when the workload needs that capacity.

Read log_reuse_wait_desc before discussing the file size. FULL recovery with missing log backups and long active transactions are common leads. Other reuse blockers require different investigations.

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases WHERE database_id = DB_ID();
DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS;

Run this in the affected database under an authorized account. DBCC OPENTRAN reports the oldest active transaction rather than changing it. With no open transaction, it returns no rows. Its permissions require the database owner role or system administrator role.

LOG_BACKUP points toward reviewing the log-backup process and recovery requirements. ACTIVE_TRANSACTION points toward the transaction and its owner. Don't switch recovery models or terminate a session to silence the symptom without that review.

Separate logical reuse from physical file sizing. After addressing the blocker, choose capacity and growth appropriate to the workload. Repeated shrink-and-grow cycles consume work without providing a lasting capacity plan.

A Missing-Index Suggestion Needs a Predicate Review

A suggested index doesn't prove the existing design is the only cause. Inspect the predicate and parameter types first. A function on the stored column can obstruct a straightforward indexed range access.

The synthetic example compares a year expression with a half-open timestamp range. It keeps the requested calendar year the same. Run both queries and inspect plans on an approved representative test copy.

CREATE TABLE #PredicateEvents
(
    EventId int NOT NULL PRIMARY KEY,
    RecordedAt datetime2(7) NOT NULL
);
CREATE INDEX IX_PredicateEvents_RecordedAt ON #PredicateEvents (RecordedAt);
INSERT #PredicateEvents VALUES
(1, '2024-12-31T23:59:59.9999999'),
(2, '2025-01-01T00:00:00'),
(3, '2026-01-01T00:00:00');
SELECT EventId, RecordedAt FROM #PredicateEvents
WHERE YEAR(RecordedAt) = 2025;

DECLARE @Start date = '2025-01-01';
DECLARE @End date = DATEADD(year, 1, @Start);
SELECT EventId, RecordedAt FROM #PredicateEvents
WHERE RecordedAt >= @Start AND RecordedAt < @End;

The tiny fixture demonstrates boundary selection rather than a measured speed improvement. A useful range predicate still doesn't guarantee a seek. The optimizer considers selectivity and the rest of the query.

Check implicit-conversion warnings and whether conversion is applied to the indexed column. Match parameter types to the column where the contract permits it. Conversion effects depend on the types and collation involved.

Then compare the suggested index with existing keys and included columns. Consolidate overlapping suggestions and consider write cost. Don't create every suggestion in bulk because it appears in a plan.

Endless Deadlock Retries Preserve the Conflict

A deadlock involves a cycle of resource dependencies between concurrent transactions. Retrying after a victim error can be part of an application design. Unlimited retries leave the conflicting access pattern untouched.

Read the graph to identify participating statements, resources, and access order. Check transaction length and supporting indexes. Consistent access order and shorter transactions are specific changes to evaluate against that evidence.

The built-in system_health session retains detected deadlock graphs on SQL Server. Its history is bounded, so yesterday's graph isn't guaranteed to remain. The following file-reading query uses fields available from SQL Server 2017 onward.

SET QUOTED_IDENTIFIER ON;
SELECT TOP (10) x.timestamp_utc,
       d.EventXml.query(
           '(event/data[@name="xml_report"]/value/deadlock)[1]') AS DeadlockGraph
FROM sys.fn_xe_file_target_read_file(
    N'system_health*.xel', NULL, NULL, NULL) AS x
CROSS APPLY (VALUES (CONVERT(xml, x.event_data))) AS d(EventXml)
WHERE x.object_name = N'xml_deadlock_report'
  AND x.timestamp_utc >= DATEADD(day, -1, SYSUTCDATETIME())
ORDER BY x.timestamp_utc DESC;

The first line matters in sqlcmd, which starts with QUOTED_IDENTIFIER off, and the XML method fails without it. The relative pattern uses SQL Server's default log location. Inspect the session's actual event-file target if your environment uses another path. Protect statement text and identifiers contained in the graph.

Use bounded application retries with an approved delay policy for transient victim errors. Separately test the root-cause change under representative concurrency. A successful retry alone doesn't explain the original cycle.

When the Same Problem Comes Back

When the same problem reappears, begin with the saved read-only checks from Consulting 104. Compare current evidence with the original finding. Confirm the engine lifetime and the workload before reusing its approved action.

Which part of the old diagnosis still matches today's evidence? The first response becomes a bounded investigation your team can perform. It doesn't promise a fixed number of minutes. The current cause and the available evidence determine the next step.

Keep the reasoning alongside the script so a different operator can follow it. Explain what would invalidate the earlier diagnosis. Reuse becomes safer when the limits are visible.

Many diagnostic principles remain useful across SQL Server versions. Scripts still need their documented columns, permissions, and syntax reviewed after an upgrade. Familiar checks deserve a compatibility review rather than blind execution.

Guardrails That Stop the Same Problem Returning

Retain a baseline covering the important workload periods. Keep Query Store capturing useful history in the databases that matter. Check its actual state and coverage rather than assuming an enabled preference is enough.

Maintain indexes through a reviewed routine suited to the objects and workload. Keep evidence for its choices and cost. A maintenance job shouldn't become the unexplained treatment for every slow query.

Keep a change log with the before-value, approved action, undo path, and acceptance check. Rerun key diagnostics after deployments and cumulative updates. A reintroduced setting or plan change is easier to identify against that record.

Assign each guardrail an owner and a check frequency. Collection failures need attention before the next incident. An empty history is a limitation, not a clean bill of performance.

Come Back for the Next Useful Question

My goal is that the same problem never needs another consultation because your team lacks the know-how. New or harder problems and follow-ups are welcome. So are second opinions before a migration or design decision.

The On-Demand Expert Hour costs USD 650 per hour. It also supports checking a vendor's or AI tool's recommendation. Bring the evidence and the decision that needs review.

AI gets checked, not trusted. The AI Enablement Workshop for Data Teams costs USD 2,432 and runs up to four hours on Zoom. It provides another area to learn instead of repeating an already understood diagnostic lesson.

For help with a new question or a follow-up, see the Consulting page. Your scripts and understanding should make you more independent. That independence leaves room for the next problem worth examining together.

Related reading on this blog: Finding the Root Cause of Slow Queries and SQL SERVER Performance: When MAXDOP = 1 Slowed Down the Entire Business.

A lasting fix is not repeated symptom relief, it is an understood cause with checks that keep it controlled.

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