The complaint arrives as "the system is slow". Sometimes it comes with a specific screen. More often it comes with a time of day — the last week of the month, or every morning at nine. By the time it reaches us, somebody has already found a slow query, added an index, and the system was fine for a few weeks.
That cycle — find the worst query, index it, repeat — is not wrong. It just never ends, because it treats the symptom of the week. In a system that has been running for ten years, slowness is almost always structural, and the structure is visible if you look in the right places first.
Start with what the server is waiting on
Not the slowest query. What the server is waiting on.
SQL Server keeps a running account of every wait — for disk, for locks, for memory, for CPU — and one query against the wait statistics tells you which kind of problem you have. This changes what you do next. If the instance is waiting on locks, no index will help and the answer is in how long transactions are held open. If it is waiting on disk, the answer is in which queries are reading far more than they return. If it is CPU, the usual suspects are plans that were compiled for the wrong parameters, or a function wrapped around a column in a WHERE clause that turns an index seek into a scan of the whole table.
Then look at queries ranked by total reads and total executions, not by duration. A query that takes 200 milliseconds but runs forty thousand times an hour matters far more than the report that takes thirty seconds once a day. The one that looks harmless is almost always the culprit, and it never appears on anyone's list of slow queries because individually it is not slow.
The four things we find most often
Reporting on the transactional database. The month-end report scans every order ever placed, holding shared locks, while the sales team is trying to save orders. This is why "slow at month end" is such a common complaint. The fix is not an index. It is a separate path for reporting — a nightly copy, a read replica, or at minimum snapshot isolation so readers stop blocking writers.
Indexes added in a panic. Twelve indexes on the order table, six of them overlapping, each one added to fix one query on one bad day. Every insert and update now maintains all twelve. Consolidating them is often the single biggest win and the most nerve-wracking, because removing an index feels like removing a safety rail. SQL Server's own "missing index" suggestions, applied literally, are how most of these got there.
Plans compiled for the wrong customer. A stored procedure that is fast for most accounts and catastrophic for the biggest one, or fast until a restart and slow afterwards. The symptom is intermittent, unreproducible, and reported as "it was fine yesterday". The cause is a query plan built for one set of parameters and reused for a very different one. It is well understood and has several fixes; the right one depends on the procedure, and it is rarely the query hint someone found on a forum.
Application chatter. The screen that runs one query per row: five hundred round trips to render a grid. No amount of SQL tuning fixes this, because every individual query is already fast. It shows up in the statistics as thousands of tiny queries and in the office as one slow screen. In newer code it is usually an ORM loading related data lazily; in older code it is a loop in a code-behind file that nobody has looked at since it was written.
Behind these four there is a familiar supporting cast: parameters declared as nvarchar compared against varchar columns, which quietly disables the index; SELECT * over tables with large text or binary columns; and a "temporary" table that has been growing since 2017 and has never had a maintenance job.
The one session
Most of this comes out of a single structured session of a few hours, on production, using only the instance's own diagnostic views — nothing installed, nothing traced, nothing that adds load. Wait statistics. Top queries by reads, CPU and execution count. Index usage, to find the ones never read and the ones that duplicate each other. The plan cache, searched for scans and implicit conversions. And the blocking chain during the busiest hour.
The output is a ranked list. In our experience the top three items on it account for most of the pain, and none of the three is usually the query the client first mentioned.
Change one thing at a time
The same discipline applies here as to any change in a live system: measure, change one thing, measure again. If you consolidate the indexes, enable snapshot isolation and move the report to a replica over the same weekend, and Monday is fast, you have learned nothing you can reuse. If Monday is slow, you cannot tell which change did it.
It is slower. It is also the only approach that produces a system anyone understands afterwards.
What good looks like
The system is not "fast". Fast is relative and the goalposts move. The system is predictable: the busy hour behaves like the quiet hour, month-end does not stop sales, and when something does slow down there is a baseline to compare against and a known place to look.
The index-of-the-week cycle stops — not because there are no more slow queries, but because they no longer surprise anyone.