Skip to main content

How to Identify a Slow Microsoft SQL Server Query

You can identify slow Microsoft SQL Server queries using SQL Server Management Studio (SSMS) to determine which specific database actions are impacting your application's performance. This process allows you to target optimization efforts on the most...

You can identify slow Microsoft SQL Server queries using SQL Server Management Studio (SSMS) to determine which specific database actions are impacting your application's performance. This process allows you to target optimization efforts on the most problematic queries.

Before you begin

You will need:

  • A Microsoft SQL Server database

  • Access to the server via SQL Server Management Studio (SSMS)

  • Appropriate permissions to view system dynamic management views (DMVs) or the Execution Plan

Identify slow queries using the Execution Plan

The Execution Plan shows the steps the SQL Server engine takes to execute a query. You can use this to identify "expensive" operations, such as table scans or large sorts.

  1. Open SQL Server Management Studio and connect to your instance.

  2. In the Object Explorer, navigate to the database containing the slow query.

  3. Open a new New Query window.

  4. Paste the SQL code for the query you suspect is slow.

  5. Click the Include Actual Execution Plan icon (or press Ctrl+M).

  6. Execute the query.

  7. In the results pane, select the Execution Plan tab.

  8. Look for high-cost operators highlighted in red or with a high percentage of the total cost.

Note: The "Actual Execution Plan" is only available when you execute the query. If you only want to see the estimated plan without running the query, use the Include Execution Plan icon.

Identify slow queries using Activity Monitor

Activity Monitor provides a real-time view of current processes and can highlight queries that are currently running and consuming significant resources.

  1. In SQL Server Management Studio, go to the Tools menu.

  2. Select Activity Monitor.

  3. In the Processes pane, look for queries with a high Wait Time or CPU (ms).

  4. Right-click a high-impact process and select Details to see the specific SQL text.

Identify slow queries using Dynamic Management Views (DMVs)

You can query system views to find the most resource-intensive queries over a period of time. This is the most effective way to find "hidden" bottlenecks that do not occur during your active session.

  1. Open a New Query window in SQL Server Management Studio.

  2. Execute a query against the sys.dm_exec_query_stats view.

  3. Identify queries with high total_worker_time (CPU) or total_elapsed_time.

SELECT TOP 10
    st.text AS QueryText,
    qs.total_worker_time / 1000 AS TotalWorkerTime_ms,
    qs.total_elapsed_time / 1000 AS TotalElapsedTime_ms,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count / 1000 AS AvgWorkerTime_ms
FROM
    sys.dm_exec_query_stats qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY
    qs.total_worker_time DESC;

Confirm the result

  • The Execution Plan will highlight specific operations (like Index Scans) that are causing delays.

  • The Activity Monitor will show you which queries are currently consuming the most CPU or memory.

  • The DMV query will provide a list of the top 10 most resource-intensive queries since the last service restart.

Limitations

  • These methods identify the cause of the slowness (e.g., a missing index or a complex join) but do not automatically fix the query.

  • Results from Dynamic Management Views (DMVs) are cleared whenever the SQL Server service is restarted.

  • Identifying a slow query does not account for external factors, such as network latency or local hardware limitations.

Did this answer your question?