A bigger server is rarely the right answer
When a database is slow, the first reflex is often to add memory or cores. That is expensive, especially with SQL Server licensed per core, and the gain is often disappointing: a query that reads a hundred times more pages than it needs will do the same on a brand-new server. In the vast majority of cases, a few queries, a few indexes and a few settings account for most of the problem.
SQL Server tuning: our four-step approach
- Measure. Wait statistics, Query Store, Extended Events and system performance counters: we establish a quantified baseline before touching anything.
- Isolate. The queries and objects that actually consume CPU, reads and wait time, ranked by impact.
- Fix. Indexes, statistics, query rewrites, instance settings, isolation level. Every change is tested and reversible.
- Verify. A before-and-after comparison on the same metrics. If the gain isn't there, we see it right away.
The causes we run into most often
Indexing: missing, ill-suited or too many indexes
A missing index forces full scans of large tables. Conversely, dozens of indexes that are never read slow down every insert and update. SQL Server's missing index suggestions are a starting point, not a list to apply as is.
Unstable execution plans
Parameter sniffing is how the same stored procedure can be fast in the morning and disastrous in the afternoon. Query Store lets us identify these regressions and stabilize plans without changing the code.
Queries that prevent index use
Implicit type conversions, functions wrapped around filtered columns, scalar functions called row by row, cursors where a set-based query would do. Our lead DBA has even written about SQL Server cursor performance problems for SQLShack.
Blocking and deadlocks
Transactions that run too long, the wrong isolation level, two processes that access the same objects in a different order: any one of these can freeze an application. Row-versioning isolation (RCSI) solves many of these cases, provided tempdb is sized accordingly.
A poorly configured instance
Max server memory left uncapped, poorly calibrated parallelism, an undersized tempdb, statistics never updated on large tables, storage latency. These settings are quick to fix and benefit every application on the instance.
Line-of-business applications and packaged software
Many companies run software on SQL Server whose code they don't control: ERP and business management suites (Sage 100, Sage X3, Cegid, Microsoft Dynamics), payroll or industry-specific applications. When “Sage is slow” or a period-end closing job never seems to finish, the cause is very often on the database side. We can almost always speed these applications up without touching them, by working on indexes, maintenance and the instance, and by giving the vendor a precise diagnosis when the fix is theirs to make.
Results you can verify
A SQL Server performance tuning engagement is judged on numbers. The target is set at the outset in concrete terms: how long a screen takes to load, how long an overnight job runs, CPU usage at peak hours. You know what was done, why, and what it delivered. If you're not sure where to start yet, a SQL Server audit gives you the big picture before digging into the details.
