Most SQL Server consulting engagements begin the same way. An application that used to be fast is now slow, the problem has been getting worse for months, and someone has already suggested buying a bigger server.

Sometimes the server really is the problem. More often, in our experience, it is not — and the business is about to spend five figures on hardware that will make a badly written query slightly less slow.

We have built and maintained SQL Server systems since 2003, including an operations platform that has run continuously for over twenty years for electric utility and recycling companies. Databases we designed early in that period are still in production, carrying two decades of accumulated data and schema changes. That kind of longevity teaches you where these systems actually break.

Here is how to work out what is wrong with yours.

Start with wait statistics, not with guesses

SQL Server records what it spends its time waiting on. Before touching an index or a query, look at that data. It tells you whether the server is waiting on disk, on CPU, on memory, on locks, or on something outside the database entirely.

This matters because the symptom and the cause are frequently unrelated. Users report that the application is slow. That could be a missing index, a blocking chain, a network problem, an antivirus product scanning database files, or a report someone scheduled at 9 a.m. that scans a table with forty million rows. All of these feel identical from the user’s chair.

Skipping this step is why so much money gets spent on the wrong fix. Adding RAM to a server that is waiting on locks does nothing at all.

The settings that are often worth reviewing

SQL Server includes default settings designed to work across a wide range of environments. In many systems, those defaults are a reasonable starting point, but they are not always the best fit for a specific workload. Before changing anything, the current workload should be measured and tested.

• Cost threshold for parallelism defaults to 5. In some modern environments, that can be low enough to cause relatively small queries to use parallel execution unnecessarily. Many systems benefit from a higher value, but the right setting depends on the workload and should be tested rather than applied universally.
• MAXDOP controls how many processors a single query can use. The best value depends on factors such as CPU count, NUMA configuration, workload type, and SQL Server version. It should be configured deliberately rather than assumed.
• tempdb configuration can become a bottleneck under heavy concurrent workloads. Multiple equally sized data files are commonly recommended as a starting point, but the number of files should be based on actual contention and system behavior rather than a fixed rule.
• Autogrowth settings should be reviewed to make sure database files grow in predictable increments. Fixed-size growth settings are often easier to manage than percentage-based growth, especially for larger databases.
• Maximum server memory should normally be configured so SQL Server does not consume memory needed by Windows and other services. The correct amount depends on the server’s total memory and what else runs on it.

None of these settings should be changed simply because they appear on a checklist. They are configuration areas worth reviewing after measuring the environment, and in the right situation they can improve performance without requiring new hardware or application code.

Indexes: the missing ones matter more than the fragmented ones

Index maintenance jobs that rebuild every index nightly are common and largely ceremonial. Fragmentation matters far less on modern storage than it did on spinning disks, and rebuilding a large index every night generates transaction log volume and blocking for limited benefit.

What actually costs you is different:

• Missing indexes on columns your queries filter and join on, forcing scans of entire tables.
• Duplicate and overlapping indexes that must all be updated on every insert and update, slowing writes to no benefit.
• Unused indexes that carry write cost and return nothing.
• Out-of-date statistics, which cause the query optimizer to choose a plan based on a distribution of data that no longer exists. This one produces the classic complaint of a query that was fine last month and is unusable today, with nothing having changed.

SQL Server tracks index usage and reports its own missing-index suggestions. Those suggestions need judgment applied — they are generated per query and taken literally will produce a dozen near-identical indexes — but they are a legitimate starting point.

Where SQL Server consulting usually finds the real problem

In practice, the most expensive issues we find are rarely exotic:

A single query. Frequently one report or one nightly process, written years ago against a much smaller table, now scanning tens of millions of rows and blocking everything else while it runs.

Blocking, not slowness. Long-running transactions holding locks while users wait. The database is not busy — it is stuck. These look like performance problems and are actually design problems.

Parameter sniffing. A stored procedure that runs fast for one input and slowly for another, because SQL Server cached a plan built for the first set of parameters. Intermittent, hard to reproduce, and maddening to diagnose without the right tools.

Plan regression after an upgrade. Query Store, available since SQL Server 2016, records execution plans over time and allows forcing a previous plan. It is off by default in older versions and is one of the most useful features most businesses are not using.

A database in the wrong recovery model. Production databases running in simple recovery cannot be restored to a point in time no matter what the backup software reports — a point worth reading alongside our guide to data backup and recovery.

Version support is a real deadline, not a nag

Older SQL Server versions reach end of support on published dates, after which no security updates are issued. The same applies to the Windows Server underneath — and running a current database on an unsupported operating system leaves the whole stack exposed. If you have not checked recently, our guide to Windows Server 2012 R2 end of life covers the current dates.

Check both your SQL Server version and the operating system it runs on against Microsoft’s SQL Server documentation and lifecycle pages. Upgrades are also an opportunity — the query optimizer has improved substantially across recent versions, and some long-standing performance complaints simply resolve.

What to ask a SQL Server consultant

If you are evaluating help, the questions that separate substance from sales:

• What will you measure before making changes, and how will we know whether it worked?
• Will you show us the wait statistics and the execution plans, or just present conclusions?
• Are you recommending configuration changes, query changes, or hardware — and why in that order?
• What can we maintain ourselves after you leave?

A consultant who recommends hardware before showing you a wait statistics analysis is guessing.

Where ITsoft fits

Our Microsoft SQL Server database development work covers performance diagnosis, schema and query design, migrations, and long-term support — for businesses running databases their operations genuinely depend on, not just spreadsheets that grew up.

We start every engagement by measuring, because we would rather tell you the fix is a configuration change than sell you a project.

Talk to Mike Treat about a SQL Server performance review — we will analyze what your server is actually waiting on and give you a prioritized list of fixes, ranked by effort against expected gain.

Related reading: data backup and recovery · Windows Server setup and maintenance · managed service provider services