TABLE OF CONTENTS
- •Want to fix performance problems for good?
- •1. Restarting First
- •2. Everyone Intervening at Once
- •3. Clearing the Plan Cache and Buffer Pool
- •4. Setting MAXDOP to 1 Server-Wide
- •5. Applying Missing Index Suggestions Blindly
- •6. Rebuilding Every Index Every Night
- •7. Adding NOLOCK to Every Query
- •8. Shrinking the Database
- •9. Changing Without Measuring
- •10. Not Looking at the Execution Plan
- •11. Reaching for Hardware First
- •12. Forgetting Backups and CHECKDB While Talking Performance
- •Improve by measuring, not guessing
A good share of SQL Server performance problems grow less from the problem itself than from the reaction to it. A server restarted in panic, indexes added without thought, parallelism switched off server-wide, five changes made at once without measuring: all of them feel like doing something in the short term, and in the long term they hide or worsen the problem.
This article collects the performance tuning mistakes we see most often in the field, why they hurt, and what to do instead.
Want to fix performance problems for good?
Aryasoft finds the problem by measuring, fixes the root cause and proves the improvement with numbers.
1. Restarting First
A restart wipes out the evidence (running requests, wait statistics, the plan cache). If long transactions are running, the database goes into a long recovery, and the outage lasts longer than it would have before the restart. Look inside first; if the server isn’t responding, connect through the DAC.
2. Everyone Intervening at Once
In a crisis, one person changes the VM’s resources, another kills sessions, another clears the plan cache, another lowers MAXDOP. In the end nobody knows which step changed what. Let one person lead, and log every step. Our outage runbook describes this approach.
3. Clearing the Plan Cache and Buffer Pool
DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS don’t fix anything in production: every query recompiles, data is read from disk again, and a short CPU and I/O storm follows. If there’s one bad plan, target only that plan, or force the good plan with Query Store.
4. Setting MAXDOP to 1 Server-Wide
This reaction to high CXPACKET waits switches parallelism off completely; reports, index maintenance and CHECKDB slow down dramatically. The right approach is to bring MAXDOP and cost threshold into the recommended range and look at the expensive queries running in parallel.
5. Applying Missing Index Suggestions Blindly
Missing index DMVs and the green hints in execution plans are produced by looking at a single query. Applying them all leads to dozens of similar indexes on the same columns, slower writes and bloated maintenance. Evaluate suggestions together with existing indexes; details are in our index guide.
6. Rebuilding Every Index Every Night
Rebuilding every index nightly regardless of fragmentation bloats the log, puts unnecessary load on Always On secondaries and fills the maintenance window. Maintenance that decides based on fragmentation and page count, plus regular statistics updates, is far more efficient.
7. Adding NOLOCK to Every Query
NOLOCK (READ UNCOMMITTED) reduces blocking but can cause dirty reads, reading the same row twice or skipping rows. In financial reports, a wrong result costs more than a lock wait. For read-write conflicts, read committed snapshot isolation (RCSI) is usually a much safer fix.
8. Shrinking the Database
Regular shrinking to gain disk space causes serious index fragmentation, and the file grows again afterwards. See our shrink guide.
9. Changing Without Measuring
Without a baseline, nobody knows whether a change worked. Take the same measurement (query duration, reads, CPU, wait statistics) before and after a change, and change one thing at a time.
10. Not Looking at the Execution Plan
The answer to why a query is slow is usually in its plan: large table scans, implicit type conversions, big gaps between estimated and actual row counts. Tuning without reading the plan is guesswork. See our slow query guide to find the expensive queries.
11. Reaching for Hardware First
More CPU or memory can hide an inefficient query for a while, but it doesn’t fix it; and because SQL Server is licensed per core, it creates a permanent cost. Measure the workload first.
12. Forgetting Backups and CHECKDB While Talking Performance
In reviews that start with a performance complaint, the two problems we find most often aren’t about performance at all: there’s no backup plan matching the business’s RPO and RTO, and DBCC CHECKDB doesn’t run regularly. A fast database you can’t recover is a far bigger risk than a slow one.
Critical
When corruption is discovered, you need a clean backup from before CHECKDB’s last successful run and every log backup after it. If CHECKDB hasn’t run for weeks and log backups don’t go back that far, data loss may be unavoidable. Verify the backup chain and CHECKDB before starting performance work.
Tip
During an outage, the places our team checks first are, in order: monitoring alerts, the error log, active sessions, Agent jobs, resource usage, disk latency and how CPU is spread across logical processors. That order shows within minutes where the problem is, before anyone intervenes in a panic.
Related Reading
SQL Server Performance Consulting
Improve by measuring, not guessing
Aryasoft’s senior DBA team finds the problem through wait statistics, execution plans and a baseline, applies every change with measurement, and checks the basic safeguards such as backups and CHECKDB too.
