Blog / SQL Server Performance Tuning Checklist for Non-DBAs
Performance

SQL Server Performance Tuning Checklist for Non-DBAs

Candidus Data Lab · 9 min read

If you're an IT director or ops lead who inherited a SQL Server database with no dedicated DBA, "it's slow" is usually where the conversation starts and stalls. This is a checklist you can actually run yourself, in plain language, before deciding whether you need to bring in help.

This is diagnostic, not a fix-it guide. The goal is to figure out what kind of problem you have — hardware, query, indexing, or something structural — so any next step (yours or a specialist's) is aimed at the right target.

1. Rule out the obvious first: is it actually the database?

  • Check CPU, memory, and disk queue length on the server itself (Task Manager or Performance Monitor). Sustained CPU above 80%, memory pressure, or high disk queue length points to a resource problem, not necessarily a query problem.
  • Confirm it's actually the database and not the network or application layer — a quick way: run the slow query directly in SQL Server Management Studio and compare its execution time to what the application reports.
  • Check if the slowness is constant or tied to specific times (batch jobs, backups, reporting queries) — timing tells you a lot about the cause before you look at a single query.

2. Find the actual slow queries, don't guess

If you're on SQL Server 2016 or later, Query Store is the fastest path — it's built in, and it tracks query performance over time without you having to set up anything complex.

  • Open Query Store (right-click the database in SSMS → Reports → Query Store → Top Resource Consuming Queries).
  • Sort by duration and by CPU — the worst offenders are usually obvious within a few minutes of looking.
  • If Query Store isn't enabled, turn it on — it's a low-overhead, high-value setting that should be on by default for any production database.

3. Check for missing indexes — this alone fixes a surprising number of "slow database" complaints

SQL Server actually tracks what indexes it thinks would help, based on real query patterns. Run this query to see its suggestions:

  • Query sys.dm_db_missing_index_details along with sys.dm_db_missing_index_group_stats for an impact-ranked list of missing indexes.
  • Don't blindly add every suggestion — SQL Server's missing-index feature doesn't account for write overhead or index overlap. Prioritize the highest-impact ones and check for near-duplicate existing indexes first.
  • If you're not confident interpreting this, this is exactly the kind of finding worth a second opinion before making changes to a production database.

4. Look at execution plans for your worst queries

You don't need to be a DBA to spot the biggest red flags in an execution plan:

  • Table scans / clustered index scans on large tables where you'd expect a seek — usually means a missing or unused index.
  • Big gap between "Estimated" and "Actual" rows — a sign statistics are out of date, which misleads the query optimizer into bad decisions.
  • Key Lookup operations with high cost — often fixable with a covering index.

5. Check when statistics were last updated

Outdated statistics are one of the most common, most invisible causes of "the database used to be fast and now it isn't." Query sys.dm_db_stats_properties to see the last update date and modification counts. If auto-update statistics is off, or if a large table hasn't had stats updated recently, that's a strong lead.

6. Confirm backups and maintenance jobs aren't the actual cause

It's common for "the database is slow" to actually mean "the database is slow during the nightly backup/reindex/statistics job window." Check your SQL Server Agent job history against when the slowness gets reported — if they line up, the fix might be scheduling, not tuning.

When to bring in help

This checklist finds the obvious problems. It won't help much with deeper issues — parameter sniffing, blocking chains, memory grant problems, or a schema that needs real redesign. If you've worked through this list and the picture is still unclear, or the fix looks like it touches production in a way you're not confident making alone, that's the point where a second set of senior eyes is worth more than more guessing.

Most "the database is slow" tickets aren't mysterious once someone actually looks at Query Store and the missing-index DMVs. The mystery is usually just that nobody's looked yet.

Worked through this and still stuck?

Free 20-minute fit call — bring what you found, get a straight read on next steps.

Book a free fit call