Skip to content
DBAExperts
SQL Server Diagnostics

How to find slow queries in SQL Server (Query Store and DMVs)

Before optimizing, you have to measure. With Query Store and DMVs, you can pinpoint exactly which queries consume the most CPU, I/O, or time.

  1. 1

    Enable Query Store

    Query Store keeps a history of queries and plans. It is the most practical tool since SQL Server 2016. Enable it per database.

    ALTER DATABASE [MiBase] SET QUERY_STORE = ON
     (OPERATION_MODE = READ_WRITE);
  2. 2

    Find the most expensive queries with Query Store

    Query the Query Store views to see queries by duration, CPU, or reads. You also get them in the graphical report in SSMS.

    SELECT TOP 20
  qt.query_sql_text,
  rs.avg_duration / 1000.0 AS avg_ms,
  rs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
  rs.count_executions
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;
  3. 3

    Alternative without Query Store: sys.dm_exec_query_stats

    Shows query statistics from the plan cache. It resets when the cache is cleared or the server restarts, so it is a snapshot of the moment.

    SELECT TOP 20
  qs.total_worker_time / qs.execution_count AS avg_cpu,
  qs.total_elapsed_time / qs.execution_count AS avg_duracion,
  qs.execution_count,
  SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 200) AS consulta
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_cpu DESC;
  4. 4

    Review the execution plan

    The plan tells you where the problem is: full table scans, spills to tempdb, bad estimates, or missing indexes. In SSMS, enable 'Include Actual Execution Plan' (Ctrl+M).

    -- Obten el plan estimado sin ejecutar
SET SHOWPLAN_XML ON;
GO
SELECT * FROM dbo.Ventas WHERE ClienteID = 123;
GO
SET SHOWPLAN_XML OFF;

// common mistake

sys.dm_exec_query_stats only sees what is still in cache: recent queries or those with RECOMPILE may not appear. The estimated plan can differ from the actual one due to bad cardinality estimates. Always compare against the actual plan when you can.

// when it's worth an expert

Reading execution plans and deciding on the fix (index, rewrite, statistics, hints) takes experience. At dba.mx we run performance diagnostics with Query Store and real plans, and deliver the improvements at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.