Summary: Wait statistics, Query Store, indexes, memory and MAXDOP: the order of checks we use in the field to find the cause of a slow SQL Server.

A "the database is slow" complaint rarely comes from a single query. It usually comes from several small problems stacking up. Knowing where to look before adding RAM or cores to the server saves both time and budget.

The order below summarises the checklist we use in Microsoft SQL Server environments. Every step can be done with read-only permissions.

1. Start with wait statistics

SQL Server records what each query waits on. The sys.dm_os_wait_stats view shows where the server spends its time: disk (PAGEIOLATCH), locks (LCK_M_*), parallelism (CXPACKET / CXCONSUMER) or memory (RESOURCE_SEMAPHORE). The wait profile tells you which area to focus on next.

2. Find plan regressions with Query Store

If a query that ran fast yesterday is slow today, its plan has usually changed. On SQL Server 2016 and later, with Query Store enabled, you can compare a query's plans and duration variance side by side. A forced plan that silently fails also shows up here.

3. Remove anti-patterns from query text

Some writing habits prevent index use:

  • Leading wildcard LIKE: LIKE '%abc' turns an index seek into a scan.
  • Functions on columns in WHERE: write a date range instead of WHERE YEAR(OrderDate) = 2026.
  • NOT IN with NULLs: can return unexpected results and poor plans; NOT EXISTS is safer.
  • Scalar UDFs and cursors: process row by row and block set-based execution.
  • SELECT *: reads unneeded columns and defeats covering indexes.

4. Check index health

Missing-index suggestions, indexes that are never read but updated on every write, and indexes that duplicate the same columns should be reviewed together. Adding every suggestion blindly slows down writes.

5. Review memory and parallelism settings

Two settings left at their defaults often cause trouble. If Max Server Memory is unlimited, the operating system runs short of memory. If MAXDOP and cost threshold for parallelism stay at their defaults, small queries run in parallel for no reason. The right values depend on the server's core count, NUMA layout and total RAM.

6. Confirm maintenance jobs actually run

Look at the last results of statistics updates, index maintenance, DBCC CHECKDB and backup jobs. A maintenance job that is scheduled but has been failing for months is one of the most commonly missed problems.

7. Read the security configuration in the same pass

A performance review is a good moment to check configuration: who holds sysadmin, whether SQL Audit is defined and running, whether risky features such as xp_cmdshell are enabled, and whether any logins have the password policy turned off.

Practical tip

Change one thing at a time and measure after each step. If you change three settings at once, you won't know which one helped. Run fix scripts in a test environment first.

Making these checks routine

Cardinal runs these checks on a schedule with a least-privilege SQL login (VIEW SERVER STATE, VIEW DATABASE STATE, VIEW ANY DEFINITION) and rolls the results into a 0–100 SQL Health Score. Its Query Advisor produces before → after suggestions for 14 anti-patterns; in a measured customer example, one query dropped from 8.2 seconds to 1.7 seconds. Fix scripts are calculated for your server but never run automatically.

Find out your SQL Server's health score

Let's run the first health scan together and get the findings and prioritised recommendations as a report.

Request a health scan